This certainly has to do with the fact that ROLEs are disabled in stored procedures.
To avoid this, you should either grant needed privileges directly to the owner
of the procedure, or define the procedure with AUTHID CURRENT_USER, which
will turn on runtime privilege checking for the procedure and it will execute with
privileges of the user who called it (much like anonymous blocks). If this is Oracle8,
not 8i, the second option is not available to you and the only way you can avoid
the issue is to grant privileges directly to the proc owner for all objects you want to
access within the proc.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Steve Hulcher" <shulcher_at_hotmail.com> wrote in message
news:c247855.0206302227.cb97fc3_at_posting.google.com...
> I'm a developer and usually depend on a DBA for the intracacies of
> setup on Oracle servers. The DBA I'm working with on my current
> project doesn't have much experience and has not been able to give me
> much direction on the following problem. Basically a synonym to a
> table on another Oracle server is visible to an anonymous plsql
> procedure, but not to a named procedure. All of this occurs within
> one schema, both servers are Oracle 8. A SQL*Plus log that
> demonstrates follows:
>
> *** THIS FAILS TO COMPILE ***
> |SQL> CREATE PROCEDURE TESTPROC
> | 2 IS
> | 3 l_delivery_id TESTSYN.DELIVERY_ID%TYPE;
> | 4 BEGIN
> | 5 DBMS_OUTPUT.PUT_LINE('made it here...');
> | 6 END;
> | 7
> | 8 /
> |
> |Warning: Procedure created with compilation errors.
> |
> |SQL> show errors
> |Errors for PROCEDURE TESTPROC:
> |
> |LINE/COL ERROR
> |-------- -----------------------------------------------------------------
> |3/15 PLS-00201: identifier 'WSH.TESTSYN' must be declared
> |3/15 PL/SQL: Item ignored
> |SQL>
> |
> |
> ***THIS COMPILES (AND RUNS) FINE***
> |SQL> DECLARE
> | 2 l_delivery_id TESTSYN.DELIVERY_ID%TYPE;
> | 3 BEGIN
> | 4 DBMS_OUTPUT.PUT_LINE('made it here...');
> | 5 END;
> | 6 /
> |made it here...
> |
> |PL/SQL procedure successfully completed.
> |
> |SQL>
>
>
> I'm going to try to get a complete script dump (of synonym, roles,
> etc.) but I haven't had access yet.
>
> I'd appreciate any pointers.
>
> Thanks
Received on Mon Jul 01 2002 - 06:03:39 CDT
