PL/SQL function to create insert script for a table

-- The following function  will create an insert script for a given table.

-- Usage : select get_insert_script('EMP') from dual;
-- This will output to stdout the insert script.
-- Now you may later change the where clause to select a subset of rows from the table.

-- Where its userful:
-- I dont have export priv on production database. I can't really use export the table.
-- This provides an easy way to pull data and then insert into DEV environment to REFRESH the table

--Another alternative way is to select the data as delimited by say comma and then
-- write a sqlloader control file to load the data. This will be faster.

create or replace
FUNCTION GET_INSERT_SCRIPT (V_TABLE_NAME VARCHAR2)
  RETURN VARCHAR2 AS
  B_FOUND  BOOLEAN         := FALSE;
  V_TEMPA  VARCHAR2 (8000);
  V_TEMPB  VARCHAR2 (8000);
  V_TEMPC  VARCHAR2 (255);
BEGIN
  FOR TAB_REC IN (SELECT TABLE_NAME
                    FROM ALL_TABLES
                   WHERE TABLE_NAME = UPPER (V_TABLE_NAME)) LOOP
    B_FOUND := TRUE;
    V_TEMPA := 'select ''insert into ' || TAB_REC.TABLE_NAME || ' (';

    FOR COL_REC IN (SELECT   *
                        FROM ALL_TAB_COLUMNS
                       WHERE TABLE_NAME = TAB_REC.TABLE_NAME
                    ORDER BY COLUMN_ID) LOOP
      IF COL_REC.COLUMN_ID = 1 THEN
        --V_TEMPA := V_TEMPA || '''||chr(10)||''';
        null;
      ELSE
        V_TEMPA := V_TEMPA || ',';
        V_TEMPB := V_TEMPB || ',';
      END IF;

      V_TEMPA := V_TEMPA || COL_REC.COLUMN_NAME;

      IF INSTR (COL_REC.DATA_TYPE, 'CHAR') > 0 THEN
        V_TEMPC := '''''''''||' || COL_REC.COLUMN_NAME || '||''''''''';
      ELSIF INSTR (COL_REC.DATA_TYPE, 'DATE') > 0 THEN
        V_TEMPC :=
             '''to_date(''''''||to_char('
          || COL_REC.COLUMN_NAME
          || ',''mm/dd/yyyy hh24:mi:ss'')||'''''',''''mm/dd/yyyy hh24:mi:ss'''')''';
          ELSIF INSTR (COL_REC.DATA_TYPE, 'TIMESTAMP') > 0 THEN
          V_TEMPC :=
             '''to_timestamp(''''''||to_char('
          || COL_REC.COLUMN_NAME
          || ',''mm/dd/yyyy hh24:mi:ss.ff'')||'''''',''''mm/dd/yyyy hh24:mi:ss.ff'''')''';
      ELSE
        V_TEMPC := COL_REC.COLUMN_NAME;
      END IF;

      V_TEMPB :=
           V_TEMPB
        || '''||decode('
        || COL_REC.COLUMN_NAME
        || ',Null,''Null'','
        || V_TEMPC
        || ')||''';
    END LOOP;

    V_TEMPA :=
         V_TEMPA
      || ') values ('
      || V_TEMPB
      || ');'' from '
      || TAB_REC.TABLE_NAME
      || ';';
  END LOOP;

  IF NOT B_FOUND THEN
    V_TEMPA := '-- Table ' || V_TABLE_NAME || ' not found';
  ELSE
    V_TEMPA := V_TEMPA || CHR (10) || 'select '' commit;'' from dual;';
  END IF;

  RETURN V_TEMPA;
END;