Table reference works in anonymous procedure, not in named procedure

From: Steve Hulcher <shulcher_at_hotmail.com>
Date: 30 Jun 2002 23:27:58 -0700
Message-ID: <c247855.0206302227.cb97fc3@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 - 01:27:58 CDT