Re: Urgent: SET ROLE in a stored procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 28 Oct 1998 14:19:08 GMT
Message-ID: <36372791.3745355@192.86.155.100>

A copy of this was sent to Dmitri Blinov <Dmitri_Blinov_at_dialogbank.com> (if that email address didn't require changing) On Wed, 28 Oct 1998 13:19:33 +0300, you wrote:

>> Unfortunately, you can't. Oracle will tell you :
>> ORA-06565: cannot execute SET ROLE from within stored procedure
>
>And what about DBMS_SESSION.SET_ROLE procedure ?
>
>PLSQL has syntax limitation and for that reason there are exist
>several packages, which allows you to use the same functionality by
>calling their functions. One of them is DBMS_SESSION.
>
>Dmitry.

The dbms_session.set_role procedure can be used in an anonymous block (not a stored, compiled object but rather an anonymous, execute one time, pl/sql block)..

For example:

SQL> begin
2 dbms_session.set_role( 'DBA' ); 3 end;
4 /

PL/SQL procedure successfully completed.

SQL> create procedure testing
  2 as
  3 begin
  4 dbms_session.set_role( 'DBA' );   5 end;
  6 /

Procedure created.

SQL> exec testing;
begin testing; end;

*
ERROR at line 1:

ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at "SYS.DBMS_SESSION", line 26
ORA-06512: at "TKYTE.TESTING", line 4
ORA-06512: at line 1

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Oct 28 1998 - 08:19:08 CST