Re: Function to retrieve oracle roles

adam_at_ddisolutions.com.au (Adam C) 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

Adam,
package the function and use invoker rights (see metalink)

hth
Frans H. Received on Tue Jun 17 2003 - 05:16:48 CDT