All,
I am unable to make stored outlines work for sql
statements with bind variable in stored procedures. Please see following sample
code and tell me what am I doing wrong.
Thanks in Advance
create table emp (num number,sal number);
insert into emp values(1,1); create or replace procedure t(num_i number) as total number; cursor c1(p1 number) is select sal from emp where num=p1; begin for i in c1(num_i) loop total:=total+i.sal; dbms_output.put_line('total salary is $'||total); end loop; end; / alter session set sql_trace=true; exec t(1); alter session set sql_trace=false; create outline on select sal from emp where num=:b1; select used from dba_outlines; alter session set
QUERY_REWRITE_ENABLED=true;
alter session set STAR_TRANSFORMATION_ENABLED=true; alter session set use_stored_outlines=true; exec t(2); select used from dba_outlines; variable b1 number;
exec :b1:=1; select sal from emp where num=:b1; select used from dba_outlines; Thanks
Shaleen
|