Results 1 to 5 of 5

Thread: set role

  1. #1
    Join Date
    Aug 2002
    Posts
    7

    Unanswered: set role

    the question is can we execute statements as 'set role' in a stored procedure .if so how????

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Depending on your version of Oracle you can use the EXECUTE IMMEDIATE statement, ie:
    Code:
    PROCEDURE doodad IS
    
    BEGIN
    
       EXECUTE IMMEDIATE ('SET ROLE rolename');
    
       .... do stuff ...
    
      EXECUTE IMMEDIATE ('SET ROLE NONE');
    
    END doodad;

  3. #3
    Join Date
    Aug 2002
    Posts
    7

    set role

    hi
    thanks for your prompt reply however here is the code that i wrote and here is the problem


    create or replace procedure execute_role as
    begin
    EXECUTE IMMEDIATE ('Set role some_ROLE identified by test');
    end;


    and here is the error

    ERROR at line 1:
    ORA-06565: cannot execute SET ROLE from within stored procedure
    ORA-06512: at "SON.EXECUTE_ROLE", line 3
    ORA-06512: at line 1

    here i have already tried the following code:

    create or replace procedure execute_role as
    str varchar2(200);

    begin

    str := 'set role some_role identified by test';
    execute immediate str;
    end;

    and i got the same error as above.

    I believe that there is some way to seet a role in a stored procedure as u can grant role in a stored procedure

    Awaiting your reply eagerly
    invinciblesummer2000

  4. #4
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Sorry... I should have guessed you'd tried the obvious. That method does work in 9.0.1 by the way.

    Here are two more methods:

    This one might give you the same problems as before
    Code:
    begin
    dbms_utility.exec_ddl_statement('SET ROLE rolename');
    end;
    But this should work:
    Code:
    begin
    dbms_session.set_role('rolename');
    end;

  5. #5
    Join Date
    Aug 2002
    Posts
    7

    set role

    hi
    thanks for the encouragement and insights you provided.i have finally hit the jackpot.I created the stored proceedure using invoker rights and it did work .If u are not creating the proceedure with invoker rights then dbms session also does not work
    Thanks a lot
    invinciblesummer2000

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •