CODEWITHSUNDEEP
sqloracle-databasestored-proceduressynonym
Novice_Techie
Novice_Techie

Reputation: 444

How to flip Oracle Synonyms between tables

I have below two tables.

A_DETAILS

B_DETAILS

I have below two SYNONYM created on the above tables

A_DETAILS points to --> L_DETAILS

B_DETAILS points to --> R_DETAILS

I want to flip synonym at some point of time like

A_DETAILS points to --> R_DETAILS

B_DETAILS points to --> L_DETAILS

I tried the code below but it creating both synonym of a single table and removing from the other table.

BEGIN

   FOR reg IN 
(SELECT SYNONYM_NAME, TABLE_NAME, OWNER
            FROM ALL_SYNONYMS
           WHERE OWNER IN ('MY_SCHEMA') AND SUBSTR (TABLE_NAME, 1, 2) = 'A_')
   LOOP
      v_Statement := 'CREATE OR REPLACE SYNONYM '|| reg.owner|| '.'|| reg.SYNONYM_NAME|| ' FOR '|| reg.owner|| '.'|| 'B_'|| SUBSTR (reg.TABLE_NAME, 3);

      EXECUTE IMMEDIATE v_Statement;
   END LOOP;
      FOR reg
      IN (SELECT SYNONYM_NAME, TABLE_NAME, OWNER
            FROM ALL_SYNONYMS
           WHERE OWNER IN ('MY_SCHEMA') AND SUBSTR (TABLE_NAME, 1, 2) = 'B_')
   LOOP
      v_Statement :='CREATE OR REPLACE SYNONYM '|| reg.owner|| '.'|| reg.SYNONYM_NAME|| ' FOR '|| reg.owner|| '.'|| 'A_'|| SUBSTR (reg.TABLE_NAME, 3);

      EXECUTE IMMEDIATE v_Statement;
   END LOOP;
   END;

Upvotes: 1

Views: 182

Answers (1)

Sentinel
Sentinel

Reputation: 6449

Do it in one loop instead of two. By the time you are getting to the second loop, the first synonym has been updated to point to B_, so the second loop is updating both synonyms to point back to A_.

BEGIN
  FOR reg IN (
    SELECT SYNONYM_NAME, TABLE_NAME, OWNER
         , case SUBSTR (TABLE_NAME, 1, 2)
             when 'A_' then 'B_'
             when 'B_' then 'A_'
           end || SUBSTR (TABLE_NAME, 3) NEW_TABLE
      FROM ALL_SYNONYMS
     WHERE OWNER IN ('MY_SCHEMA') AND SUBSTR (TABLE_NAME, 1, 2) in ('A_','B_'))
  LOOP
    v_Statement := 'CREATE OR REPLACE SYNONYM '|| reg.owner|| '.'|| reg.SYNONYM_NAME
                || ' FOR '|| reg.owner|| '.'|| reg.NEW_TABLE;

    EXECUTE IMMEDIATE v_Statement;
  END LOOP;
END;

Upvotes: 3

Related Questions