Shaleen, After a lot of digging into trace files, (and other DD tables such as IDL_UB1$ and IDL_UB2$ and IDL_SB4$ tables :), I have concluded that stored outlines are not being used for an SQL piece when it is part of a stored procedure, (probably for certain situations). I was able to see that the SQL text that is generated when the exec t(1) is run is totally different from the _source_ that is stored. You can see this too in the trace file - although you had a lower case 'select' string, the Trace shows an upper case when it parses this cursor, and even when I tried creating an upper case file, it introduced strange spaces in the SQL in the trace. This of course results in a different has value and signature, leading to the SO not being used. This may be a bug too...
I would suggest a workaround: Look at the trace file and create the SO from that, using exactly the same spacing, text, etc. will also try this out tomorrow, if I have time... 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: Shaleen Garg [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 12, 2002 2:29 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: sql with bind variable in stored procedure doesnt use > stored > > > John, > > Thanks for the input. As far as I can see that sql's are > exactly same in my > example. Morover none of the initialization parameters have > been changed. > Need input from all Gurus > > Thanks > Shaleen > ----- Original Message ----- -- 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).
