How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL

Question

I have three tables with these structures:

1) z_test_a(c_num number , <other columns>)
2) z_test_b(c_num number , Amount number , <other columns>)
3) z_test_b(c_num number , Amount number , <other columns>)

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:"B"."AMOUNT1" 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 " i_table_names "
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;

Answer 1

How about constructing your dynamic query like this:

with params (amount1, amount2) as
 ( select 100, 200
 from dual )
select z.*
from params p
 cross apply (
 select c_num from z_test_a
 intersect
 select c_num from z_test_b where amount = p.amount1 -- add dynamically
 ) z;

That last select under the intersect would be added dynamically according to the value of i_table_names, as in your original version.

The procedure would then be:

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 )
as
 v_base_query varchar2(2000) :=
 'with params (amount1, amount2) as
 ( select :b1, :b2
 from dual )
 select z.*
 from params p
 cross apply (
 select c_num from z_test_a';
begin
 -- Check input parameter " i_table_names "
 if i_table_names like '%z_test_b%' then
 v_base_query := v_base_query || chr(10) ||
 'intersect select c_num from z_test_b where amount = p.amount1';
 end if;
 if i_table_names like '%z_test_c%' then
 v_base_query := v_base_query || chr(10) ||
 'intersect select c_num from z_test_c where amount = p.amount2';
 end if;
 v_base_query := v_base_query || chr(10) || ') z';
 -- 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 bind_variable_intersect;

• Pantea: Thanks for your answer . There is one problem . Cross apply won't work here . when I test my procedure it gives me the error , missing keyword and when I change cross apply to "," it gives me another error ! why is this happening? why dose cross apply not working here?
• Pantea: Even this simple query wont work !!! Gives the error : Missing keyword : select * from z_test_a cross apply z_test_b !!!!!!!
• William Robertson: I was forgetting that older versions of Oracle didn’t have this. oracle-base.com/articles/12c/…
• Pantea: I guess Cross join is ok , Don't you?
• Pantea: No , Even cross join won't work !!

Answer 2

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 --> this is "B"
 where 1 = 1
intersect 
select c_num 
 from z_test_b 
 where amount = b.amount1 --> you can't reference "B" 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
   --> WHERE's not here any more; it is moved to join
  -- JOIN
  , z_test_b z
  where z.amount = b.amount1 --> moved here
  

• Pantea: Thanks . I changed the first if to this : "intersect select c_num from z_test_b where amount = :bind1" and the second one to : "intersect select c_num from z_test_b where amount = :bind2" I did not change the "open o_out for v_base_query" but it gives me this error :ORA-04024:self deadlock detected while trying to mutex pin cursor . !! I have not seen such error before !! why is this happening???
• Littlefoot: Huh? Never saw it either. Google says that it might be related to Oracle bug (something about "ALTER DATABASE OPEN RESETLOGS"). If that's really so, I'm afraid I can't help.
• Littlefoot: I added part which shows what I meant by joining the table to a CTE.
• Pantea: It won't work because there are two tables so two joins needed and you are using a were clause here.