Title: Automated Data-Copying Utility for complex RI-Driven Environments

Author: Venkat S. Devraj, a database consultant for the Oracle Solution Center, Raymond James
Consulting in Denver, Colorado.

Have you ever wanted to copy just a few records in one table from production to development/test, only to find that the table
that you want to copy is the child table of two other tables, both of which are children of yet another table!

At many sites it's necessary to replicate sample production data onto development/test environments to resolve
post-deployment application bugs. For example, if a form's calculations seem to be in error, it is better to copy sample data
from the base-table to development, rather than trying out various experiments in the production environment. In environments
where complex business-rules are physically enforced within the database via complex networks of referential integrity
constraints, it becomes highly cumbersome, if not virtually impossible, to navigate through all the parent-child relationships. You
end up having to manually find the parents and populate them first or replicating the entire schema (via import/export, etc.). All
these manual methods take a lot of time.

After running into this issue at multiple client sites, I looked around for that "light-weight dream-utility," which would ask me
which table I wanted to copy, the number of records to be copied, the source and target databases and would automatically
resolve all referential integrity issues by populating all parents first and then the children--without any intervention from me
(provided sufficient space is available, of course!). Not being very successful in finding any that pleased both the DBA and the
project budget, I finally wrote my own.

The following is the code for a stored procedure called copydata. It needs to be created in the source database (production).
In addition, a db link has to be created in the source database to point to the proper schema in the target database
(development). The stored procedure expects the following three inputs:

   1.table name to be copied (empl);
   2.# of records (200, 500, etc.);
   3.name of db link to be used (mentioned above).

The procedure makes the following assumptions :

   1.The source and target databases have the exact same data-structures (i.e. in terms of table-names, column-names,
     column-sizes, primary keys, foreign keys, etc.).
   2.The user has the privilege to EXECUTE sys.dbms_sql and has a synonym called dbms_sql pointing to sys.dbms_sql (if
     he/she doesn't own it).
   3.The target database has sufficient space for the copy-operation; i.e., it does not make any explicit checks to ensure
     availability of space.
   4.SERVEROUTPUT is ON (if being run from SQL*Plus).

Since it is stored procedure, it offers a lot of flexibility in usage. I generally call it in SQL*Plus with the following command :

SQL> exec copydata('EMP', 200, 'dev_dblink');

If it is not feasible to create either the stored procedure or the db link in production, feel free to make necessary changes to the
script so that it can be created and run on the development (target database), instead. I have used this script on a 7.3 database,
using PL/SQL v2.3. Please use this utility at your own discretion.


/*
** Author       : Venkat S. Devraj
** Organization : Oracle Solution Center, Raymond James Consulting
** Date written : 1998
**
** Functionality: To copy specific data from one environment to another
** Assumptions  : 1) Data structures (tables, columns, referential-integrity,
** etc.) is the same in both environments
**                2) User has a DB Link created in source environment, pointing to
**                   target environment (with the right username/password)
**                3) User has EXECUTE privilege on SYS.dbms_sql package
**                4) SERVEROUTPUT has been set on (if this is being run from
**                   SQL*Plus)
**
** Conventions  : a) All parameter-names start with a "p" (example : pStmt)
**                b) All (non-parameter) integer/number variable-names start a "n"
**                   (example : nCtr)
**                c) All (non-parameter) varchar2/character variable-names start
**                   with a "c" (example : cStmt)
**
** Usage        : From the SQL*Plus prompt, type :
**                SQL> exec copydata('EMP', 500, 'dev_dblink_name');
**
*/
CREATE OR REPLACE PROCEDURE copydata(pTabName IN VARCHAR2
                                    ,pNumRecs IN NUMBER
                                    ,pTrgtDBLink IN VARCHAR2
                                    ) AS

  /* cursor to find parent tables */
  CURSOR c_find_parents(pTable IN VARCHAR2) IS
    SELECT table_name FROM user_constraints
     WHERE constraint_name IN
       (SELECT r_constraint_name FROM user_constraints
         WHERE table_name = pTable
           AND constraint_type = 'R'
           AND status = 'ENABLED');

  cTabName user_constraints.table_name%TYPE;
  cTempTabName user_constraints.table_name%TYPE;
  cStmt VARCHAR2(500);

  nCtr PLS_INTEGER;
  nLevel PLS_INTEGER;
  nNdx   PLS_INTEGER;
  nLoopCtr PLS_INTEGER;

  /* record to hold table_name and parent-level
  -- i.e. child-table would have level = 1, it's parent would
  -- have level = 2; grand-parent would have level = 3 and so on
  */
  TYPE rec_level IS RECORD (rTable user_constraints.table_name%TYPE,
                            rLevel PLS_INTEGER);

  TYPE tbl_level IS TABLE OF rec_level
    INDEX BY BINARY_INTEGER;

  tbl_level_tab tbl_level;

  /* procedure to run the actual INSERT statement */
  PROCEDURE run_statement(pStmt IN VARCHAR2) IS
    nCrsrHandle INTEGER;
    nCtr PLS_INTEGER;
  BEGIN
    nCrsrHandle := dbms_sql.open_cursor;
    dbms_sql.parse(nCrsrHandle, pStmt, dbms_sql.v7);
    nCtr := dbms_sql.execute(nCrsrHandle);

    COMMIT;
  EXCEPTION
    -- if duplicate records, then ignore error
    WHEN DUP_VAL_ON_INDEX THEN NULL;

    -- any other error, break with error-message
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20101, SQLCODE || ' - ' || SQLERRM);
  END;

