Generating scripts using DBMS_METADATA

This is another example of using DBMS_METADATA to generate scripts.

SQL> /* My database version */
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> /* My PL/SQL block */
SQL>
SQL> DECLARE
  2    myddl clob;
  3    fname VARCHAR2(200);
  4    extn VARCHAR2(200);
  5 
  6  /* Main function to get the DDls with DBMS_METADATA */ 
  7    FUNCTION get_metadata(pi_obj_name  dba_objects.object_name%TYPE,
  8                          pi_obj_type  IN dba_objects.object_type%TYPE,
  9                          pi_obj_owner IN dba_objects.owner%TYPE) RETURN clob IS
 10      h   number;
 11      th  number;
 12      doc clob;
 13    BEGIN
 14      h := DBMS_METADATA.open(pi_obj_type);
 15      DBMS_METADATA.set_filter(h, 'SCHEMA', pi_obj_owner);
 16      DBMS_METADATA.set_filter(h, 'NAME', pi_obj_name);
 17      th := DBMS_METADATA.add_transform(h, 'MODIFY');
 18      th := DBMS_METADATA.add_transform(h, 'DDL');
 19      --DBMS_METADATA.set_transform_param(th,'SEGMENT_ATTRIBUTES',false);
 20      doc := DBMS_METADATA.fetch_clob(h);
 21      DBMS_METADATA.CLOSE(h);
 22      RETURN doc;
 23    END get_metadata;
 24 
 25  ---Main execution begins. 
 26  BEGIN
 27    FOR i in (SELECT object_name, object_type, owner
 28                FROM dba_objects
 29               WHERE owner IN ('SCOTT', 'HR')
 30                 AND object_type IN ('PACKAGE', 'TRIGGER')) LOOP
 31      --Calling the function.              
 32      myddl := get_metadata(i.object_name,i.object_type,i.owner);
 33      --Preparing the filename.
 34      fname := i.owner||i.object_name || '.' ||
 35               CASE WHEN i.object_type='PACKAGE' THEN 'pkb'
 36               ELSE 'trg'
 37               END;
 38     --Writing the file.
 39      DBMS_XSLPROCESSOR.clob2file(myddl, 'SAUBHIK', fname);
 40    END LOOP;
 41  END;
 42  /

PL/SQL procedure successfully completed.

SQL> 

In case, You can not use  DBMS_XSLPROCESSOR, then UTL_FILE is another way to write the files into disk.

SQL> DECLARE
  2    myddl clob;
  3    fname VARCHAR2(200);
  4    extn VARCHAR2(200);
  5 
  6  /* Main function to get the DDls with DBMS_METADATA */ 
  7    FUNCTION get_metadata(pi_obj_name  dba_objects.object_name%TYPE,
  8                          pi_obj_type  IN dba_objects.object_type%TYPE,
  9                          pi_obj_owner IN dba_objects.owner%TYPE) RETURN clob IS
 10      h   number;
 11      th  number;
 12      doc clob;
 13    BEGIN
 14      h := DBMS_METADATA.open(pi_obj_type);
 15      DBMS_METADATA.set_filter(h, 'SCHEMA', pi_obj_owner);
 16      DBMS_METADATA.set_filter(h, 'NAME', pi_obj_name);
 17      th := DBMS_METADATA.add_transform(h, 'MODIFY');
 18      th := DBMS_METADATA.add_transform(h, 'DDL');
 19      --DBMS_METADATA.set_transform_param(th,'SEGMENT_ATTRIBUTES',false);
 20      doc := DBMS_METADATA.fetch_clob(h);
 21      DBMS_METADATA.CLOSE(h);
 22      RETURN doc;
 23    END get_metadata;
 24 
 25  ---Writing the CLOB using UTL_FILE
 26  PROCEDURE write_clob(p_clob in clob, pi_fname VARCHAR2) as
 27      l_offset number default 1;
 28      fhandle UTL_FILE.file_type;
 29      buffer VARCHAR2(4000);
 30    BEGIN
 31      fhandle:=UTL_FILE.fopen('SAUBHIK',pi_fname,'A');
 32      loop
 33        exit when l_offset > dbms_lob.getlength(p_clob);
 34        buffer:=dbms_lob.substr(p_clob, 255, l_offset);
 35        UTL_FILE.put_line(fhandle,buffer);
 36        l_offset := l_offset + 255;
 37        buffer:=NULL;
 38      end loop;
 39      UTL_FILE.fclose(fhandle);
 40    END write_clob; 
 41 
 42  ---Main execution begins. 
 43  BEGIN
 44    FOR i in (SELECT object_name, object_type, owner
 45                FROM dba_objects
 46               WHERE owner IN ('SCOTT', 'HR')
 47                 AND object_type IN ('PACKAGE', 'TRIGGER')) LOOP
 48      --Calling the function.              
 49      myddl := get_metadata(i.object_name,i.object_type,i.owner);
 50      --Preparing the filename.
 51      fname := i.owner||i.object_name || '.' ||
 52               CASE WHEN i.object_type='PACKAGE' THEN 'pkb'
 53               ELSE 'trg'
 54               END;
 55     --Writing the file using UTL_FILE.
 56      write_clob(myddl,fname);
 57    END LOOP;
 58  END;
 59  /

PL/SQL procedure successfully completed.

SQL>