RE: Procedural Consistency

The app was getting an ORA-600, which metalink identified as a 'bug' in 9i. The app uses persistent connections and the 'login' package had been recompiled without removing the connections. All of a sudden the ORA-600s started being thrown.

It made me curious (call me George) as to how Oracle handles code consistency in procedures. The scenario I pondered was that a proc is called within a loop. At some point while the loop is being executed by a calling app, the proc is changed. This could cause the app to alter the handling of the data and produce inconsistent results. Since the proc's execution is complete, is the proc still pinned?

Just trying to understand a little more about oracle internals...

-----Original Message-----
Sent: Thursday, September 26, 2002 4:58 AM To: Multiple recipients of list ORACLE-L

What is the error and how is the error occuring? My experience has been:

1. Long running SQL calling PL/SQL function: will die due to invalidated state of the function.
2. Long running PL/SQL calling PL/SQL procedure (statically): cannot re-compile the procedure during the run as it is pinned.
3. Long running PL/SQL calling PL/SQL procedure (dynamically): will just run whatever procedure exists at the time of the call.
   • Bill.

At 12:43 24/09/2002 -0800, you wrote:
>Okay, I know I'm being a little lazy on this one, but I'm very interested
>to hear the ideas/conjecture/proof. So away we go....
>
>We recently encountered a bug in Oracle where a long running process
>attempted to execute a procedure that was within a package that had been
>recompiled since the process had first executed the procedure. This brings
>up the question as to whether the kernel requires that each time the
>process executes the procedure that the procedure is exactly the same as
>when the process first executed it. If so, how does the process keep track
>of the version of the procedure that it has previously executed?
>
>Any input/thoughts are greatly appreciated...
>
>Dan

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bill Buchan
  INET: wbuchan_at_uk.intasys.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: Fink, Dan
  INET: Dan.Fink_at_mdx.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).