Why do I get PLS-00302: component must be declared when it exists?

Question

I am using Oracle 10.2.

I am working in some scripts to move some ORACLE Objects from one SCHEMA (S1) to another (S2). I am creating the functions with DBA role. When moved, one of my functions becomes invalid, but I don't understand why. Its code goes along these lines:

MY_FUNC

CREATE OR REPLACE FUNCTION S2."MY_FUNC" RETURN VARCHAR2 IS
   something VARCHAR2;
   othervar VARCHAR2 (50):= 'TEST';   
BEGIN
   something := S2.MY_FUNC2();
    /*some code*/
    return othervar;
END;
/

If I use MY_FUNC2 without the schema, It works:
something := MY_FUNC2(); instead of something := S2.MY_FUNC2();

My_FUNC2

CREATE OR REPLACE FUNCTION S2."MY_FUNC2" RETURN VARCHAR2 IS
       something BOOLEAN;
       othervar VARCHAR2 (50) := 'TEST2';           
    BEGIN
       /*some code*/
        return othervar;
    END;
    /

MY_FUNC2 has a synonym like this:

 CREATE OR REPLACE PUBLIC SYNONYM "MY_FUNC2" FOR "S2"."MY_FUNC2"

MY_FUNC compiles with errors:

PLS-00302: component 'MY_FUNC2' must be declared

I don't understand why I am getting this error, when my functions were in the other schema (S1) they had exactly the same structure and the synonym was created exactly the same (but pointing to S1) and MY_FUNC compiled fine.

I didn't create this functions and synonym originally. Is it possible that I am missing some privileges in S2, so MY_FUNC can work properly?

Answer 1

You can get that error if you have an object with the same name as the schema. For example:

create sequence s2;

begin
  s2.a;
end;
/

ORA-06550: line 2, column 6:
PLS-00302: component 'A' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

When you refer to S2.MY_FUNC2 the object name is being resolved so it doesn't try to evaluate S2 as a schema name. When you just call it as MY_FUNC2 there is no confusion, so it works.

The documentation explains name resolution. The first piece of the qualified object name - S2 here - is evaluated as an object on the current schema before it is evaluated as a different schema.

It might not be a sequence; other objects can cause the same error. You can check for the existence of objects with the same name by querying the data dictionary.

select owner, object_type, object_name
from all_objects
where object_name = 'S2';