Home » SQL & PL/SQL » SQL & PL/SQL » Set Role from procedure (Oracle 10g)
Set Role from procedure [message #309448] Thu, 27 March 2008 08:37 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
I get this error if I try to execute SET ROLE <rolename> from
the procedure

Error::: ORA-06565: cannot execute SET ROLE from within stored procedure

May I know how to activate the role from the procedure ?
Re: Set Role from procedure [message #309449 is a reply to message #309448] Thu, 27 March 2008 08:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SET ROLE is a SQL*Plus command only.
Re: Set Role from procedure [message #309455 is a reply to message #309448] Thu, 27 March 2008 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DBMS_SESSION.SET_ROLE instead.

Regards
Michel
Re: Set Role from procedure [message #310155 is a reply to message #309455] Mon, 31 March 2008 07:43 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
I tried DBMS_SESSION.SET_ROLE, but get the same error

Error::: ORA-06565: cannot execute SET ROLE from within stored procedure
Re: Set Role from procedure [message #310161 is a reply to message #310155] Mon, 31 March 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you have.
I used it every day, so it works unless you use it inappropriately.

Regards
Michel

Re: Set Role from procedure [message #310165 is a reply to message #309448] Mon, 31 March 2008 08:09 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
A@orcl > select * from session_roles;

no rows selected

A@orcl > set role role1;

Role set.

A@orcl > select * from session_roles;

ROLE
------------------------------
ROLE1

1 row selected.

A@orcl > set role none;

Role set.

A@orcl > begin
  2  dbms_session.set_role('role1');
  3  end;
  4  /

PL/SQL procedure successfully completed.

A@orcl > select * from session_roles;

ROLE
------------------------------
ROLE1

1 row selected.
Re: Set Role from procedure [message #310436 is a reply to message #309448] Tue, 01 April 2008 04:50 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks for your replies.
It doesn't work from a stored procedure but when I execute it from a PL/SQL block it works. So I'm now setting the role after calling the stored procedure inside the shell script.
Re: Set Role from procedure [message #310508 is a reply to message #309448] Tue, 01 April 2008 08:05 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You may also want to investigate a concept called a Secure Application Role. It has syntax such as:

create role secure_role identified using secure_role_pkg;

Which I should have mentioned before. It is actually a nice way to enable roles.

Previous Topic: using field from main query as parameter in PL/SQL code of formula field
Next Topic: Facing an strange error, needed urgently.
Goto Forum:
  


Current Time: Thu Dec 08 10:15:50 CST 2016

Total time taken to generate the page: 0.12513 seconds