export.sql

REM export.sql
REM Version 1.0, last updated 5/9/97
REM This package implements a 'PL/SQL schema exporter'.  It will query
REM the data dictionary views all_source, all_objects and/or all_triggers
REM to determine the source code for PL/SQL objects of type PROCEDURE,
REM FUNCTION, PACKAGE, PACKAGE BODY, or TRIGGER, and then write them out
REM to a file using UTL_FILE.  This file can then be read in using SQL*Plus
REM to recreate the objects.

REM Because this package uses both UTL_FILE and cursor variables, it requires
REM PL/SQL 2.3 (Oracle 7.3) or higher.  For more information, see
REM Chapter 8 of _Oracle8 PL/SQL Programming_ by Scott Urman.

CREATE OR REPLACE PACKAGE Export AS

  /* Exports one object.  The parameters are used as follows:
   * p_Schema:    Specifies the owner of the object to be exported
   * p_ObjType:   Specifies the type of the object.  Valid values are 'PACKAGE',
   *              'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', or 'TRIGGER'.  If NULL,
   *              then the object type is determined by selecting from all_objects.
   * p_BothTypes: If TRUE, when p_ObjType = 'PACKAGE' both the package and package
   *              body will be exported.
   * p_FileDir:   Directory where the output file should be created.
   * p_FileName:  Name of the output file.
   * p_Mode:      Mode (either 'A' to append or 'W' to write) of the output file.
   */ 
  PROCEDURE OneObj(p_Schema IN VARCHAR2,
                   p_ObjName IN VARCHAR2,
                   p_ObjType IN VARCHAR2 DEFAULT NULL,
                   p_BothTypes IN BOOLEAN DEFAULT TRUE, 
                   p_FileDir IN VARCHAR2,
                   p_FileName IN VARCHAR2,
                   p_Mode IN VARCHAR2);
                   
  /* Exports all objects of a given type.  The parameters are used as follows:
   * p_Schema:    Specifies the owner of the objects to be exported
   * p_ObjType:   Specifies the type of the object.  Valid values are 'PACKAGE',
   *              'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', or 'TRIGGER'.  If
                  specified, than all objects owned by p_Schema of the given type
                  will be exported.  If NULL, then all PL/SQL objects owned by
                  p_Schema will be exported.
   * p_FileDir:   Directory where the output file should be created.
   * p_FileName:  Name of the output file.
   * p_Mode:      Mode (either 'A' to append or 'W' to write) of the output file.
   */
  PROCEDURE AllObjs(p_Schema IN VARCHAR2,
                    p_ObjType IN VARCHAR2 DEFAULT NULL,
                    p_FileDir IN VARCHAR2,
                    p_FileName IN VARCHAR2,
                    p_Mode IN VARCHAR2);
END Export;
/

