How can I check that if synonym already exist then don't create synonym

Question

I am using Oracle SQL developer 2.1 for creating a synonym.

CREATE OR REPLACE SYNONYM "ETKS_PR_RW"."SQ_CLDOS_ATCHMNT_ID" 
    FOR "CLDOS_ONLINE_DBA"."SQ_CLDOS_ATCHMNT_ID";

How can I check that if this synonym already exists then don't create the synonym if it does.

Answer 1

As you're using the replace keyword there is no need to check whether the synonym exists first. You will over-write whatever synonym existed with the previous name.

The only reason to be wary of using replace is if you might have a different synonym with the same name. If your database is organised well this shouldn't happen. You should always know what all of your objects are and where the synonyms point.

However, if you do want to there are a couple of options:

1. Remove replace. The statement will throw an error if the synonym already exists and won't get over-written.

2. Query the data-dictionary, as you're in multiple schemas all_synonyms seems like the best bet.

   select *
     from all_synonyms
    where owner = 'ETKS_PR_RW'
      and synonym_name = 'SQ_CLDOS_ATCHMNT_ID';
   

If you want to combine these into a single block then you can do something like this:

declare

   l_exists number;

begin
   -- check whether the synonym exists
   select 1
     into l_exists
     from all_synonyms
    where owner = 'ETKS_PR_RW'
      and synonym_name = 'SQ_CLDOS_ATCHMNT_ID';

-- an error gets raise if it doesn-t.
exception when no_data_found then
   -- DDL has to be done inside execute immediate in a block.
   execute immediate 'CREATE OR REPLACE SYNONYM ETKS_PR_RW.SQ_CLDOS_ATCHMNT_ID 
                   FOR CLDOS_ONLINE_DBA.SQ_CLDOS_ATCHMNT_ID';

end;
/

On a slightly separate not please do not quote your object names. Oracle can have cased objects, but it is very, very rarely worth the hassle. All objects will be upper-cased automatically so you don't need the ".