Generating PL/SQL Scripts to Reproduce Table Data

When developing test scripts, it is sometimes convenient to have PL/SQL collections matching tables of interest so data can be repeatedly generated. For example, when developing unit tests for PL/SQL procedures, a collection can be used to load data into a table, mangle the data during testing, and then regenerated. Once a table is created and populated with the basic data for scripts, the following procedure can be used to easily generate a PL/SQL script to reproduce the data. The function parameters are:

p_table_owner = Schema of table of interest
p_table_name = Name of the table of interest
p_tbl_abbrev = A "root" used in naming the collection

CREATE OR REPLACE
PROCEDURE generate_array_insert( p_table_owner VARCHAR2, p_table_name VARCHAR2, p_tbl_abbrev VARCHAR2 )
IS
  TYPE column_tbl IS TABLE OF VARCHAR2( 200 );
  TYPE rowid_tbl IS TABLE OF ROWID;
 
  m_columns    COLUMN_TBL;
  m_datatypes  COLUMN_TBL;
  m_rowids     ROWID_TBL;
 
  m_nls_date_fmt   VARCHAR2( 200 );
  m_value          VARCHAR2( 32767 );
  m_output_line    VARCHAR2( 32767 );
  m_rowtype_tbl_tp VARCHAR2( 200 ) DEFAULT p_tbl_abbrev || '_array_t';
  m_rowtype_tbl    VARCHAR2( 200 ) DEFAULT 'g_' || p_tbl_abbrev || '_array';
  m_rowtype_rec    VARCHAR2( 200 ) DEFAULT 'm_temp_' || p_tbl_abbrev;
 
