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.

score 30 · Accepted Answer · answered May 07 '12 at 09:44

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:

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

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 ".

score 1 · Answer 2 · edited May 07 '12 at 10:36

1

I think if you removed the OR REPLACE keyword it will prompt you that it exist

Or you can create pl/sql code using these tables

desc dba_synonyms
desc user_synonyms

To make it more flexible and customized

Assuming Oracle PL/SQL

DECLARE
    src_schema    VARCHAR2(256) := 'EMPIK_DYNAMO_01';
    target_schema VARCHAR2(256) := 'EMPIK_PORTAL_BETA_1';
    CURSOR src_objects IS
      SELECT table_name AS object_name
      FROM   all_all_tables
      WHERE  owner = src_schema
      UNION
      SELECT sequence_name AS object_name
      FROM   all_sequences
      WHERE  sequence_owner = src_schema;
BEGIN
    FOR next_row IN src_objects LOOP
        BEGIN
            EXECUTE IMMEDIATE 'CREATE or REPLACE SYNONYM '|| target_schema|| '.'
            ||
            next_row.object_name|| ' for '|| src_schema|| '.'||
            next_row.object_name;
        EXCEPTION
            WHEN OTHERS THEN
              dbms_output.Put_line('ERROR WHILE CREATING SYNONYM FOR: '
                                   || next_row.object_name);

              dbms_output.Put_line(SQLERRM);
        END;
    END LOOP;
END;

/

Here the customization for your problem

BEGIN
    EXECUTE IMMEDIATE 'CREATE or REPLACE SYNONYM ETKS_PR_RW.SQ_CLDOS_ATCHMNT_ID FOR CLDOS_ONLINE_DBA.SQ_CLDOS_ATCHMNT_ID';  
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line ('ERROR WHILE CREATING SYNONYM FOR: SQ_CLDOS_ATCHMNT_ID');
      dbms_output.Put_line (SQLERRM);
END;

edited May 07 '12 at 10:36

Sathyajith Bhat

21,321
22
95
134

answered May 07 '12 at 08:04

shareef

9,255
13
58
89

can you tell me syntax for option 2. This is what I am looking for. – SOF User May 07 '12 at 08:09
here a guide example http://snipplr.com/view/39190/create-synonyms/ am sure this is what you want i hope give me accept answer thankx in advance – shareef May 07 '12 at 08:10
hay bro this is more complex and what I think It will copy all objects. – SOF User May 07 '12 at 08:22
please provide more info your question is ambigous:if you wish create one syn and detect the existance you choose option 1 above if you want create syn for objects in schema you choose option 2 and put the appropriate where please give me you problem with more details !! and edit you question – shareef May 07 '12 at 08:42
Can you just post script according to my scenario where I can above create synonym with check if it already exist then don't create. – SOF User May 07 '12 at 08:48
If you remove replace it won't prompt you, your block will never throw an error and _never_, _ever_ `dbms_output.put_line` errors. There's no point at all and it just covers over errors you should be dealing with. – Ben May 07 '12 at 09:46
my explanation may be misunderstood any way i meant in sql editor CREATE or REPLACE SYNONYM ETKS_PR_RW.SQ_CLDOS_ATCHMNT_ID FOR CLDOS_ONLINE_DBA.SQ_CLDOS_ATCHMNT_ID tiwce will show ora error thats the first option and the second option i posted i tested it on toad and it gave me and error in dbms output window! thanks you – shareef May 07 '12 at 10:41
@shareef, it wasn't me who down-voted you but I disagree with what you're saying. The second one may give you an error, but only if you don't have the correct permissions. `dbms_output.put_line` is a _very_ bad way of dealing with errors. You have to be there to notice them! Also, I really don't understand the reasoning for your latest update. This is massively over-complicated. – Ben May 07 '12 at 10:47
@Ben okay thanks i learned somthing about dbms and i guess your right about the correct permissions ... i have to be more experienced in understanding questions "but i still think the question was general" :) – shareef May 07 '12 at 10:51

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

2 Answers2