| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Can't create cursor on dba_synonyms, what's wrong?
Roles are not enabled in procedures. The owner of a procedure must have the privelege needed to perform operations directly (or gotten them from public).
In the following, you undoubtably have access to dba_synonyms via the DBA role, while you have access to user_synonyms via a public grant.
Try this in sql*plus:
SQL> set role none;
SQL> select count(*) from sys.dba_synonyms;
You will probably see:
SQL> select count(*) from sys.dba_synonyms;
*
Once you get sys to grant access on dba_synonyms to the owner of the procedure, it'll work.
On Mon, 29 Sep 1997 02:20:52 GMT, gennick_at_worldnet.att.net (Jonathan Gennick) wrote:
>I am trying to create a cursor, in a PL/SQL procedure, on
>the dba_synonyms view, but can't seem to make it work. See
>below for some code which reproduces the error. However, I
>can create a cursor on user_synonyms. What's going on here?
>Why can I see one view and not the other? Is there some
>simple, boneheaded thing, which I am not doing correctly?
>
>Here's the code:
>
>SQL> create or replace procedure SynTest as
> 2 cursor EachSynonym is
> 3 select * from dba_synonyms;
> 4 begin
> 5 null;
> 6 end;
> 7 /
>
>Warning: Procedure created with compilation errors.
>
>SQL>
>SQL> show errors
>Errors for PROCEDURE SYNTEST:
>
>LINE/COL ERROR
>--------
>-----------------------------------------------------------------
>3/5 PL/SQL: SQL Statement ignored
>3/19 PLS-00201: identifier 'SYS.DBA_SYNONYMS' must be
>declared
>SQL>
>SQL> create or replace procedure SynTest as
> 2 cursor EachSynonym is
> 3 select * from user_synonyms;
> 4 begin
> 5 null;
> 6 end;
> 7 /
>
>Procedure created.
>
>SQL>
>
>
>regards,
>
>Jonathan Gennick
>
>gennick_at_worldnet.att.net
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |