RE: sql with bind variable in stored procedure doesnt use stored

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:shgarg_orafaq_at_hotmail.com]
> 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: 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).