Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Using public synonymes in stored procedures
nicranet wrote:
> Hi,
>
> I have a problem with one pubilc synonyme I using in a stored
> procedures. I try to find the role of SP-execute-user with:
>
> SELECT granted_role INTO sRolle FROM DBA_ROLE_PRIVS where
> grantee=(SELECT USER FROM DUAL);
>
> But if I try to create the procedure with this statment, I get this
> error :
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 42/1 PL/SQL: SQL Statement ignored
> 42/38 PL/SQL: ORA-00942: Table or View does not exists
>
> But DBA_ROLE_PRIVS is a public synonyme. It means everone can read
> this table.
> I don't understand, what do I wrong?
>
> Can anybody help me? Thanks in advance!
>
> P.S. Sorry for my bad english! :-)
What Frank said and drop DUAL from your statement:
SELECT granted_role
INTO sRolle
FROM DBA_ROLE_PRIVS
WHERE grantee = USER;
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Feb 16 2007 - 10:50:13 CST