Re: Set Role From a Stored Procedure

From: Victor L. Artchakov <VICTOR_at_rias.khv.ru>
Date: 1997/06/27
Message-ID: <ABPewTtegN@rias.khv.ru>#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.
> >

What about my spool file?

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;
 10 /

Procedure created.

SQL> exec set_role;

PL/SQL procedure successfully completed. Received on Fri Jun 27 1997 - 00:00:00 CDT