* Using ref cursor with dynamic SQL ans bind variables
* How does using bind variable :b1 for p2 help?
SQL> create or replace function test_func1_(p1 varchar2, p2 varchar2)
2 return sys_refcursor
3 as
4 x sys_refcursor;
5 begin
6 open x for 'select * from test1 where c1 '||p1||' and '||
7 'c2= :b1' using p2;
8 return x;
9 end;
10 /
Function created.
SQL>
SQL> set autoprint on
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> variable x refcursor;
SQL> exec :x:=test_func1_('like '||''''||'a'||'''',1);
PL/SQL procedure successfully completed.
C1 C2 SUMC2
-- ---------- ----------
a 1 100
a 1 135
a 1 200
a 1 360
SQL> exec :x:=test_func1_('like '||''''||'a'||'''',2);
PL/SQL procedure successfully completed.
C1 C2 SUMC2
-- ---------- ----------
a 2 430
a 2 100
SQL> exec :x:=test_func1_('like '||''''||'a'||'''',3);
PL/SQL procedure successfully completed.
C1 C2 SUMC2
-- ---------- ----------
a 3 50
SQL> exec :x:=test_func1_('='||''''||'b'||'''',2);
PL/SQL procedure successfully completed.
C1 C2 SUMC2
-- ---------- ----------
b 2 400
b 2 130
SQL> exec :x:=test_func1_('='||''''||'a'||'''',2);
PL/SQL procedure successfully completed.
C1 C2 SUMC2
-- ---------- ----------
a 2 430
a 2 100
SQL> exec :x:=test_func1_('in('||''''||'a'||''''||','||''''||'b'||''''||')',2);
PL/SQL procedure successfully completed.
C1 C2 SUMC2
-- ---------- ----------
b 2 400
a 2 430
b 2 130
a 2 100
SQL>
SQL> alter session set sql_trace=false;
Session altered.
SQL> spool off;