Home » SQL & PL/SQL » SQL & PL/SQL » Package Specification varaible (Oracle 11g)
Package Specification varaible [message #658227] Wed, 07 December 2016 06:32 Go to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

Hi

I had defined the few global variables in the Package Specification.One of the main procedure is initializing some values into those variables.
In the main procedure we are calling different sub procedures. But unfortunately some times global variables are becoming null & Some times i am able to get the value which I initialized in my main procedure .

Could you please let me know functionality of the variable which is defined in spec & initialized in the one procedure.

Thanks
SaiPradyumn


Re: Package Specification varaible [message #658234 is a reply to message #658227] Wed, 07 December 2016 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You could post your code then we can say what is wrong.

Re: Package Specification varaible [message #658240 is a reply to message #658234] Wed, 07 December 2016 07:30 Go to previous messageGo to next message
cookiemonster
Messages: 13265
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also are you calling this package via a connection pool?
Re: Package Specification varaible [message #658302 is a reply to message #658240] Thu, 08 December 2016 06:32 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
Sorry for the delay Mike ,

hi CookieMonester ,

I am calling this procedure,through the SQL Developer Tool.
As there are so many dependent objects i am unable to post the actual code .
Please help me to understand the behavior of the global variables which are defined in the package specification.
Re: Package Specification varaible [message #658305 is a reply to message #658302] Thu, 08 December 2016 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"global variables defined in the package specification" are very simple: they are variables that are included in your session.

So the cookiemonster's question, do you use a connection pool?
Which means does your session can be reused by someone else or can you be reassigned to another session?

[Updated on: Thu, 08 December 2016 06:37]

Report message to a moderator

Re: Package Specification varaible [message #658306 is a reply to message #658305] Thu, 08 December 2016 06:41 Go to previous messageGo to next message
cookiemonster
Messages: 13265
Registered: September 2008
Location: Rainy Manchester
Senior Member
SQL Developer can be multi-session I believe
Re: Package Specification varaible [message #658307 is a reply to message #658227] Thu, 08 December 2016 06:46 Go to previous messageGo to next message
pablolee
Messages: 2871
Registered: May 2007
Location: Scotland
Senior Member
saipradyumn wrote on Wed, 07 December 2016 12:32

But unfortunately some times global variables are becoming null & Some times i am able to get the value which I initialized in my main procedure .
Clearly describe the scenarios where one happens and when the other happens. i.e. Is it always the same procedures that the variable is null and the same procedures where it has the variable populated? Or can the same procedure sometimes have a null and sometimes be populated.
How are you identifying the value of the variable during each procedure call?



Re: Package Specification varaible [message #658320 is a reply to message #658307] Fri, 09 December 2016 08:06 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
Hi ,

It's not the same procedure always.To identify global parameter values i had created the my own table and inserting global parameter value into that table .Some times tat test have the value and some times it doesn't have the value:
Re: Package Specification varaible [message #658321 is a reply to message #658307] Fri, 09 December 2016 08:08 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

So the cookiemonster's question, do you use a connection pool?
Which means does your session can be reused by someone else or can you be reassigned to another session?


Answer to this question is NO
Re: Package Specification varaible [message #658322 is a reply to message #658321] Fri, 09 December 2016 08:31 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
You have not provided us anything of substance; just a vague description of what you observe.
One of two realities exist.
1) Oracle is behaving as designed & Problem Exists Between Keyboard And Chair
or
2) an Oracle bug exists.
My experience leans heavily toward #1 being actual reality.
If #2 is true, then you need to provide reproducible test case to get it corrected.

Good Luck!
Re: Package Specification varaible [message #658323 is a reply to message #658320] Fri, 09 December 2016 08:38 Go to previous messageGo to next message
pablolee
Messages: 2871
Registered: May 2007
Location: Scotland
Senior Member
saipradyumn wrote on Fri, 09 December 2016 14:06
Hi ,

It's not the same procedure always.To identify global parameter values i had created the my own table and inserting global parameter value into that table .Some times tat test have the value and some times it doesn't have the value:
So what are the conditions under which procedure A(for want of a better term) 'has the value' and what are the conditions when it doesn't.

As Blackswan has said, you're not giving us much to go on.

It sounds to me like you are calling the procedure in different sessions and in one session you are first populating the variable in question, but in the other you are not, but that's a guess based on very limited info.
Re: Package Specification varaible [message #658324 is a reply to message #658323] Fri, 09 December 2016 09:11 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Blackswa/pablolee


Really thank you very much for your guidance.I am just calling this package by using one pl/sql block. As you explained if the problem is with respect to the multiple sessions, to make sue that i am in the same session i am just contacting the current session id (sys_context('USERENV','SID')) also to the global parameter value .

Now my temp table always contains required data and even session id is same for all my intermediate calls .now my problem seems to be resolved.
DO i need to populate the session id in some variable always ?

Apart from these i am not doing any thing extra to my package.Please trust me .

Thanks
Sai Pradyumn
Re: Package Specification varaible [message #658325 is a reply to message #658324] Fri, 09 December 2016 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
variables are NOT populated if you do not populate them.
Re: Package Specification varaible [message #658329 is a reply to message #658325] Fri, 09 December 2016 10:01 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
Even though we are not populating the session id in to the some variable that session id value should be unique across all procedures of that package .Still i am surprising how that session id initialization is resolving my problem
Re: Package Specification varaible [message #658331 is a reply to message #658329] Fri, 09 December 2016 10:39 Go to previous message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post some code we can see and reproduce, otherwise you can't have more than what we have already said.

Previous Topic: Long data type in oracle
Next Topic: AND criteria in where clause
Goto Forum:
  


Current Time: Tue Oct 16 02:38:21 CDT 2018