Message-ID: <F001.00518FA5.20021212181838@fatcity.com>
Date: Thu, 12 Dec 2002 18:18:38 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: John Kanagaraj <john.kanagaraj@hds.com>
Subject: RE: sql with bind variable in stored procedure doesnt use stored
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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@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@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@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).