title: PL SQL - Dynamic SQL in the where clause of a select statement
body: <p>I'm really new to Oracle PL/SQL, but experienced in Sql Server, and I'm trying to learn the ins and outs of it. For an introductory to it I've decided to optimize a query that is generated as a C# string and then executed as a text SqlCommand. So I'm trying to create a stored proc but to include all the variations of how the where clause can be generated, it's causing it to run ~40 seconds while the C# generated string executes in ~3 seconds. Here's an example of what I mean by the variations of the where clause...</p>
  
  <pre><code>declare  
  searchType nvarchar2(3); -- options are 'x', 'y', 'z'  
  searchDesc nvarchar2(4); -- options are '1', '2', '3'  
  searchValue nvarchar2(5);
  
  begin  
  
  select count(*)  
  from tbl_A a
  where ((searchType = 'x' 
      and ((a.desc_X = searchDesc and a.value_1 = searchValue)
        or (a.desc_X = searchDesc and a.value_2 = searchValue)
        or (a.desc_X = searchDesc and a.value_3 = searchValue)
      )
    )
    or (searchType = 'y' 
      and ((a.desc_Y = searchDesc and a.value_1 = searchValue)
        or (a.desc_Y = searchDesc and a.value_2 = searchValue)
        or (a.desc_Y = searchDesc and a.value_3 = searchValue)
      )
    )
    or (searchType = 'z' 
      and ((a.desc_Z = searchDesc and a.value_1 = searchValue)
        or (a.desc_Z = searchDesc and a.value_2 = searchValue)
        or (a.desc_Z = searchDesc and a.value_3 = searchValue)
      )
    )
  )
  
  end;
  </code></pre>
  
  <p>So what I'm wondering is, is it possible to have a select statement that can execute dynamic sql in the where clause. Or does the entire statement need to be dynamic sql. Below is an example of what I'm questioning...</p>
  
  <pre><code>declare
  whereClause varchar2(500);  
  searchType nvarchar2(3); -- options are 'x', 'y', 'z'  
  searchDesc nvarchar2(4); -- options are '1', '2', '3'  
  searchValue nvarchar2(5);  
  
  begin
  
  select case
      when searchType = 'x' then 'a.desc_X = :desc and a.value_1 = :val'
      when searchType = 'y' then 'a.desc_Y = :desc and a.value_2 = :val'
      when searchType = 'z' then 'a.desc_Z = :desc and a.value_3 = :val'
      end
  into whereClause
  from dual;
  
  select count(*)
  from tbl_A a
  where (
      execute immediately whereClause using searchDesc, searchValue
  )
  
  end;
  </code></pre>
  
  <p>When I tried to execute it as all dynamic sql, it still took ~15 seconds to execute. So if anyone has a better way of handling the many where clause variations, I'm open to suggestions.</p>