Home » SQL & PL/SQL » SQL & PL/SQL » Can procedures in a package step on each other?
Can procedures in a package step on each other? [message #201333] Fri, 03 November 2006 09:37 Go to next message
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
Junior Member
I have a package with several procedures and functions in it. I have defined several variables, as well as a nested table to hold errors, in the package body. These are used by multiple procedures and functions in the package. Currently, this package is scheduled to run once every 10 minutes. I want to add a new procedure to this package that will be called by an on-line web front-end application. Can this new procedure use the same variables and error table that are currently set up, or could that lead to values in the variables and error table "stepping on each other"? For example, to use the nested error table, the new procedure has to initialize the collection. If the 10-minute run was in progress and had inserted rows into that collection, would this initialization wipe them out?
I hope I have explained the issue well enough. Thanks for your feedback.
Re: Can procedures in a package step on each other? [message #201336 is a reply to message #201333] Fri, 03 November 2006 09:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In short, No.
Each session will have it's own instantiation of the package and it's own copy of an package body global parameters.
Re: Can procedures in a package step on each other? [message #201354 is a reply to message #201336] Fri, 03 November 2006 11:37 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Not true. If you have a connection pool, the package will stay active after user1 gets their response, and the next user to use that connection in the pool will execute it with the old values still visible in global variables/nested table.

In iAS, you can add pre or post code to the connection, so that new connections flush package state.

dbms_session.reset_package
Re: Can procedures in a package step on each other? [message #201638 is a reply to message #201354] Mon, 06 November 2006 03:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I thought that connection pooling implied a reset of state after each call, or is that optional? It's certainly the way it works here.

Even so, that's not quite the situation that the Op was after - I think he was wondering if session A could interfere with the values in session B's packages.
Previous Topic: Bulk collection
Next Topic: Need Query
Goto Forum:
  


Current Time: Sat Dec 10 05:06:24 CST 2016

Total time taken to generate the page: 0.08299 seconds