Concept clarification - Invalid packages

From: Johnson, George <GJohnson_at_GAM.COM>
Date: Fri, 28 Jan 2005 08:56:02 -0000
Message-ID: <ECD91EB68018C04CA1B6801EE47A910F335C7520@ntas-ldn15.gam.com>

Sorry this is a bit of a long one....

I am currently arguing with a developer over an application's behaviour with regards handling of invalid packages.

Application X is making SQL calls and using package calls in-line to retrieve data, now while this is going on you can change the package being used, by reloading the complete package definition, this obviously causes the SQL to throw an ORA4061, state of packages had changed. This is in turn tells the app that something has gone wrong, however the app simply hands back only the data it has collected so far. I believe the app should say "oh well ORA SQL error, sorry no data. please try again later".

Now extracting the small section of SQL:

	SELECT VIEW_001.COL1
		FROM
  	VIEW_NUMBER_001
		WHERE


  	( PACKAGE_001.FUNCTION_001(VIEW_001.COL1))


	If this runs in a session, and in another session we reload the

package, it cuts right into the SQL and stops it with the ORA-4061, "state of package has changed", as I would expect it to.

Now if I run this in one session, NOTE: wrapping a count function around the data column this time:

	SELECT count(VIEW_001.COL1)
		FROM
  	VIEW_NUMBER_001
		WHERE


  	( PACKAGE_001.FUNCTION_001(VIEW_001.COL1))


	The package reload in the other session waits until the SQL has

finished, before it gets a crack the reload/rebuild.

My understanding, possibly flawed, is that the 2nd SQL, the count() is forcing the package to stay locked down until the result is completely collated, finally counted and returned, before allow the package to be manipulated.

The first SQL is constantly calling and releasing the package, so at some point Oracle is allowing the rebuild package to jump in, the split second it knows the package is no longer locked in use.

Is my thinking correct or have I missed something stupidly fundamental?

Thanks for you patience if you got this far!

This message contains confidential information and is intended only for the individual or entity named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as an invitation or offer to buy or sell any securities or related financial instruments.
GAM operates in many jurisdictions and is regulated or licensed in those jurisdictions as required.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 28 2005 - 03:58:36 CST