RE: sql with bind variable in stored procedure doesnt use

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:kirti.deshpande_at_verizon.com]
> 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: kirti.deshpande_at_verizon.com
>
> 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: ListGuru_at_fatcity.com (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: john.kanagaraj_at_hds.com

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: ListGuru_at_fatcity.com (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).