Oracle Stored Procedure that copies and compiles database objects between schemas



We have a single ORACLE server that contains production as well as development environments, each one uses two schemas - SCHEMA_1 / SCHEMA_2 and SCHEMA_1_DEV / SCHEMA_2_DEV respectively. First we design everything in _DEV environment and then copy all database structure to production. The problem is that the development stored procedures point to tables and other procedures in schema ending with "_DEV". However, in production all schema calls should have no "_DEV".

The following procedure copies (and compiles) any object from development environment to production after removing the "_DEV" call from everywhere. This way we save time and avoid any human error on doing such replacements by hand.

Note: We are using DBMS_SQL so that package or procedure of any size can be compiled. EXECUTE IMMEDIATE cannot run queries whose length is greater than 32 KB
  1. Read the script needed to generate the object from USER_SOURCE table.
  2. Execute each line of this script with DBMS_SQL
    3. instead of EXECUTE IMMEDIATE which cannot run queries whose length is greater than 32 KB
Scroll to the bottom to copy the source code from a textarea.

Call to procedure:
EXEC MIGRATE_OBJECT_TO_PRODUCTION ('NEW_SCHEMA', 'PKG_TO_COPY', 'PACKAGE')

Procedure Source Code:
 CREATE OR REPLACE PROCEDURE MIGRATE_OBJECT_TO_PRODUCTION (
   IN_SCHEMA_TO     IN   VARCHAR2,
   IN_OBJECT_NAME   IN   VARCHAR2,
   IN_OBJECT_TYPE   IN   VARCHAR2
)
IS
   -- LOCAL STORED PROCEDURE
   PROCEDURE PRC_CREATE_OBJECT (
      IN_SCHEMA_TO     IN   VARCHAR2,
      IN_OBJECT_NAME   IN   VARCHAR2,
      IN_OBJECT_TYPE   IN   VARCHAR2
   )
   IS
      L_SQL_TABLE        DBMS_SQL.VARCHAR2A;
      -- COLLECTION OF VARCHAR2(32767) INDEX BY BINARY_INTEGER
      L_CURRENT_ROW      BINARY_INTEGER     := 0;
      L_CUR              PLS_INTEGER;
      L_ROWS_PROCESSED   PLS_INTEGER;

      CURSOR CRS_OBJECT_SCRIPT (
         IN_OBJECT_NAME   IN   VARCHAR2,
         IN_OBJECT_TYPE   IN   VARCHAR2
      )
      IS
         -- CURSOR RETRIEVES ALL LINES OF CODE TO CREATE THE OBJECT
         -- REPLACE ALL CALLS TO SCHEMA NAME ENDING WITH _DEV
         SELECT   REPLACE (UPPER (TEXT), '_DEV.', '.') AS TEXT, LINE
             FROM USER_SOURCE
            WHERE UPPER (NAME) = UPPER (IN_OBJECT_NAME)
              AND UPPER (TYPE) = UPPER (IN_OBJECT_TYPE)
         ORDER BY LINE;
   BEGIN
      FOR CODE_LINE IN CRS_OBJECT_SCRIPT (IN_OBJECT_NAME, IN_OBJECT_TYPE)
      LOOP
         IF CODE_LINE.LINE = 1
         THEN
            -- ADD NEW SCHEMA NAME AND CREATE OR REPLACE COMMAND
            CODE_LINE.TEXT :=
                  ' CREATE OR REPLACE '
               || REPLACE (CODE_LINE.TEXT,
                           IN_OBJECT_NAME,
                           IN_SCHEMA_TO || '.' || IN_OBJECT_NAME
                          );
         END IF;

         L_CURRENT_ROW := NVL (L_SQL_TABLE.LAST, 0) + 1;
         L_SQL_TABLE (L_CURRENT_ROW) := CODE_LINE.TEXT;
      END LOOP;

      IF L_CURRENT_ROW = 0
      THEN
         RAISE NO_DATA_FOUND;
      END IF;

      -- PARSE THE PL/SQL AND EXECUTE IT
      L_CUR := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE (L_CUR,
                      L_SQL_TABLE,
                      L_SQL_TABLE.FIRST,
                      L_SQL_TABLE.LAST,
                      FALSE,
                      DBMS_SQL.NATIVE
                     );
      L_ROWS_PROCESSED := DBMS_SQL.EXECUTE (L_CUR);
      DBMS_SQL.CLOSE_CURSOR (L_CUR);
      L_SQL_TABLE.DELETE (0, L_CURRENT_ROW);
      -- CLEAR THE COLLECTION OF QUERY LINES
      DBMS_OUTPUT.PUT_LINE (   IN_OBJECT_TYPE
                            || ' '
                            || IN_OBJECT_NAME
                            || ' successfully created.'
                           );
   EXCEPTION                                       -- EXCEPTION HANDLERS BEGIN
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.PUT_LINE (   'There is no '
                               || IN_OBJECT_TYPE
                               || ' '
                               || IN_OBJECT_NAME
                               || '.'
                              );
      WHEN OTHERS
      THEN                                         -- HANDLES ALL OTHER ERRORS
         DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);
         ROLLBACK;
   END PRC_CREATE_OBJECT;
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);

   IF IN_OBJECT_TYPE = 'PACKAGE'
   THEN
      -- PACKAGE NEEDS PACKAGE AND PACKAGE BODY DECLARATIONS
      PRC_CREATE_OBJECT (IN_SCHEMA_TO, IN_OBJECT_NAME, 'PACKAGE');
      PRC_CREATE_OBJECT (IN_SCHEMA_TO, IN_OBJECT_NAME, 'PACKAGE BODY');
   ELSE
      -- FOR ALL OTHER OBJECTS
      PRC_CREATE_OBJECT (IN_SCHEMA_TO, IN_OBJECT_NAME, IN_OBJECT_TYPE);
   END IF;

   COMMIT;
END MIGRATE_OBJECT_TO_PRODUCTION;



To copy the source code, click inside the textbox => Ctrl-A => Ctrl-C
^ Top ^
List of all ORACLE Codes