| 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: PL/SQL: select in other schema does'nt work ?
A copy of this was sent to Wouter Pels <wop_at_nfm.nl>
(if that email address didn't require changing)
On Mon, 26 Apr 1999 15:29:12 +0200, you wrote:
>why does a select statement like 'Select * from scott.emp' not work in a
>
>plsql procedure created & owned by a user granded with dba rights ?
>
>If the same stament is used in a sql session there is no problem, but
>when i try to compile the procedure, the table in the other schema
>is not recognized, also if a table synonym is used.
>
>Please can anybody tell me whats going wrong ?
>Thanks !
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 emp to <OWNER>;
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |