Re: A role identified by a package - ORA-06565

Vsevolod Afanassiev wrote:

> I am trying to create a role identified by a package, the
> creation part works, but when I try to use it, I get
> ORA-06565: cannot execute SET ROLE from withing stored procedure
>
> SQL> create role test_role identified by test_pkg;
>
> Role created.
>
> SQL> create or replace package test_role_auth as
> 2 procedure set_role;
> 3 end test_role_auth;
> 4 /
>
> Package created;
>
> SQL> create or replace package body test_roleauth as
> 2 procedure set_role is
> 3 begin
> 4 dbms_session.set_role('TEST_ROLE');
> 5 end set_role;
> 6 end test_role_auth;
> 7 /
>
> Package body created;
>
> SQL> connect /
> Connected.
> SQL> begin
> 2 test_role_auth.set_role;
> 3 end;
> 4 /
> begin
> *
> ERROR at line 1
> ORA-06565: cannot execute SET ROLE from within stored procedure
> ORA-06512: at "SYS.DBMS_SESSION", line 120
> ORA-06512: at "OPS$ORACLE.TEST_ROLE_AUTH", line 4
> ORA-06512: at line 2
>
> Any ideas?
>
> Thanks,
> Sev
>

Hi

If the role got a password (identified by) then the set role must look like: set role <role_name> identified by <password>. I gues the same thing applies to your package. So you need to extent it.

/svend Received on Fri Jan 11 2002 - 13:03:53 CST