Shaleen/Kirti, If this is version dependent, then Oracle will brand this a 'feature' rather than a bug. And I think I know why this is happening.... As per the Concepts manual, "When you create a stored procedure, Oracle parses the procedure and stores its parsed representation in the database." I assume that when this SP is called, the SQL segment is reconstructed by the kernel and it looses its formatting during this reconstruction, thus creating a different signature/hash area. 9iR2 may be reconstructing this properly while 8i may not (as it was the first release for Outlines).
I hope this make sense! Anyone willing to look at and compare the structures of IDL_UB1$ and IDL_UB2$ and IDL_SB4$ tables (SYS) between the two versions. (Don't have my hands on 9iR2 yet - I am way behind the times here :( John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 So WHO is the Reason for the Season?! Write me for details! ** The opinions and statements above are entirely my own and not those of my employer or clients ** > -----Original Message----- > From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 12, 2002 6:26 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: sql with bind variable in stored procedure doesnt use > > > It appears to be version dependent... > > Here is what I did in 9i R2 (and in 9i R1): > > SQL> create table emp (num number,sal number); > Table created. > SQL> insert into emp values(1,1); > 1 row created. > SQL> create or replace procedure t as > 2 total number:=0; > 3 cursor c1 is > 4 select sal from emp; > 5 begin > 6 for i in c1 loop > 7 total:=total+i.sal; > 8 dbms_output.put_line('total salary is $'||total); > 9 end loop; > 10 end; > 11 / > Procedure created. > SQL> set serveroutput on > SQL> @f2 > SQL> set echo on > SQL> exec outln_pkg.drop_by_cat('DEFAULT'); > PL/SQL procedure successfully completed. > SQL> create outline on > 2 select sal from emp; > Outline created. > SQL> select used from dba_outlines; > USED > --------- > UNUSED > SQL> alter session set QUERY_REWRITE_ENABLED=true; > Session altered. > SQL> alter session set STAR_TRANSFORMATION_ENABLED=true; > Session altered. > SQL> alter session set use_stored_outlines=true; > Session altered. > SQL> exec t; > total salary is $1 > PL/SQL procedure successfully completed. > SQL> select used from dba_outlines; > USED > --------- > USED > SQL> exec outln_pkg.drop_by_cat('DEFAULT'); > PL/SQL procedure successfully completed. > SQL> select used from dba_outlines; > no rows selected > SQL> create outline on > 2 select sal from emp; > Outline created. > SQL> select sal from emp; > SAL > ---------- > 1 > SQL> select used from dba_outlines; > USED > --------- > USED > SQL> > > ================ > But in 8.1.7.4: > ================ > SQL> @f2 > SQL> set echo on > SQL> exec outln_pkg.drop_by_cat('DEFAULT'); > PL/SQL procedure successfully completed. > SQL> create outline on > 2 select sal from emp; > Outline created. > SQL> select used from dba_outlines; > USED > --------- > UNUSED > SQL> alter session set QUERY_REWRITE_ENABLED=true; > Session altered. > SQL> alter session set STAR_TRANSFORMATION_ENABLED=true; > Session altered. > SQL> alter session set use_stored_outlines=true; > Session altered. > SQL> exec t; > total salary is $1 > PL/SQL procedure successfully completed. > SQL> select used from dba_outlines; > USED > --------- > UNUSED > SQL> exec outln_pkg.drop_by_cat('DEFAULT'); > PL/SQL procedure successfully completed. > SQL> select used from dba_outlines; > no rows selected > SQL> create outline on > 2 select sal from emp; > Outline created. > SQL> select sal from emp; > SAL > ---------- > 1 > SQL> select used from dba_outlines; > USED > --------- > USED > SQL> > > > - Kirti > > -----Original Message----- > Sent: Thursday, December 12, 2002 2:14 PM > To: Multiple recipients of list ORACLE-L > stored > > > Shaleen, > > I have not been able to dig deeper (yet) into this. However > the following > _may_ be why this is not working: I believe that the stored outline is > matched with an about-to-be-executed SQL using the Hash value > of the SQL. > The hash value is computed by some mumbo-jumbo based on the actual SQL > string - thus even a single extra space in the to-be-executed > SQL will not > pick up the previously stored Outline since the hash value will be > different. Also, the hash value may depend on the shared_pool_size and > shared_pool_reserved_sizes and any changes will require Outline to be > generated again... (Gurus may want to add to this) > > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > So WHO is the Reason for the Season?! Write me for details! > > ** The opinions and statements above are entirely my own and > not those of my > employer or clients ** > > > -----Original Message----- > Sent: Wednesday, December 11, 2002 9:29 PM > To: Multiple recipients of list ORACLE-L > outlines > > > I tried this even without bind variable and could not make it > work from a > stored procedure. ANy help over here will be very appreciated > Folllowing is the testcase. > > Thanks > Shaleen > > create table emp (num number,sal number); > insert into emp values(1,1); > > create or replace procedure t as > total number:=0; > cursor c1 is select sal from emp; > begin > for i in c1 loop > total:=total+i.sal; > dbms_output.put_line('total salary is $'||total); > end loop; > end; > / > > exec outln_pkg.drop_by_cat('DEFAULT'); > create outline on select sal from emp; > 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; > select used from dba_outlines; > select sal from emp; > select used from dba_outlines; > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
