CODEWITHSUNDEEP
oracle-databaseplsqlschemapackageprocedure
user1617237
user1617237

Reputation: 143

Why can't execute SCHEMA_NAME.PACKAGE_NAME.PROCEDURE, Oracle

I have a schema A, Package B and Procedure C. B is in A schema and C is in B's Package Body.

It works fine when I say:

Begin 
Exec B.C;
END;

But it throw an error when I say:

Begin
Exec A.B.C;
END;

Error report:
ORA-06550: line 2, column 12:
PLS-00302: component 'B' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

I log in as A so it's in A's schema. and SELECT * FROM user_OBJECTS WHERE OBJECT_NAME = 'B'; shows package and package body both valid.

Upvotes: 3

Views: 4934

Answers (2)

adimoise91
adimoise91

Reputation: 568

I had the same problem and found the issue. I will write here the answer to close this problem and help other people.

In my case, my user A have execute privilege on a procedure B(just a procedure, not a package, but it's the same). When the user tries to run: 

Begin 
Exec A.B;
END;

Get the error:

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

Problem: That's because a public synonym with name A was created in the database. This is an old database and I am just the DBA, not the developer, but in this case the developer was an uninspired developer. He used 4 types of objects with the same name: user, table, tablespace and public synonym. A public synonym named A in front of a table named A.

Solution: Because you don't know exactly who is using that public synonym I had to found another solution instead of deleting the public synonym. I created a private synonym for that procedure. Now the user can skip the owner of procedure in the execution code and ignore the public synonym. This problem appears in Oracle Database 10.2.0.4.

Begin 
Exec B;
END;
PL/SQL procedure successfully completed.

Conclusion: Never use a public/private synonym with the name of the schema.

Hope to help someone. If I didn't made myself clear, please leave a comment.

Upvotes: 3

stefannebesnak
stefannebesnak

Reputation: 740

Fix syntax error in the package, this is a generic PL/SQL compile error message.

Check error points (line 2, column 12) in the PL/SQL where the syntax error occurred, correct it and then try recompiling your code.

Component 'B' must be declared.

After that grant EXECUTE_CATALOG_ROLE to allow user 'A' execute privileges for packages and procedures in the data dictionary.

grant EXECUTE_CATALOG_ROLE to A;

Upvotes: -1

Related Questions