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


i have following issue:

i have created synonym user in order call procedure schema, output all_synonyms table log sqlplus 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 run using synonym name. if oora-06550. example:

exec syn_name; oora-06550: line 1, column 7: pls-00201: identifier 'syn_name' must declared  exec user_1.syn_name; //works expected 

i've ran `grant execute on syn_name user_1;

is there else need set? i'm not sure i'm missing. in advance.

you can see effect if change current_schema. if matches user works ok:

select user, sys_context( 'userenv', 'current_schema') schema dual;  user                           schema                        ------------------------------ ------------------------------ user_1                         user_1                          create synonym syn_name admin.procedure_name;  synonym syn_name created.  exec syn_name;  pl/sql procedure completed. 

but if session has different current_schema synonym has prefixed real user name:

alter session set current_schema = user_2;  session altered.  select user, sys_context( 'userenv', 'current_schema') schema dual;  user                           schema                        ------------------------------ ------------------------------ user_1                         user_2                          select owner, synonym_name, table_owner, table_name all_synonyms 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 declared ora-06550: line 1, column 7: pl/sql: statement ignored  exec user_1.syn_name;  pl/sql procedure completed. 

you may not know you're changing current schema; may happening in login trigger, possibly avoid need synonyms. can check current schema with:

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

if shows admin don't need synonym @ all, , can call procedure_name directly, having prefix admin schema name.


Comments

Popular posts from this blog

magento2 - Magento 2 admin grid add filter to collection -

Android volley - avoid multiple requests of the same kind to the server? -

Combining PHP Registration and Login into one class with multiple functions in one PHP file -