Concept clarification - Invalid packages
- From: "Johnson, George" <GJohnson@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 28 Jan 2005 08:56:02 -0000
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.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
