How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL I have three tables with these structures:
1) z_test_a(c_num   number , &lt;other columns&gt;)
2) z_test_b(c_num   number , Amount  number , &lt;other columns&gt;)
3) z_test_b(c_num   number , Amount  number , &lt;other columns&gt;)

Inspired by one the answers in this post: https://stackoverflow.com/questions/2514254/how-can-i-create-a-dynamic-where-clause , I've learned that one way to use Dynamic Query and still use Bind variables is to write your query with With clause. I tried to apply this approach with Intersect but I could not succeed and my procedure does not work.It ends up giving me this error:ORA-00904:&quot;B&quot;.&quot;AMOUNT1&quot; invalid identifier for this input parameter:
1)i_table_names:z_test_b,z_test_c
2)i_table_names:1000
3)i_amnt_second:1000

Is this even possible to use this approach when there is intersect or union or the query must be as simple as it is in the post mentioned above? And is there a better way to write this procedure?Maybe it can be written without dynamic query .
My procedure is:
create or replace procedure bind_variable_intersect(i_table_names in varchar2,
                                                    i_amnt_first  in number,
                                                    i_amnt_second in number,
                                                    o_out         out sys_refcursor) is

v_base_query varchar2(2000) := ' with binds as 
                                   (select :bind1 as amount1, 
                                           :bind2 as amount2                        
                                    from dual)
                                 select t.c_num
                                 from z_test_a t , binds b where 1=1  ';

begin

-- Check input parameter &quot; i_table_names &quot;
if i_table_names like '%z_test_b%' then
   v_base_query := v_base_query ||
                ' intersect select c_num from z_test_b where amount = b.amount1  ';
end if;

if i_table_names like '%z_test_c%' then
   v_base_query := v_base_query ||
                ' intersect select c_num from z_test_c where amount = b.amount2  ';
end if;

-- Debug Code
dbms_output.put_line(v_base_query);

-- Execute
open o_out for v_base_query
   using i_amnt_first,i_amnt_second;

end;
You got it wrong, I'm afraid. This is what your query evaluates to:
with binds as 
  (select :bind1 as amount1, 
          :bind2 as amount2                        
   from dual)
select t.c_num
  from z_test_a t, 
       binds b               --&gt; this is &quot;B&quot;
  where 1 = 1
intersect 
select c_num 
  from z_test_b 
  where amount = b.amount1   --&gt; you can't reference &quot;B&quot; here
             

Instead of intersect, you could:

use :bind1 in z_test_b, i.e.
intersect
  select c_num
  from z_test_b
  where amount = :bind1


or join z_test_b with z_test_a and binds (but I presume that former suggestion is simpler because of possible where clause; currently, it is useless (where 1 = 1), but I presume that it is here just for illustration. Something like this:
-- CTE you already have; remove WHERE from it
with binds as 
  (select :bind1 as amount1, 
          :bind2 as amount2                        
   from dual)
select t.c_num
  from z_test_a t,
       binds b
                      --&gt; WHERE's not here any more; it is moved to join

-- JOIN
, z_test_b z
where z.amount = b.amount1       --&gt; moved here