Generate PL/SQL code using BULK COLLECT FORALL statements

2009-05-18T17:07:00.001-07:00

Combination of BULK COLLECT and FORALL statements is used primarily to improve the performance of ETL processes that deal with large volumes of data and also to avoid ORA-01555 Snapshot
Too Old error when periodic commits are necessary during updating/inserting the rows in the same table that is used in the select statement fetching the source data. The performance is greatly enhanced by the fact that the switches between SQL to PL/SQL engines are not performed at each raw, but only once for the entire DML statement for the number of rows fetched specified by the LIMIT clause of the BULK COLLECT statement. Further performance enhancements can be achieved by additional utilization of multithreading and partitioning of target table.

Coding the PL/SQL procedure that uses BULK COLLECT FORALL statements can be rather time consuming and error prone effort when you need to create the code accommodating a table with large number of columns and you need to create a separate collection for each column of such large table.

This script offers a quick solution to automatically generate the PL/SQL code (template) that uses Data Dictionary to create all necessary types, collections as well as basic code to select, fetch, and then insert the fetched data into a target table using FORALL statement.

When the script is executed, it prompts you to provide the TABLE_NAME. This version of the script uses USER_TABLES data dictionary table to retrieve the column information. It can be easily expended to use DBA_TABLES as well; however you would then need to provide the table owner parameter value.

This is how the script works:

It uses the table name provided by user to generate:

All types and collections (for each column in the table) in the Declaration section of PL/SQL block.
Definition of CURSOR to select all data from specified table.
BULK COLLECT loop iteration to fetch 20000 rows at the time.
Dummy Transform section.
FORALL statement to insert transformed data into a target table (a pseudo table name).

The script creates a template using a pseudo name for the target table by taking first 25 characters of the source table name and then attaching a '_bkup' suffix to it. It also assumes that you need to insert the data into empty table; therefore there is a TRUNCATE TABLE statement in the beginning of the execution logic.

When used for ETL processing, the "T" (transform) portion of the code logic must be added to the code in-between FETCH and FORALL statements.

Just run this script in SQL*Plus and it will create a spool file with your table_name appended with '_proc' suffix containing the PL/SQL Procedure code.

define tab=&table_name

SET lines 200

SET feed OFF

SET ver OFF

SET pages 0

COLUMN last_col new_value last_column

SELECT MAX(column_id) last_col

  FROM user_tab_columns

 WHERE table_name=UPPER('&tab');

spool &tab._proc.SQL

SELECT 'CREATE OR REPLACE PROCEDURE dmt_load'||SUBSTR(LOWER('&tab'),1,22)||CHR(10)||

        'IS'||CHR(10)

  FROM user_tables

 WHERE table_name=UPPER('&tab');

SELECT '   TYPE '||INITCAP(SUBSTR(column_name,1,23))||'TabType is TABLE OF '||LOWER(table_name)||'.'||LOWER(column_name)||'%TYPE;'||CHR(10)||

       '   '||INITCAP(SUBSTR(column_name,1,23))||'Tab '||INITCAP(SUBSTR(column_name,1,23))||'TabType;'

  FROM user_tab_columns

 WHERE table_name=UPPER('&tab')

 ORDER BY

       column_id;

SET recsep OFF

SELECT DECODE(column_id,1,'   CURSOR '||SUBSTR(LOWER(table_name),1,28)||'_c'||CHR(10)||

       '   IS'||CHR(10)||

       '      SELECT '||LOWER(column_name),'            ,'||LOWER(column_name))

  FROM user_tab_columns

 WHERE table_name=UPPER('&tab')

 ORDER BY

       column_id;

SELECT '        FROM '||LOWER(table_name)||';'

  FROM user_tables

 WHERE table_name=UPPER('&tab');

SET recsep wr

SELECT CHR(10)||

       '   BEGIN'||CHR(10)||CHR(10)||

       '      EXECUTE IMMEDIATE '||''''||'TRUNCATE TABLE '||SUBSTR(LOWER('&tab'),1,25)||'_bkup'||''';'||CHR(10)||CHR(10)||

       '      OPEN '||SUBSTR(LOWER('&tab'),1,28)||'_c;'||CHR(10)||

       CHR(10)||

       '      LOOP'

  FROM dual;

SET recsep OFF

SELECT DECODE(column_id,1,'         FETCH '||SUBSTR(LOWER('&tab'),1,28)||'_c'||' BULK COLLECT INTO '||INITCAP(SUBSTR(column_name,1,23))||'Tab '

                      ,'                                   ,'||INITCAP(SUBSTR(column_name,1,23))||'Tab ')

  FROM user_tab_columns

 WHERE table_name=UPPER('&tab')

 ORDER BY

       column_id;

SELECT '         LIMIT 20000;'

  FROM dual;

SELECT CHR(10)||

       '         /*'||CHR(10)||

       '         ||'||CHR(10)||

       '         || Insert your data transformation logic here'||CHR(10)||

       '         ||'||CHR(10)||

       '         */'

  FROM dual;

SELECT CHR(10)||

       '         FORALL i IN 1 .. '||INITCAP(SUBSTR(column_name,1,23))||'Tab.COUNT'||CHR(10)||

       '            INSERT INTO '||LOWER(table_name)||'_bkup'

  FROM user_tab_columns

 WHERE table_name=UPPER('&tab')

   AND ROWNUM < 2;

SELECT DECODE(column_id,1,'                       ('||LOWER(column_name)

                      ,'                       ,'||LOWER(column_name))

  FROM user_tab_columns

 WHERE table_name=UPPER('&tab')

 ORDER BY

       column_id;

SELECT '                       )'

  FROM dual;

SELECT DECODE(column_id,1,'                VALUES ('||INITCAP(SUBSTR(column_name,1,23))||'Tab '||'(i)'

                      ,'                       ,'||INITCAP(SUBSTR(column_name,1,23))||'Tab '||'(i)')

  FROM user_tab_columns

 WHERE table_name=UPPER('&tab')

 ORDER BY

       column_id;

SELECT '                       );'

  FROM dual;

SET recsep wr

SELECT '         COMMIT;'||

       CHR(10)||CHR(10)||

       '         IF '||SUBSTR(LOWER('&tab'),1,28)||'_c'||'%NOTFOUND THEN'||CHR(10)||

       '            EXIT;'||CHR(10)||

       '         END IF;'||CHR(10)||CHR(10)||

       '      END LOOP;'||CHR(10)||CHR(10)||

       '   END dmt_load'||SUBSTR(LOWER('&tab'),1,22)||';'||CHR(10)

  FROM user_tables

 WHERE table_name=UPPER('&tab');

spool OFF

Oracle Dvelopment 11i

Generate PL/SQL code using BULK COLLECT FORALL statements