Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Function to retrieve oracle roles

Re: Function to retrieve oracle roles

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 17 Jun 2003 09:24:41 +0100
Message-ID: <3eeed049$0$18487$ed9e5944@reading.news.pipex.net>


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
Received on Tue Jun 17 2003 - 03:24:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US