1

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;
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;
| improve this answer | |
  • 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 Aug 9 at 7:01
  • Even this simple query wont work !!! Gives the error : Missing keyword : select * from z_test_a cross apply z_test_b !!!!!!! – Pantea Aug 9 at 7:03
  • 1
    I was forgetting that older versions of Oracle didn’t have this. oracle-base.com/articles/12c/… – William Robertson Aug 9 at 7:09
  • I guess Cross join is ok , Don't you? – Pantea Aug 9 at 7:16
  • No , Even cross join won't work !! – Pantea Aug 9 at 7:18
1

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
    
| improve this answer | |
  • 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??? – Pantea Aug 2 at 12:15
  • 1
    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 Aug 2 at 12:18
  • I added part which shows what I meant by joining the table to a CTE. – Littlefoot Aug 2 at 12:24
  • It won't work because there are two tables so two joins needed and you are using a were clause here. – Pantea Aug 2 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.