| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
SAL
1
SQL> select used from dba_outlines;
USED
SQL> @f2
SQL> set echo on
SQL> exec outln_pkg.drop_by_cat('DEFAULT');
PL/SQL procedure successfully completed.
SQL> create outline on
SAL
1
SQL> select used from dba_outlines;
USED
-----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!
-----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 servicesReceived on Thu Dec 12 2002 - 20:26:28 CST
---------------------------------------------------------------------
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).
 |
 |