Dump user table data in INSERT format in Oracle

DROP PACKAGE DATA2INSERT;

--------------------------------------------------------------------------------
CREATE PACKAGE DATA2INSERT IS

/*
  Dump user table data in INSERT format.

  Any Oracle database version 9 or above should be fine.

  Usage:
    select * from table(DATA2INSERT.TAB2INSERT(...));
    select * from table(DATA2INSERT.TABLIST2INSERT(...));

    This will be most likely found embedded between SPOOL commands inside a SQL
    script for SQL*Plus.
    Or perhaps used in a Korn shell script containing something like the following:
      #!/usr/bin/ksh
      $ORACLE_HOME/bin/sqlplus -s << SQLEND
      connect user/password@dbname
      set pagesize 0 linesize 9999 tab off feedback off
      select * from table(DATA2INSERT.TAB2INSERT('TABLENAME'[,parameters ...]));
      SQLEND
    Then you can redirect the output to your taste:
      myscript.ksh > mydumpfile.sql

  This package should not be used in a context where the input values (function
  parameters) cannot be trusted. In other words: beware of SQL injection!

  Copyright (C) Marco Coletti, marco.coletti<replace this with the snail>it.telecomitalia.it

  This program is free software: you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation, either version 3 of the License, or
  (at your option) any later version.

  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

  TYPE STRTABLE IS TABLE OF VARCHAR2(32767);

  debug BOOLEAN := FALSE;

  /*
    Accepts a current user table name.
    Returns a one-column rowset representing data of the given table as a
    sequence of SQL INSERT statements.
    The rows are issued in no particular order.

    The output format is meant to be immediately executable SQL.

    Usage:
      select * from table(DATA2INSERT.TAB2INSERT('TABLENAME'[,parameters ...]));

    Parameters:
      p_table_name:
        The name of any table in the current user schema.
      p_commit_interval:
        N>0 means that a commit statement will be issued every N rows, with a
        final commit.
        Zero means no commit statements, not even a final commit.
        Null means only a final commit, and is the default.
      p_ddl:
        Any value means that a DDL CREATE statement for the given table will be
        issued first.
        Null means no DDL statement and is the default.
      p_where_clause:
        Data filter: this string will be evaluated in the context of the SQL
        statement [select ... from <p_table_name> where <p_where_clause>],
        determining which table rows are to be issued in the output rowset.
        The default is null, that is no where clause, hence no filter.
  */
  FUNCTION TAB2INSERT (
    p_table_name      IN VARCHAR2
   ,p_commit_interval IN PLS_INTEGER DEFAULT NULL
   ,p_ddl             IN VARCHAR2 DEFAULT NULL
   ,p_where_clause    IN VARCHAR2 DEFAULT NULL )
    RETURN STRTABLE pipelined;

  /*
    Accepts a csv list of current user table names.
    Returns a one-column rowset representing the data of the given tables as a
    sequence of SQL INSERT statements.

    The output format is like that of the function TAB2INSERT.
    The order of tables as given is honored.

    Usage:
      select * from table(DATA2INSERT.TABLIST2INSERT('TABLE1,TABLE2,...'[,parameters ...]));

    Parameters:
      p_tablist:
        A comma separated list of names of tables in the current user schema.
      p_commit_interval:
        Like the homonymous parameter of the function TAB2INSERT.
      p_ddl:
        Like the homonymous parameter of the function TAB2INSERT.
  */
  FUNCTION TABLIST2INSERT (
    p_tablist IN VARCHAR2
   ,p_commit_interval IN PLS_INTEGER DEFAULT NULL
   ,p_ddl IN VARCHAR2 DEFAULT NULL )
    RETURN STRTABLE pipelined;

END DATA2INSERT;
/
show errors

