Re: Roles

From: Chris Halioris <halioris_chris_nonlilly_at_lilly.com>
Date: 1997/01/29
Message-ID: <32EFB364.1044_at_lilly.com>#1/1

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. Received on Wed Jan 29 1997 - 00:00:00 CET