CREATE OR REPLACE PACKAGE BODY Export AS
  
  /* This is the work horse procedure for the package.  OutputObj will output one
   * object, specified by p_Schema, p_ObjName, and p_ObjType, to the file specified
   * by p_FileHandle.  The file must have already been opened for writing
   * (in 'W' or 'A' mode).
   */
  PROCEDURE OutputObj(p_FileHandle IN OUT UTL_FILE.FILE_TYPE,
                      p_Schema IN VARCHAR2,
                      p_ObjName IN VARCHAR2,
                      p_ObjType IN VARCHAR2) IS

    /* These variables are used to retreive the trigger text. Since all_triggers
     * stores a trigger body as a LONG, we have to use DBMS_SQL to fetch the long
     * in chunks.  */
    v_SQLStmt VARCHAR2(200) :=
      'SELECT description, trigger_body
         FROM all_triggers
         WHERE owner = :v_owner
         AND trigger_name = :v_name';
    v_Cursor         INTEGER;
    v_NumRows        INTEGER;
    v_Dummy          INTEGER;
    v_Description    all_triggers.description%TYPE;
    v_BodyChunk      VARCHAR2(100);
    v_ChunkSize      NUMBER := 100;
    v_CurPos         NUMBER := 0;
    v_ReturnedLength NUMBER := 0;

    /* These variables are used to retrieve the source for other types of objects.
     * DBMS_SQL is not necessary here, since all_source stores each line of source
     * separately.
     */
    v_TextLine all_source.text%TYPE;
    CURSOR c_ObjCur IS
      SELECT text
        FROM all_source
        WHERE owner = p_Schema
        AND name = p_ObjName
        AND type = p_ObjType
        ORDER BY line;

  BEGIN
    -- First, write the 'CREATE OR REPLACE ' to the file.
    UTL_FILE.PUT(p_FileHandle, 'CREATE OR REPLACE ');
    
    IF (p_ObjType = 'TRIGGER') THEN
      BEGIN
        -- Output the object type (TRIGGER in this case) to the file.
        UTL_FILE.PUT(p_FileHandle, 'TRIGGER ');
        
        -- Open the cursor and parse the statement.
        v_Cursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_Cursor, v_SQLStmt, DBMS_SQL.V7);
        
        -- Bind the input variables to the placeholders.
        DBMS_SQL.BIND_VARIABLE(v_Cursor, ':v_owner', p_Schema);
        DBMS_SQL.BIND_VARIABLE(v_Cursor, ':v_name', p_Objname);
        
        -- Define the output variables.  Note the use of DEFINE_COLUMN_LONG for
        -- the trigger body text.
        DBMS_SQL.DEFINE_COLUMN(v_Cursor, 1, v_Description, 2000);
        DBMS_SQL.DEFINE_COLUMN_LONG(v_Cursor, 2);
        
        -- Execute the statement, and fetch the row.  We don't have to fetch in a 
        -- loop since there is only one row per trigger.
        v_Dummy := DBMS_SQL.EXECUTE(v_Cursor);
        v_NumRows := DBMS_SQL.FETCH_ROWS(v_Cursor);
        
        -- Retreive the value for the trigger description, and output it to the file.
        -- Note that we can use UTL_FILE.PUT rather than UTL_FILE.PUT_LINE, since the
        -- table contains the trailing newline character.
        DBMS_SQL.COLUMN_VALUE(v_Cursor, 1, v_Description);
        UTL_FILE.PUT(p_FileHandle, v_Description);
        
        -- Loop until we've retreived the entire trigger body.  We will retreive
        -- v_ChunkSize characters each loop iteration.
        LOOP
          DBMS_SQL.COLUMN_VALUE_LONG(v_Cursor, 2, v_ChunkSize, v_CurPos,
                                     v_BodyChunk, v_ReturnedLength);
                                     
          IF v_ReturnedLength < v_ChunkSize THEN
            -- We've just retreived the last chunk.  For some reason, an extra NULL
            -- character is stored in the table after the newline.  So we need to
            -- remove it first.
            v_BodyChunk := SUBSTR(v_BodyChunk, 1, LENGTH(v_BodyChunk) - 1);
            
            -- Output the trimmed chunk, and exit the loop.
            UTL_FILE.PUT(p_FileHandle, v_BodyChunk);
            EXIT;
          ELSE
            -- We've retreived a chunk from the middle of the long.  Output it to the
            -- file, and update the current position for the next loop iteration.
            UTL_FILE.PUT(p_FileHandle, v_BodyChunk);
            v_CurPos := v_CurPos + v_ReturnedLength;
          END IF;
        END LOOP;
        
        -- Close the cursor, since we're finished the processing.
        DBMS_SQL.CLOSE_CURSOR(v_Cursor);
      EXCEPTION
        WHEN OTHERS THEN
          -- For all errors, first close the cursor, then re-raise the exception for
          -- handling in the calling environment.
          DBMS_SQL.CLOSE_CURSOR(v_Cursor);
          RAISE;
        END;
    ELSE
      -- If we get here, we are not outputting a trigger.  So we can just loop through 
      -- the source lines in all_source, and output each one.
      -- Note that the first line will be the type and object name, plus 'IS' or 'AS'.
      OPEN c_ObjCur;
      LOOP
        FETCH c_ObjCur INTO v_TextLine;
        EXIT WHEN c_ObjCur%NOTFOUND;
        
        -- Each line already contains a trailing newline character, so we can just use
        -- UTL_FILE.PUT rather than UTL_FILE.PUT_LINE.
        UTL_FILE.PUT(p_FileHandle, v_TextLine);
      END LOOP;
      CLOSE c_ObjCur;
    END IF;

    -- Output the final '/'.
    UTL_FILE.PUT_LINE(p_FileHandle, '/');
  END OutputObj;  
  
  PROCEDURE OneObj(p_Schema IN VARCHAR2,
                   p_ObjName IN VARCHAR2,
                   p_ObjType IN VARCHAR2 DEFAULT NULL,
                   p_BothTypes IN BOOLEAN DEFAULT TRUE,
                   p_FileDir IN VARCHAR2,
                   p_FileName IN VARCHAR2,
                   p_Mode IN VARCHAR2) IS
    v_FileHandle UTL_FILE.FILE_TYPE;
    v_ObjType all_objects.object_type%TYPE;
  BEGIN
    -- Validate the input parameters.
    IF p_BothTypes AND (p_ObjType != 'PACKAGE') THEN
      RAISE_APPLICATION_ERROR(-20000,
        'Export.OneObj: BothTypes set but type != PACKAGE');
    ELSIF p_ObjType IS NOT NULL AND p_ObjType NOT IN
          ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER') THEN
      RAISE_APPLICATION_ERROR(-20001,
        'Export.OneObj: Illegal value ' || p_ObjType || ' for object type');
    ELSIF p_FileDir IS NULL OR p_FileName IS NULL or p_Mode IS NULL THEN
      RAISE_APPLICATION_ERROR(-20002,
        'Export.OneObj: Directory, Filename and Mode must be non-NULL');
    ELSIF p_Mode NOT IN ('A', 'a', 'W', 'w') THEN
      RAISE_APPLICATION_ERROR(-20003,
        'Export.OneObj: Mode ' || p_Mode || ' not ''A'' or ''W''');
    END IF;
    
    -- Determine the correct object type, and insure that the object
    -- exists.
    BEGIN
      IF p_ObjType IS NULL THEN
        -- No object type specified - check for existence whithout 
        -- specifying the type.
        SELECT object_type
          INTO v_ObjType
          FROM all_objects
          WHERE owner = UPPER(p_Schema)
          AND object_name = UPPER(p_Objname)
          AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE',
                              'PACKAGE BODY', 'TRIGGER');
      ELSIF p_BothTypes THEN
        -- BothTypes specified - check for existence of package header
        -- first.
        SELECT object_type
          INTO v_ObjType
          FROM all_objects
          WHERE owner = UPPER(p_Schema)
          AND object_name = UPPER(p_Objname)
          AND object_type = 'PACKAGE';
        
        -- Now check for the package body.
        BEGIN
          SELECT object_type
            INTO v_ObjType
            FROM all_objects
            WHERE owner = UPPER(p_Schema)
            AND object_name = UPPER(p_Objname)
            AND object_type = 'PACKAGE BODY';
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20006,
              'Export.ObjObj: BothTypes set but package body ' ||
              p_Schema || '.' || p_Objname || ' not found');
        END;
      ELSE
        -- Object type specified, Bothtypes not specified - check for
        -- existence using object type.
        SELECT object_type
          INTO v_ObjType
          FROM all_objects
          WHERE owner = UPPER(p_Schema)
          AND object_name = UPPER(p_Objname)
          AND object_type = p_ObjType;
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20004,
          'Export.OneObj: Object ' || p_Schema || '.' || p_Objname || 
          ' not found');
      WHEN TOO_MANY_ROWS THEN
        RAISE_APPLICATION_ERROR(-20005,
          'Export.OneObj: More than one match for ' || p_Schema || '.' ||
          p_Objname);
    END;

    -- If we get to this point, we know that the object exists, so we can
    -- open the file and write to it.  If the mode is 'A', then write 2
    -- blank lines first.
    v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, p_Mode);
    IF p_Mode IN ('A', 'a') THEN
      UTL_FILE.NEW_LINE(v_FileHandle, 2);
    END IF;
    
    -- Output the object.  
    IF p_ObjType = 'PACKAGE' AND p_BothTypes THEN
      OutputObj(v_FileHandle, p_Schema, p_ObjName, 'PACKAGE');
      UTL_FILE.NEW_LINE(v_FileHandle, 2);
      OutputObj(v_FileHandle, p_Schema, p_ObjName, 'PACKAGE BODY');
    ELSE
      OutputObj(v_FileHandle, p_Schema, p_ObjName, v_ObjType);
    END IF;

    -- Close the output file.    
    UTL_FILE.FCLOSE(v_FileHandle);
  EXCEPTION
    -- Handle the UTL_FILE exceptions meaningfully, and make sure
    -- that the file is properly closed.
    WHEN UTL_FILE.INVALID_PATH THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.OneObj: Invalid Path');
    WHEN UTL_FILE.INVALID_OPERATION THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.OneObj: Invalid Operation');
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.OneObj: Invalid File Handle');
    WHEN UTL_FILE.WRITE_ERROR THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.OneObj: Write Error');
    WHEN UTL_FILE.INTERNAL_ERROR THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.OneObj: Internal Error');
    WHEN OTHERS THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE;
  END OneObj;
  
  PROCEDURE AllObjs(p_Schema IN VARCHAR2,
                    p_ObjType IN VARCHAR2 DEFAULT NULL,
                    p_FileDir IN VARCHAR2,
                    p_FileName IN VARCHAR2,
                    p_Mode IN VARCHAR2) IS
    v_FileHandle UTL_FILE.FILE_TYPE;
    v_ObjName all_objects.object_name%TYPE;
    v_ObjType all_objects.object_type%TYPE;
    v_ObjectFound BOOLEAN := FALSE;
    
    -- Use a cursor variable for the query, since it can take two forms.
    TYPE t_AllObjs IS REF CURSOR;
    c_AllObjsCur t_AllObjs;
    
  BEGIN
    -- Validate the input parameters.
    IF p_ObjType IS NOT NULL AND p_ObjType NOT IN
          ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER') THEN
      RAISE_APPLICATION_ERROR(-20001,
        'Export.AllObjs: Illegal value ' || p_ObjType || ' for object type');
    ELSIF p_FileDir IS NULL OR p_FileName IS NULL or p_Mode IS NULL THEN
      RAISE_APPLICATION_ERROR(-20002,
        'Export.AllObjs: Directory, Filename and Mode must be non-NULL');
    ELSIF p_Mode NOT IN ('A', 'a', 'W', 'w') THEN
      RAISE_APPLICATION_ERROR(-20003,
        'Export.AllObjs: Mode ' || p_Mode || ' not ''A'' or ''W''');
    END IF;

    -- If the object type isn't specified, then open the cursor for
    -- querying all objects owned by p_Schema.  If it is, then open the
    -- cursor for querying only that type of object.
    IF p_ObjType IS NULL THEN
      OPEN c_AllObjsCur FOR
        SELECT object_name, object_type
          FROM all_objects
          WHERE owner = UPPER(p_Schema)
          AND object_type in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION',
                              'TRIGGER');
    ELSE
      OPEN c_AllObjsCur FOR
        SELECT object_name, object_type
          FROM all_objects
          WHERE owner = UPPER(p_Schema)
          AND object_type = p_ObjType;
    END IF;
    
    -- Loop over all objects matching the selection criteria, and output
    -- each one, with 2 blank lines in between.
    LOOP
      FETCH c_AllObjsCur INTO v_ObjName, v_ObjType;
      EXIT WHEN c_AllObjsCur%NOTFOUND;

      IF NOT v_ObjectFound THEN
        -- We've found at least one object matching the input parameters.
        -- Open the file, and if the mode is 'A' write 2
        -- blank lines first.
        v_ObjectFound := TRUE;
        v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, p_Mode);
        IF p_Mode IN ('A', 'a') THEN
          UTL_FILE.NEW_LINE(v_FileHandle, 2);
        END IF;
      END IF;
      
      OutputObj(v_FileHandle, p_Schema, v_ObjName, v_ObjType);
      UTL_FILE.NEW_LINE(v_FileHandle, 2);
    END LOOP;
    
    -- Check for a found object, and close the cursor and file handle.
    CLOSE c_AllObjsCur;
    IF NOT v_ObjectFound THEN
      RAISE_APPLICATION_ERROR(-20004,
          'Export.AllObjs: No objects found');
    END IF;
    UTL_FILE.FCLOSE(v_FileHandle);
  EXCEPTION
    -- Handle the UTL_FILE exceptions meaningfully, and make sure
    -- that the file is properly closed.
    WHEN UTL_FILE.INVALID_PATH THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.AllObjs: Invalid Path');
    WHEN UTL_FILE.INVALID_OPERATION THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.AllObjs: Invalid Operation');
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.AllObjs: Invalid File Handle');
    WHEN UTL_FILE.WRITE_ERROR THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE_APPLICATION_ERROR(-20010,
                              'Export.AllObjs: Write Error');
    WHEN OTHERS THEN
      UTL_FILE.FCLOSE(v_FileHandle);
      RAISE;
  END AllObjs;
END Export;
/