Re: Set Role From a Stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/27
Message-ID: <33b316e2.9305560@newshost>#1/1

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:

SQL> l
  1 CREATE OR REPLACE PROCEDURE set_role   2 IS
  3 cursor_handle INTEGER;

  4    rc                       number;
  5  BEGIN
  6        cursor_handle := DBMS_SQL.OPEN_CURSOR;
  7        DBMS_SQL.PARSE(cursor_handle,'SET ROLE ALL',DBMS_SQL.V7);
  8       rc := dbms_sql.execute( cursor_handle );

  9* END;
SQL> /   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_SYS_SQL", line 348
ORA-06512: at "SYS.DBMS_SQL", line 141
ORA-06512: at "TKYTE.SET_ROLE", line 8
ORA-06512: at line 1

And you see that setting a role fails in a procedure.

On Thu, 26 Jun 97 20:24:07 +0400, "Victor L. Artchakov" <victor_at_rias.khv.ru> wrote:

>> Is anyone familiar with a way to issue a Set Role from within a stored
>> procedure? Thanks.
>>
>Hello!
>Use DBMS_SQL package.
>example:
>l
> 1 CREATE OR REPLACE PROCEDURE set_role
> 2 IS
> 3 cursor_handle INTEGER;
> 4 BEGIN
> 5 cursor_handle := DBMS_SQL.OPEN_CURSOR;
> 6 DBMS_SQL.PARSE(cursor_handle,'SET ROLE ALL',DBMS_SQL.V7);
> 7* END;
>SQL> /
>
>Procedure created.
>
>SQL> exec set_role
>
>PL/SQL procedure successfully completed.
>
>Best wishes
>Victor L. Artchakov
>Khabarovsk Russia
>victor_at_rias.khv.ru
>
>

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 Fri Jun 27 1997 - 00:00:00 CDT