Oracle Synonyms issue

Question

My Scenario:

• Schema name: schema1
• Package name: pkg_system
• procedure name: proc1

Now I am trying to create a synonyms for my proc1 as below

CREATE PUBLIC SYNONYM call_proc FOR schema1.pkg_system.proc1;

...but it gave me syntax error.

ORA-00933: SQL command not properly ended

I changed the code as below:

CREATE PUBLIC SYNONYM call_proc FOR pkg_system.proc1;

I can successfully create the synonyms but when I tried to execute the stored procedure via the synonym:

EXEC call_proc

...got the following error:

ORA-06550: line 1, column 7:
PLS-00201: identifier call_proc must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

And I used the schema name to call the procedure like schema1.call_Proc still got the same error.

What I did wrong here?

Answer 1

One way to get around this limitation, assuming you really need to call the procedure with a single name (for whatever reason), you could wrap it in a schema-level procedure:

CREATE PROCEDURE schema1.proc1 IS
BEGIN
   pkg_system.proc1;
END;

CREATE PUBLIC SYNONYM proc1 FOR schema1.proc1;

Answer 2

Note here

Restriction on the FOR Clause The schema object cannot be contained in a package.

In other words, you can't create a synonym for a package procedure.

I've seen solutions where a wrapper procedure (non packaged) is created and a public synonym created for that.