Home » SQL & PL/SQL » SQL & PL/SQL » Packages
Packages [message #219743] Thu, 15 February 2007 16:44 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi,

I have a doubt regarding packages. Can there be conficts with the variables , plsql tables which I have used in package specification and body when used in concurrent oracle sessions in anyway?

For Example

package specification A
{
   plsql_table a;
   plsql_table b;
   ....
   some variables;
   function1();
   function2();
   ....
   ....
   procedure1();
   procedure2();
   ....
}

package body A
{
 
  function1()
  {
     detailed...
     usage of plsql_table a;
     usage of plsql_table b;
  }
  ...
  ...
  ...
  procedure2()
  {
     detailed...
     usage of plsql_table a;
     usage of plsql_table b;
  }
}



In this scenario, can by any chance the values in plsql_table or variables have problem in concurrent sessions? To my knowledege , all the variables declared are independent to each session.

Thanks in Advance.

regards
Srivaths

[Updated on: Thu, 15 February 2007 22:38]

Report message to a moderator

Re: Packages [message #219795 is a reply to message #219743] Fri, 16 February 2007 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Different sessions will each have their own copies of the tables. There is no chance of session 1 seeing session 2s data in a pl/sql table.
Re: Packages [message #219849 is a reply to message #219795] Fri, 16 February 2007 06:17 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Thanks for the reply. I want to add-up one more point. In my scenario, I have some two tables, which had some 5 triggers. To avoid mutating table error, i have used global plsql table declared which catch the updated rowid and then apply the changes in after update trigger. In that after update trigger, i am calling those package procedure and functions.

Example :

Table 1

   Before update : Initilize the plsql table (4 mutating)
   For each row  : Catch those updated rowid
   After update  : Loop thro the rowid's caught , call that package which again uses those table.

   After update  : some trigger1
   After update  : some trigger2


Please let me know if there are something which I should look for before writing triggers and the packages which are called inside those triggers involving mutating tables which can cause problem.

Thanks
Srivaths
Re: Packages [message #219864 is a reply to message #219849] Fri, 16 February 2007 07:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That looks like a workable approach to solving the problem.
Re: Packages [message #220242 is a reply to message #219743] Mon, 19 February 2007 15:51 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Understand one issue. Oracle does not guarantee that triggers at the same level (statement after update) will fire in the same order. They will be random, so if your other two triggers require information that is set by the first after update trigger, it may or may not be done.
Re: Packages [message #220244 is a reply to message #220242] Mon, 19 February 2007 16:14 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Thanks for the info Bill. I think there is some other loophole in my system, which i have to workout.

Thanks to you also JRowbottom.

I will let know you people once i find out.

Thanks
Srivaths
Re: Packages [message #221839 is a reply to message #220244] Wed, 28 February 2007 11:03 Go to previous message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Thanks everybody. I found a loophole in my system, it was nothing to do with package procedure called from triggers.

Thanks a lot again.

regards
Srivaths

[Updated on: Wed, 28 February 2007 11:04]

Report message to a moderator

Previous Topic: Count function
Next Topic: Unix Timestamps/Partitioning
Goto Forum:
  


Current Time: Sun Dec 04 04:46:58 CST 2016

Total time taken to generate the page: 0.07688 seconds