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;

/

Find unused variables in PLSQL package

Oracle provided view ALL_IDENTIFIERS can be used to identify variables and constants which are decalred but never used. Many of the Integrated Development Environments like Toad and PLSQL developer issues warnings at compile time on unused varaibles.

Step 1 :- Set the PLSCOPE_SETTINGS session variable to IDENTIFIERS:ALL for the session.

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

Step 2 :- Recompile the package so that all identifiers are added to ALL_IDENTIFIERS view.

ALTER PACKAGE <> COMPILE;

Step 3 :- Execute the SQL below to identify all the unused variables and constants in the package 

WITH identifiers_inline AS(  

SELECT *

FROM all_identifiers ide

WHERE object_name = '&Package'

AND object_type = 'PACKAGE BODY')

--

SELECT NAME, 
       TYPE,
       line,
       col

FROM identifiers_inline ide

WHERE TYPE IN ('VARIABLE', 'CONSTANT')

AND usage = 'DECLARATION'

AND (NOT EXISTS 

      (SELECT 1

       FROM identifiers_inline

       WHERE signature = ide.signature

       AND usage <> 'DECLARATION')  

      OR  

     NOT EXISTS 

      (SELECT 1

       FROM identifiers_inline

       WHERE signature = ide.signature

       AND usage <> 'DECLARATION'

       AND line <> ide.line

       AND col <> ide.col));

Finally, the below script can be used to check for unused identifiers in packages identified by the package filter parameter. The output will be done in dbms output. Please note that the script recompiles every package fetch by the package filter parameter.

DECLARE

  l_package_filter VARCHAR2(30) := '&package_filter';

  CURSOR cur_packages IS

    SELECT object_name

    FROM all_objects

    WHERE object_name LIKE l_package_filter

    AND object_type = 'PACKAGE BODY';

  CURSOR cur_identifiers IS WITH identifiers_inline AS(

    SELECT object_name package_name,

           NAME identifier_name,

           TYPE,

           line,

           col,

           usage,

           signature

    FROM all_identifiers ide

    WHERE object_name LIKE l_package_filter

    AND object_type = 'PACKAGE BODY')

 

      SELECT package_name,

        identifier_name,

        TYPE,

        line,

        col

      FROM identifiers_inline ide

      WHERE TYPE IN ('VARIABLE', 'CONSTANT')

      AND usage = 'DECLARATION'

      AND (NOT EXISTS (SELECT 1

                      FROM identifiers_inline

                      WHERE signature = ide.signature

                      AND usage <> 'DECLARATION') OR NOT EXISTS (SELECT 1

                             

                                FROM identifiers_inline

                                                              WHERE signature = ide.signature

                                                              AND usage <> 'DECLARATION'

                                                              AND line <> ide.line

                                                              AND col <> ide.col));

BEGIN

  EXECUTE IMMEDIATE 'ALTER SESSION SET PLSCOPE_SETTINGS=''IDENTIFIERS:ALL''';

  FOR rec_packages IN cur_packages

  LOOP

    EXECUTE IMMEDIATE 'ALTER PACKAGE ' || rec_packages.object_name || ' COMPILE';

  END LOOP;

  --

  dbms_output.put_line(rpad('Package Name',

                              31) || rpad('Identifier Name',

                                          31) || rpad('Type',

                                                      15) || rpad('Line',

                                                                  7) || rpad('Column',

                                                                             7));

  dbms_output.put_line(RPAD('*',91,'*'));

  --

  FOR rec_identifiers IN cur_identifiers

  LOOP

    dbms_output.put_line(rpad(rec_identifiers.package_name,

                              31) || rpad(rec_identifiers.identifier_name,

                                          31) || rpad(rec_identifiers.TYPE,

                                                      15) || rpad(rec_identifiers.line,

                                                                  7) || rpad(rec_identifiers.col,

                                                                             7));

  END LOOP;

END;