Script to back up PLSQL code and other database Objects

One of the drawbacks of writing PLSQL code directly in an IDE like Toad or SQL developer is that the developers have to explicitly back up their code and other database objects to source control or local file system. Though this is left to developer's discipline, often it happens that after a refresh of development instance some code will be identified as missing.

The below script can be used to back up the required code files all at one go. The driving criteria can be modified according to the requirement of individual projects. The code will be backed up as files in the database server in the specified location one file per database object.

DECLARE

  v_ddl_statement CLOB;

  v_index         NUMBER;

  v_length        NUMBER;

  c_chunk_size    NUMBER := 4000;

  v_chunk_size    NUMBER;

  c_path          VARCHAR2(100) := '/usr/tmp'; --Edit this to an ORacle directory or a valid unix path with utl_file write permission

  v_file_name     VARCHAR2(100);

  v_data_chunk    VARCHAR2(32767);

  v_handle        utl_file.file_type;

  /*Edit the below cursor to set the selection criteria according to your requirements*/

  CURSOR cur_object IS

    SELECT object_type,

           owner,

           object_name

    FROM dba_objects

    WHERE object_type NOT IN ('PACKAGE BODY', 'SCHEMA', 'DIRECTORY') --These object types are not supported. Package will extract both spec and body

    /* Edit the below conditions as suitable*/

    AND object_name LIKE 'XXBIZ%EAM%'

    --AND created > SYSDATE - 5

    AND rownum < 5;

BEGIN

  FOR object_rec IN cur_object

  LOOP

    v_file_name := object_rec.object_name || '_' || object_rec.object_type || '.sql';

    dbms_output.put_line(object_rec.object_name || ' to ' || v_file_name);

    SELECT dbms_metadata.get_ddl(object_rec.object_type,

                                 object_rec.object_name,

                                 object_rec.owner)

    INTO v_ddl_statement

    FROM dual;

    v_index  := 1;

    v_length := dbms_lob.getlength(v_ddl_statement);

    v_handle := utl_file.fopen(c_path,

                               v_file_name,

                               'W',

                               32767);

    LOOP

      v_chunk_size := least(c_chunk_size,

                            v_length - v_index);

      v_data_chunk := dbms_lob.substr(v_ddl_statement,

                                      v_chunk_size,

                                      v_index);

      utl_file.put_line(v_handle,

                        v_data_chunk);

      EXIT WHEN v_chunk_size < c_chunk_size;

      v_index := v_index + v_chunk_size;

    END LOOP;

    utl_file.put_line(v_handle,

                      '/');

    utl_file.put_line(v_handle,

                      '');

    utl_file.fclose(v_handle);

  END LOOP;

END;

/