CODEWITHSUNDEEP
oracle-databasestored-proceduressynonym
user1129209
user1129209

Reputation: 19

Synonym not working: USER.SYNONYM_NAME works but SYNONYM_NAME does not

I have the following issue:

I have created a synonym for a user in order to call a procedure from another schema, this is the output from the all_synonyms table log into sqlplus as USER_1:

OWNER   SYNONYM_NAME  TABLE_OWNER   TABLE_NAME      DB_LINK    ORIGIN_CON_ID
~~~~~   ~~~~~~~~~~~~  ~~~~~~~~~~~   ~~~~~~~~~~      ~~~~~~~    ~~~~~~~~~~~~~
USER_1  SYN_NAME      ADMIN         PROCEDURE_NAME  NULL       0

But I'm unable to run it using only the synonym name. If I do that I get an OORA-06550. Example:

exec SYN_NAME;
OORA-06550: line 1, column 7: PLS-00201: identifier 'SYN_NAME' must be declared

exec USER_1.SYN_NAME; //works as expected

I've already ran `grant execute on SYN_NAME to USER_1;

Is there anything else I need to set? I'm not sure what I'm missing. Thanks for the help in advance.

Upvotes: 0

Views: 4163

Answers (2)

Alex Poole
Alex Poole

Reputation: 191315

You can see that effect if you change your current_schema. If that matches your user then it works OK:

select user, sys_context( 'userenv', 'current_schema') as schema from dual;

USER                           SCHEMA                       
------------------------------ ------------------------------
USER_1                         USER_1                        

create synonym syn_name for admin.procedure_name;

Synonym SYN_NAME created.

exec syn_name;

PL/SQL procedure successfully completed.

But if my session has a different current_schema then the synonym has to be prefixed with my real user name:

alter session set current_schema = user_2;

Session altered.

select user, sys_context( 'userenv', 'current_schema') as schema from dual;

USER                           SCHEMA                       
------------------------------ ------------------------------
USER_1                         USER_2                        

select owner, synonym_name, table_owner, table_name
from all_synonyms where synonym_name = 'SYN_NAME';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                   
------------------------------ ------------------------------ ------------------------------ ------------------------------
USER_1                         SYN_NAME                       ADMIN                          PROCEDURE_NAME                

exec syn_name;

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

exec user_1.syn_name;

PL/SQL procedure successfully completed.

You may not know you're changing your current schema; it may be happening in a login trigger, possibly to avoid the need for synonyms. You can check your current schema with:

select sys_context( 'userenv', 'current_schema') as schema from dual;

If that actually shows you ADMIN then you don't need the synonym at all, and can just call PROCEDURE_NAME directly, with having to prefix it with the ADMIN schema name.

Upvotes: 1

Rene
Rene

Reputation: 10541

If I read this correctly the procedure exists in schema admin and you want user_1 to be able to call it.

As admin user:

grant execute on <procedure_name> to user_1;

As user_1:

create synonym <procedure_name> for admin.<procedure_name>;

Upvotes: 1

Related Questions