Saturday, May 26, 2012

Script to Generate DDL for Synonyms


This script will produce DDL required to recreate a synonym.
REM LOCATION:   Object Management\Synonyms
REM FUNCTION:   Generate DDL for synonyms.
REM TESTED ON:  10.2.0.3, 11.1.0.6
REM PLATFORM:   non-specific
REM REQUIRES:   dba_synonyms
REM NOTES:      Prompts for synonym owner and synonym name. Wildcard accepted.
REM
REM
REM ******************** Oracle Administration ********************
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_LINK_NAME
SET long 1000
SET serveroutput on
SET verify off lines 132

DECLARE
   v_output         CLOB          := NULL;
   v_owner          VARCHAR2 (30) := '&&ENTER_OWNER_NAME';
   v_synonym_name   VARCHAR2 (30) := '&&ENTER_SYNONYM_NAME';
BEGIN
   DBMS_OUTPUT.put_line ('DDL For Database Synonyms');

   FOR tt IN (SELECT owner, synonym_name
                FROM dba_synonyms
               WHERE owner LIKE v_owner AND synonym_name LIKE v_synonym_name)
   LOOP
      SELECT DBMS_METADATA.get_ddl ('SYNONYM', tt.synonym_name, tt.owner)
        INTO v_output
        FROM DUAL;

      DBMS_OUTPUT.put_line (v_output);
   END LOOP;
END;
/