BEGIN
  nLevel := 0;
  nNdx := 1;
  nLoopCtr := 1;

  tbl_level_tab(nNdx).rTable := UPPER(pTabName);
  tbl_level_tab(nNdx).rLevel := nLevel;

  LOOP
    -- check whether level needs to be incremented
    IF tbl_level_tab(nLoopCtr).rLevel = nLevel THEN
       nLevel := nLevel + 1;
    END IF;

    cTabName := tbl_level_tab(nLoopCtr).rTable;
    nLoopCtr := nLoopCtr + 1;

    OPEN c_find_parents(cTabName);
    LOOP
      FETCH c_find_parents INTO cTempTabName;
      EXIT WHEN c_find_parents%NOTFOUND;

      nNdx := nNdx + 1;

      tbl_level_tab(nNdx).rTable := cTempTabName;
      tbl_level_tab(nNdx).rLevel := nLevel;

    END LOOP;
    CLOSE c_find_parents;
    EXIT WHEN nLoopCtr > nNdx;

  END LOOP;

  /* Populate parent tables, if any, in descending order */
  IF nNdx > 1 THEN
    FOR nCtr in REVERSE 2..nNdx LOOP
      cStmt := 'INSERT INTO ' || tbl_level_tab(nCtr).rTable || '@' || pTrgtDBLink ||
               ' SELECT * FROM ' || tbl_level_tab(nCtr).rTable;

      run_statement(cStmt);
    END LOOP;
  END IF;

  /* Now, populate main table with desired number of rows */
  cStmt := 'INSERT INTO ' || tbl_level_tab(1).rTable || '@' || pTrgtDBLink ||
           ' SELECT * FROM ' || tbl_level_tab(1).rTable ||
           '  WHERE ROWNUM <= ' || TO_CHAR(pNumRecs);
  run_statement(cStmt);

  /* write results back to user */
  IF nNdx > 1 THEN
     dbms_output.put_line('The following tables have been successfully copied : ');
     FOR nCtr IN REVERSE 2..nNdx LOOP
       dbms_output.put_line(tbl_level_tab(nCtr).rTable);
     END LOOP;

     dbms_output.put_line(tbl_level_tab(1).rTable || ' - ' || TO_CHAR(pNumRecs) ||
                          ' records (or less)');
  ELSE
     dbms_output.put_line(TO_CHAR(pNumRecs) || ' (or less) records from table ' ||
                          tbl_level_tab(1).rTable ||
                          ' has been successfully copied');
  END IF;

END;
/
/* eof() */