--------------------------------------------------------------------------------
CREATE PACKAGE BODY DATA2INSERT IS

  ------------------------------------------------------------------------------
  FUNCTION TAB2INSERT (
    p_table_name IN VARCHAR2
   ,p_commit_interval IN PLS_INTEGER DEFAULT NULL
   ,p_ddl IN VARCHAR2 DEFAULT NULL
   ,p_where_clause IN VARCHAR2 DEFAULT NULL )
    RETURN STRTABLE pipelined
  IS
    column_list LONG;
    insertable_value LONG;
    value_list LONG;
    sql_text LONG;
    separator VARCHAR2(9);
    cur sys_refcursor;
    insert_statement LONG;
    commit_counter PLS_INTEGER := 0;
  BEGIN

    FOR c IN (
      SELECT COLUMN_NAME, DATA_TYPE
      FROM USER_TAB_COLUMNS
      WHERE TABLE_NAME = UPPER(p_table_name)
      ORDER BY DATA_TYPE )
    LOOP

      separator := CASE WHEN column_list IS NULL THEN NULL ELSE ',' END;
      column_list := column_list || separator || c.COLUMN_NAME;

      CASE
        WHEN c.DATA_TYPE IN ('NUMBER','FLOAT','BINARY_DOUBLE') THEN
          insertable_value := 'decode(' || c.COLUMN_NAME || ',null,''null'',' || c.COLUMN_NAME || ')';
        WHEN c.DATA_TYPE IN ('VARCHAR2','CHAR') THEN
          insertable_value := '''''''''||replace(' || c.COLUMN_NAME || ','''''''','''''''''''')||''''''''';
        WHEN c.DATA_TYPE LIKE 'TIMESTAMP%' THEN
          insertable_value := 'decode(' || c.COLUMN_NAME || ',null,''null'',''to_timestamp(''''''||to_char(' || c.COLUMN_NAME || ',''yyyymmddhh24missff'')||'''''',''''yyyymmddhh24missff'''')'')';
        WHEN c.DATA_TYPE = 'DATE' THEN
          insertable_value := 'decode(' || c.COLUMN_NAME || ',null,''null'',''to_date(''''''||to_char(' || c.COLUMN_NAME || ',''yyyymmddhh24miss'')||'''''',''''yyyymmddhh24miss'''')''';
        ELSE
          insertable_value := 'unknown_data_type';
      END CASE;

      separator := CASE WHEN value_list IS NULL THEN NULL ELSE '||'',''||' END;
      value_list := value_list || separator || insertable_value;

    END LOOP;

    -- build a dynamic select statement to extract data in INSERT format from the given user table
    sql_text := 'select ''insert into ' || p_table_name ||
                ' (' || column_list ||
                ') values (''||' || value_list ||
                '||'');'' as INSERT_STATEMENT from ' || p_table_name ||
                CASE WHEN p_where_clause IS NOT NULL THEN ' where ' || p_where_clause END;
    sql_text := REPLACE(sql_text,'''||''',''); -- remove redundant concatenation between VARCHAR2 type literals

    IF p_ddl IS NOT NULL THEN
      pipe ROW (dbms_metadata.get_ddl('TABLE',UPPER(p_table_name),USER) || ';');
    END IF;

    IF debug THEN
      pipe ROW (sql_text);
    ELSE
      OPEN cur FOR sql_text;
      LOOP -- generate INSERT statements from data of the given table
        FETCH cur INTO insert_statement;
        EXIT WHEN cur%NOTFOUND;
        pipe ROW (insert_statement);
        commit_counter := MOD(commit_counter + 1, p_commit_interval);
        IF commit_counter = 0 THEN
          pipe ROW ('commit;');
        END IF;
      END LOOP;
      IF commit_counter = 0 -- there is already a final commit
         OR p_commit_interval = 0 -- caller asked for zero commit
      THEN
        NULL;
      ELSE
        pipe ROW ('commit;'); -- final commit
      END IF;
    END IF;

    RETURN;

  END TAB2INSERT;

  ------------------------------------------------------------------------------
  FUNCTION TABLIST2INSERT (
    p_tablist IN VARCHAR2
   ,p_commit_interval IN PLS_INTEGER DEFAULT NULL
   ,p_ddl IN VARCHAR2 DEFAULT NULL )
    RETURN STRTABLE pipelined
  IS
  BEGIN

    FOR c IN (
      SELECT TABLE_NAME
      FROM USER_TABLES
      WHERE ','||UPPER(p_tablist)||',' LIKE '%,'||TABLE_NAME||',%'
      ORDER BY INSTR(','||UPPER(p_tablist)||',',','||TABLE_NAME||',') )
    LOOP

      pipe ROW ('-- '||c.TABLE_NAME);

      FOR d IN ( SELECT COLUMN_VALUE FROM TABLE(TAB2INSERT(c.TABLE_NAME,p_commit_interval,p_ddl)) )
      LOOP
        pipe ROW (d.COLUMN_VALUE);
      END LOOP;

    END LOOP;

    RETURN;

  END TABLIST2INSERT;

END DATA2INSERT;
/
show errors