Re: Table reference works in anonymous procedure, not in named procedure

shulcher_at_hotmail.com (Steve Hulcher) 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

Please search the archives. This is a FAQ (asked and answered at least once a week)

Hth

Sybrand bakker
Senior Oracle DBA Received on Mon Jul 01 2002 - 06:45:31 CDT