Re: Roles

[Quoted] [Quoted] No offense, but it would be nice to test your suggestions before posting a reply. ORACLE documentation clearly states that SET ROLE statement can not be called from stored procedure. It makes no difference how you call it: via SQL or dynamic SQL or any other way (e.g. DBMS_SESSION.SET_ROLE).
But even if it would be allowed via DBMS_SQL package, your stored procedure would not work. You assumed that SET ROLE is a DDL statement. It is not. SET ROLE is session control statement. Therefore DBMS_SQL.PARSE just parses it (not parse+execute like it is done for DDL statements). You need explicit DBMS_SQL.EXECUTE. And if you will add it, compile and execute your stored procedure it will tell you:

SQL> CREATE OR REPLACE PROCEDURE set_role(p_role_name IN VARCHAR2) IS

  2     cursor_handle INTEGER;
  3     rows_processed INTEGER;
  4  BEGIN
  5     cursor_handle := DBMS_SQL.OPEN_CURSOR;
  6     DBMS_SQL.PARSE(cursor_handle, 'set role ' ||
  7                    p_role_name, DBMS_SQL.V7);
  8     rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

Procedure created.

SQL> exec set_role('DUMMY');
begin set_role('DUMMY'); 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 134
ORA-06512: at "OPERATIONS.SET_ROLE", line 8
ORA-06512: at line 1

Solomon.Yakobson_at_entex.com

In article <32EFB364.1044_at_lilly.com>,   Chris Halioris <halioris_chris_nonlilly_at_lilly.com> wrote:
>
> Gustavo Tamaki wrote:
> >
> > Anyone know how to set a Role from a database procedure or database
> > function ??????
> >
> > Regards,
> >
> > Gustavo Tamaki
> > gstamaki_at_pobox.com
>
> I believe you could use DBMS_SQL to issue the dynamic SQL that would do
> it. If I recall correctly off the top of my head, for DML statements you
> must open a cursor then the parse will actually execute it. Might look
> something like:
>
> CREATE OR REPLACE PROCEDURE set_role(p_role_name IN VARCHAR2) IS
> cursor_handle INTEGER;
> BEGIN
> cursor_handle := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_handle, 'set role ' ||
> p_role_name, DBMS_SQL.V7);
> END;
>
> Basically, construct any DML statement you wish to execute in the second
> parameter of the call to DBMS_SQL.PARSE.
>
> HTH,
> Chris Halioris
> Tactics, Inc.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet