Re: Function to retrieve oracle roles

Try

CREATE OR REPLACE FUNCTION GetRoles return varchar2 /* run as interactive user */
authid current_user
as
sRoles varchar(1000);

   CURSOR icurs IS
SELECT *
FROM SESSION_ROLES;
   BEGIN
  For rec IN icurs
loop
sRoles := sRoles || ';' || rec.ROLE;
end loop;
return sRoles;
End;
/

and lookup authid current_user for an explanation as to why this works and you code doesn't.

cheers

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
"Adam C" <adam_at_ddisolutions.com.au> wrote in message
news:8bdc35cd.0306162257.2c64ffe_at_posting.google.com...


> Hello guru's

>

> I am trying to write a function that will retrieve the current users

> roles. From the PL/SQL prompt I can "Select * FROM SESSION_ROLES;" and it

> happily lists all the roles for the current session. However when i try

> to build a function to get the roles (via a cursor) i dont seem to ever

> get any roles in the cursor.

>

> This is my Function:

> CREATE OR REPLACE FUNCTION GetRoles return varchar2 as

>    sRoles varchar(1000);

>

>    CURSOR icurs IS

> SELECT *

> FROM SESSION_ROLES;

>

>    BEGIN

>   For rec IN icurs

> loop

> sRoles := sRoles || ';' || rec.ROLE;

> end loop;

>

> return sRoles;

> End;

>

> Can anyone point out where I have gone wrong or what else I need to do

> to enable this function.

>

> I am developing in Win2K using Oracle8i.

>

> Thanks in advance.

>

> Adam C