| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Using public synonym in a procedure
A copy of this was sent to Stanley Fung <sfung_at_mdsi.bc.ca>
(if that email address didn't require changing)
On Thu, 28 Jan 1999 14:24:57 -0800, you wrote:
>Hi, all,
>
>I have a strange problem, probably that's the way Oracle works.
>
>I have a procedure which selects data from a public synonym, ex named
>scott_emp. When I create the procedure, I have this error:
>
>PLS-00201: identifier 'scott.emp' must be declared
>
>In fact, I try this:
>select count(*) from scott_emp;
>select count(*) from scott.emp;
>
>Both work fine. If I do this in the procedure:
>create or replace procedure get_emp is
>...
> select count(*) from scott_emp;
>...
>
>I get PLS-00201 error.
>
>Has anyone seen this before? Or know what's wrong?
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant select on scott.emp to <owner>;
>Please send response directly to me, as I don't often read messages from
>newsgroup. Thanks in advance.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 28 1999 - 19:55:45 CST
 |
 |