Re: Set Role From a Stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/30
Message-ID: <33b7fc8f.31089824@newshost>#1/1

Believe, set role has no effect in a procedure, roles have no effects in procedures. Apparently in 7.1, the set role call will not FAIL when you use dbms_sql (but it does not succeed either). Using dbms_session in 7.1, 7.2 and 7.3 you'll see:

PROCEDURE dbms_session.set_role

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ROLE_CMD                       VARCHAR2                IN

SQL> exec dbms_session.set_role( 'all' );

PL/SQL procedure successfully completed.

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

Procedure created.

SQL> exec set_role;
begin set_role; 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.SET_ROLE", line 4
ORA-06512: at line 1

In an anonymous block, in sql*plus for example, this will work. In a stored procedure, NO.

On 29 Jun 1997 21:59:51 GMT, "Aram Meguerian" <aram_at_unisys.com.br> wrote:

>> > roles are never enabled in a stored procedure.. setting a role in a
stored
>> > procedure won't work. In the supplied example, the parse succeeded,
but the
>> > execute of the statement (only DDL is implicitly execute with dbms_sql,
set role
>> > is not ddl) never took place. Modify the routine to be:
>
> We are doing that, but I think we used the DBMS_SESSION package
> and an specific procedure inside it that can "SET ROLE" ...
>
>
> Aram Meguerian
> aram_at_unisys.com.br
>
>-------------------------------------------------------------------
> TANSTAAFL - There ain't no such thing as a free lunch
> by Robert A. Heinlein
>-------------------------------------------------------------------
> I don't work at Unisys, it is just my Internet Provider,
> so don't blame it for anything I have just said.
>-------------------------------------------------------------------
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 30 1997 - 00:00:00 CDT