Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01031,ORA-06512 dbms_session.set_context error
ORA-01031,ORA-06512 dbms_session.set_context error [message #290237] Fri, 28 December 2007 02:53 Go to next message
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
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290245 is a reply to message #290237] Fri, 28 December 2007 03:20 Go to previous messageGo to next message
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
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290257 is a reply to message #290237] Fri, 28 December 2007 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290263 is a reply to message #290257] Fri, 28 December 2007 04:19 Go to previous messageGo to next message
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 809 times)
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290267 is a reply to message #290263] Fri, 28 December 2007 04:24 Go to previous messageGo to next message
Frank
Messages: 7877
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290295 is a reply to message #290285] Fri, 28 December 2007 05:23 Go to previous messageGo to next message
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
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290302 is a reply to message #290295] Fri, 28 December 2007 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #290318 is a reply to message #290302] Fri, 28 December 2007 06:17 Go to previous messageGo to next message
Frank
Messages: 7877
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 Go to previous messageGo to next message
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

Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563660 is a reply to message #290326] Thu, 16 August 2012 07:06 Go to previous messageGo to next message
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,
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563680 is a reply to message #563660] Thu, 16 August 2012 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is possible you post your code (including account privileges) then we can tell you what is wrong.

Regards
Michel
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563686 is a reply to message #563680] Thu, 16 August 2012 08:36 Go to previous messageGo to next message
Littlefoot
Messages: 19686
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.
Re: ORA-01031,ORA-06512 dbms_session.set_context error [message #563774 is a reply to message #290237] Fri, 17 August 2012 00:42 Go to previous messageGo to next message
krishna_900
Messages: 11
Registered: June 2005
Junior Member

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

[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 Go to previous message
joy_division
Messages: 4527
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.
Previous Topic: Time Bucket Outer
Next Topic: ORA-14074: partition bound must collate higher than that of the last partition
Goto Forum:
  


Current Time: Tue Oct 21 06:39:58 CDT 2014

Total time taken to generate the page: 0.10880 seconds