| 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: Development vs Test
A copy of this was sent to avilner_at_usa.net
(if that email address didn't require changing)
On Mon, 01 Mar 1999 19:02:52 GMT, you wrote:
>Hello,
>
>We have a schema (DEV) in the database, that owns all tables, indexes,
>sequences, etc. There is also a set of public synonyms on the objects. If I
>were to log in with my user ID (ABV), I can issue the following query:
>
> select count(*)
> from PROP_HDR_142;
>
>In this case, public synonyms are used to resolve the reference to the
>DEV.PROP_HDR_142.
>
>However, if I try to create procedure:
>
>create or replace procedure proc_test
>is
> i number;
>begin
> select count(*)
> into i
> from prop_hdr_142;
>end;
>/
>show errors;
>
>... I get the following error:
>7/7 PLS-00201: identifier 'DEV.PROP_HDR_142' must be declared
>5/2 PL/SQL: SQL Statement ignored
>
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 dev.prop_hdr_142 to <OWNER>;
>I have CREATE PROCEDURE privilege granted to user ABV, and ABV also has
>SELECt/INSERT/UPDATE/DELETE privileges on all of DEV's tables.
>
>Why would the SELECT work, but not the procedure?
>
>Thank you in advance.
>
>Alex Vilner
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |