PLS-00302 When PUBLIC SYNONYM has the same name as the SCHEMA

(Doc ID 399983.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.1
This problem can occur on any platform.

Symptoms

When a synonym is created with the same name as the schema, an error is returned when attempting to execute a stored procedure that uses the schema reference.

Database versions:
--------------------------

11.1.0.2              - fails with PLS-00302 -- this is the correct behavior.
10.2.0.1              - fails with PLS-00302     -- this is the correct behavior.
10.2.0.2              - fails with PLS-00302     -- this is the correct behavior.
9.2.0.8                - fails  with PLS-00302    -- This is the correct behavior.
9.2.0.6, 9.2.0.7   - works with no errors      -- this is not the correct behavior.


Steps To Reproduce:

As SYSTEM - grant the privs to SCOTT
--------------------------------------------
SQL> grant create public synonym to scott;


As SCOTT - execute the following commands
------------------------------------------------------------- 

CREATE OR REPLACE PROCEDURE purge_old_tasks (p_times IN NUMBER) AS
   l_dummy NUMBER;
BEGIN
   FOR i IN 1 .. p_times LOOP
      SELECT l_dummy + 1 INTO l_dummy FROM dual;
   END LOOP;
END;
/


Procedure created.

Run the procedure
--------------------------
SQL> execute SCOTT.purge_old_tasks(3);
PL/SQL procedure successfully completed.

Creating a public synonym with the same name as the schema
-------------------------------------------------------------------------
SQL> create public synonym scott for dual;
Synonym created.

The procedure now fails:
---------------------------------------------------------
SQL> execute SCOTT.purge_old_tasks(3);
BEGIN testcase.purge_old_tasks(3); END;

ERROR at line 1:
ORA-06550: line 1, column 16:
PLS-00302: component 'PURGE_OLD_TASKS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms