OraFAQ Forum: SQL & PL/SQL » ORA-01031,ORA-06512 dbms_session.set_context error

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01031,ORA-06512 dbms_session.set_context error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01031,ORA-06512 dbms_session.set_context error [message #290237]

Fri, 28 December 2007 02:53

krishna_900
Messages: 11
Registered: June 2005

Junior Member

Hi All,

my senario is i have created a context from schema 'Schema1' using "accessed globally" option and created on package to set the values for conext. It is working fine.

When i deploy the same package on schema2 (remember the context is on schema1) and try to execute it on schema2 then it is giving me the following error. any help will be much appreciated.

attached the test package for reference.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> exec krishna_pkg.MyProcedure
BEGIN krishna_pkg.MyProcedure; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "LGE2008DEV.KRISHNA_PKG", line 6
ORA-06512: at line 1

Attachment: KRISHNA_PKG.txt
(Size: 0.28KB, Downloaded 1586 times)

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290245 is a reply to message #290237]

Fri, 28 December 2007 03:20

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Check this link
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279 and search for set_context. You will find another user had a similar sort of issue and I strongly believe you might also have the same problem.

HTH

Regards

Raj

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290257 is a reply to message #290237]

Fri, 28 December 2007 03:55

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post the whole sessions you execute from start to the error including context creation, package creation, privilege granted and so on.

Regards
Michel

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290263 is a reply to message #290257]

Fri, 28 December 2007 04:19

krishna_900
Messages: 11
Registered: June 2005

Junior Member

Hi Raj,
I have gone through the TOM link and found that if a context is created from schema1 for a package reside in schema1 then the same context will not be able to use from schema2 even if i deploy the same package in schema2. But i am not able to find a solution for this. May be this is how oracle works. My problem is i cannot create two context with same name inside one database even though it is for two different schemas. Looking for a solution or workaround.

Hi Michel,
I have attached the output as per your request.

Regards,
Krishna

Attachment: OUTPUT1.txt
(Size: 1.62KB, Downloaded 2423 times)

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290267 is a reply to message #290263]

Fri, 28 December 2007 04:24

Frank
Messages: 7901
Registered: March 2000

Senior Member

Looks familiar

[Updated on: Fri, 28 December 2007 04:25]

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290285 is a reply to message #290263]

Fri, 28 December 2007 04:53

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

krishna_ctx context is associated to LGE2008DEV.krishna_pkg package and so SITLGE2008.krishna_pkg can't set it.

Regards
Michel

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290295 is a reply to message #290285]

Fri, 28 December 2007 05:23

krishna_900
Messages: 11
Registered: June 2005

Junior Member

Hi Frank and Michel,

The situation is similar for me. Because of tight budget constraints we need to do the testing and development on two different schema on same database. Yes, persistent associated array is a good option which i have never thought about.

Still I am not convinced with the context approach. If I create a context for one schema why not it is sharable among other schema’s.

Regards,
Krishna

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290302 is a reply to message #290295]

Fri, 28 December 2007 05:44

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is sharable but it is associated to ONE package, just give execute privilege on this package to all schemas you want to share.

Regards
Michel

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290318 is a reply to message #290302]

Fri, 28 December 2007 06:17

Frank
Messages: 7901
Registered: March 2000

Senior Member

Another option is to make the context-name variable in the code. Set it in one single place and refer to that constant.
In cases where you want to share database-instances, you can rename one of the contexts, without having to alter too much code.

The associative array will cause PLSQL/SQL context switches, whereas it seems that calls to sys_context don't.

Bummer... why can I come up with this solution only for someone else??

[Updated on: Fri, 28 December 2007 06:18]

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290326 is a reply to message #290318]

Fri, 28 December 2007 06:47

krishna_900
Messages: 11
Registered: June 2005

Junior Member

Yep. That’s the way for me.

I will be declaring one constant on a common package or current package specification and then change it to a variable method. I have tested it and it works fine.

One more method i have thought to avoid hard coding is to prefix schema name to context variable in runtime. But userenv gives only username not schema name where package has been deployed?? Since there will be multiple database user accessing the same package this logic will not work??

Thanks a lot for support mates.
Krishna

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563660 is a reply to message #290326]

Thu, 16 August 2012 07:06

inamhaque@hotmail.com
Messages: 1
Registered: August 2012

Junior Member

Hi Krishna,

Is possible that you can upload the solution this context problem, I'm trying the same way as you mentioned in your post but still getting 'ora-01031 insufficient privileges error'.

I shall be grateful to you.

Best Regards,

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563680 is a reply to message #563660]

Thu, 16 August 2012 08:23

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Is possible you post your code (including account privileges) then we can tell you what is wrong.

Regards
Michel

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563686 is a reply to message #563680]

Thu, 16 August 2012 08:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Michel's suggestion might be better than waiting for Krishna to answer your question as he last visited this Forum in Dec 2007.

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563774 is a reply to message #290237]

Fri, 17 August 2012 00:42

krishna_900
Messages: 11
Registered: June 2005

Junior Member

Its been a while, i have attached a sample package. Hope this helps.

Attachment: Context Sample.txt
(Size: 0.42KB, Downloaded 1980 times)

[Updated on: Fri, 17 August 2012 00:49]

Report message to a moderator

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563842 is a reply to message #563686]

Fri, 17 August 2012 08:02

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Littlefoot wrote on Thu, 16 August 2012 09:36

Michel's suggestion might be better than waiting for Krishna to answer your question as he last visited this Forum in Dec 2007.

I see the wheels turning in your head right now, trying to figure something out. Those same wheels are tuning in my head. Or maybe, it's only in my head and not yours.

Report message to a moderator

Previous Topic:	Time Bucket Outer
Next Topic:	ORA-14074: partition bound must collate higher than that of the last partition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 14:44:42 CST 2024