BEGIN
 
  DBMS_OUTPUT.PUT_LINE( 'DECLARE' );
  DBMS_OUTPUT.PUT_LINE( 'TYPE ' || m_rowtype_tbl_tp || ' IS TABLE OF ' || p_table_owner || '.' || p_table_name || '%ROWTYPE' || ';' );
  DBMS_OUTPUT.PUT_LINE( m_rowtype_tbl || ' ' || UPPER( m_rowtype_tbl_tp ) || ';' );
  DBMS_OUTPUT.PUT_LINE( m_rowtype_rec || ' ' || p_table_owner || '.' || p_table_name || '%ROWTYPE;' );
  DBMS_OUTPUT.PUT_LINE( 'BEGIN' );
  DBMS_OUTPUT.PUT_LINE( m_rowtype_tbl || ' := ' || m_rowtype_tbl_tp || '();' );

  SELECT value
  INTO m_nls_date_fmt
  FROM nls_session_parameters
  WHERE parameter = 'NLS_DATE_FORMAT';

  SELECT LOWER( column_name ), UPPER( data_type )
  BULK COLLECT INTO m_columns, m_datatypes
  FROM dba_tab_cols
  WHERE owner = p_table_owner
    AND table_name = p_table_name
    AND virtual_column = 'NO'
  ORDER BY column_id;
 
  EXECUTE IMMEDIATE 'SELECT rowid FROM ' || p_table_owner || '.' || p_table_name
  BULK COLLECT INTO m_rowids;
 
  FOR row_idx IN m_rowids.FIRST..m_rowids.LAST
  LOOP
   
    FOR col_idx IN m_columns.FIRST..m_columns.LAST
    LOOP
 
      EXECUTE IMMEDIATE
        'SELECT ' || m_columns( col_idx ) || ' ' ||
        'FROM ' || p_table_owner || '.' || p_table_name || ' ' ||
        'WHERE rowid = ''' || m_rowids( row_idx ) || ''''
      INTO m_value;
 
      m_output_line := m_rowtype_rec || '.' || m_columns( col_idx ) || '  :=  ' ||
        CASE
          WHEN m_datatypes( col_idx ) IN ( 'VARCHAR2', 'CHAR', 'CLOB', 'ROWID' ) THEN
            NVL( '''' || m_value || '''', 'NULL' )
          WHEN m_datatypes( col_idx ) IN ( 'DATE' ) THEN
            'TO_DATE( ''' || m_value || ''', ''' || m_nls_date_fmt || ''' )'
          ELSE
            NVL( m_value, 'NULL' )
        END || ';';
 
      DBMS_OUTPUT.PUT_LINE( m_output_line );

    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE( m_rowtype_tbl || '.EXTEND;' );
    DBMS_OUTPUT.PUT_LINE( m_rowtype_tbl || '(' || m_rowtype_tbl || '.LAST) := ' || m_rowtype_rec || ';' );   
 
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE( 'IF ' || m_rowtype_tbl || ' IS NOT EMPTY THEN' );
  DBMS_OUTPUT.PUT_LINE( 'FORALL i IN 1..' || m_rowtype_tbl || '.COUNT' );
  DBMS_OUTPUT.PUT_LINE( 'INSERT INTO ' || p_table_owner || '.' || p_table_name || ' VALUES ' || m_rowtype_tbl || '(i);' );
  DBMS_OUTPUT.PUT_LINE( 'END IF;' );
  DBMS_OUTPUT.PUT_LINE( 'END;' );
  DBMS_OUTPUT.PUT_LINE( '/' );
 
END;
/

This procedure will likely need some customization based on the datatypes and formatting of your data. Here is an example of output based on a test table:

SQL> desc testuser.test_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 COL1                                               VARCHAR2(50)
 COL2                                               DATE
 COL3                                               NUMBER


SQL> SET SERVEROUTPUT ON
SQL> select * from testuser.test_tab;
COL1                                               COL2            COL3
-------------------------------------------------- --------- ----------
First Column                                       04-FEB-13         12
Second Column                                      21-DEC-12         45


SQL> EXEC generate_array_insert( 'TESTUSER', 'TEST_TAB', 'source' );
DECLARE
TYPE source_array_t IS TABLE OF TESTUSER.TEST_TAB%ROWTYPE;
g_source_array SOURCE_ARRAY_T;
m_temp_source TESTUSER.TEST_TAB%ROWTYPE;
BEGIN
g_source_array := source_array_t();
m_temp_source.col1  :=  'First Column';
m_temp_source.col2  :=  TO_DATE( '04-FEB-2013', 'DD-MON-YYYY' );
m_temp_source.col3  :=  12;
g_source_array.EXTEND;
g_source_array(g_source_array.LAST) := m_temp_source;
m_temp_source.col1  :=  'Second Column';
m_temp_source.col2  :=  TO_DATE( '21-DEC-2012', 'DD-MON-YYYY' );
m_temp_source.col3  :=  45;
g_source_array.EXTEND;
g_source_array(g_source_array.LAST) := m_temp_source;
IF g_source_array IS NOT EMPTY THEN
FORALL i IN 1..g_source_array.COUNT
INSERT INTO TESTUSER.TEST_TAB VALUES g_source_array(i);
END IF;
END;
/

PL/SQL procedure successfully completed.

Here is the generated script with a little manual clean-up so it is more readable:

DECLARE
  TYPE source_array_t IS TABLE OF TESTUSER.TEST_TAB%ROWTYPE;

  g_source_array SOURCE_ARRAY_T;
  m_temp_source TESTUSER.TEST_TAB%ROWTYPE;
BEGIN
  g_source_array := source_array_t();

  -- Build the collection
  m_temp_source.col1  :=  'First Column';
  m_temp_source.col2  :=  TO_DATE( '04-FEB-2013', 'DD-MON-YYYY' );
  m_temp_source.col3  :=  12;
  g_source_array.EXTEND;
  g_source_array(g_source_array.LAST) := m_temp_source;

  m_temp_source.col1  :=  'Second Column';
  m_temp_source.col2  :=  TO_DATE( '21-DEC-2012', 'DD-MON-YYYY' );
  m_temp_source.col3  :=  45;
  g_source_array.EXTEND;
  g_source_array(g_source_array.LAST) := m_temp_source;

  -- Perform "tweaks" here
  -- Perform the inserts
  IF g_source_array IS NOT EMPTY THEN
    FORALL i IN 1..g_source_array.COUNT
      INSERT INTO TESTUSER.TEST_TAB VALUES g_source_array(i);
  END IF;

END;
/

This script generates a stand-alone anonymous block, but I usually separate the collection-building code from the inserts and place them in two separate functions. That way, for each test, the collection data can be adjusted before inserting.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s