CODEWITHSUNDEEP
oracle-databaseinsert
UltraCommit
UltraCommit

Reputation: 2276

ORACLE - GENERATE INSERT STATEMENT

The following function generates the SELECT to produce INSERT STATEMENT of the records of an Oracle table:

CREATE OR REPLACE FUNCTION GEN_INSERT_STATEMENT (IN_TABLE_NAME VARCHAR2)
   RETURN CLOB
IS
   LC$COLS_SELECT   CLOB;
   LC$COLS_VALUES   CLOB;
   LC$COLOUMN       CLOB;

   CURSOR LCUR$TAB_COLUMNS (IN_TABLE_NAME VARCHAR2)
   IS
        SELECT   COLUMN_NAME, DATA_TYPE, COLUMN_ID
          FROM   USER_TAB_COLS
         WHERE   TABLE_NAME = IN_TABLE_NAME
      ORDER BY   COLUMN_ID;
BEGIN
   FOR LREC$TAB_COLUMNS IN LCUR$TAB_COLUMNS (UPPER (IN_TABLE_NAME))
   LOOP
      LC$COLS_SELECT :=
            LC$COLS_SELECT
         || CASE LREC$TAB_COLUMNS.COLUMN_ID WHEN 1 THEN '' ELSE ',' END
         || LREC$TAB_COLUMNS.COLUMN_NAME;

      IF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'CHAR') > 0
      THEN
         LC$COLOUMN :=
               '''''''''||REPLACE('
            || LREC$TAB_COLUMNS.COLUMN_NAME
            || ','''''''','''''''''''')||''''''''';
      ELSIF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'DATE') > 0
      THEN
         LC$COLOUMN :=
            '''TO_DATE(''''''||TO_CHAR(' || LREC$TAB_COLUMNS.COLUMN_NAME
            || ',''mm/dd/yyyy hh24:mi:ss'')||'''''',''''mm/dd/yyyy hh24:mi:ss'''')''';
      ELSE
         LC$COLOUMN := LREC$TAB_COLUMNS.COLUMN_NAME;
      END IF;

      LC$COLS_VALUES :=
            LC$COLS_VALUES
         || CASE LREC$TAB_COLUMNS.COLUMN_ID WHEN 1 THEN '' ELSE ',' END
         || '''||DECODE('
         || LREC$TAB_COLUMNS.COLUMN_NAME
         || ',NULL,''NULL'','
         || LC$COLOUMN
         || ')||''';
   END LOOP;

   RETURN    'SELECT ''INSERT INTO '
          || IN_TABLE_NAME
          || ' ('
          || LC$COLS_SELECT
          || ') VALUES ('
          || LC$COLS_VALUES
          || ');'' FROM '
          || IN_TABLE_NAME
          || ';';
END;
/

The problem is that this function DOES NOT HANDLE the case in which are existing some VARCHAR2 fields with strings ending with: CHR(0)

Usage:

SELECT GEN_INSERT_STATEMENT ('MY_ORACLE_TABLE_NAME') FROM DUAL;

... generates a SELECT to produce the INSERT statement.

If in a VARCHAR2 field the value ends with CHR(0), the INSERT statement will be truncated exactly where is CHR(0) located.

How can I fix this?

Upvotes: 1

Views: 3000

Answers (2)

teopost
teopost

Reputation: 121

Use this:

Usage:

select fn_gen_inserts('select * from tablename', 'p_new_owner_name', 'p_new_table_name')
from dual;

where:

p_sql            –  dynamic query which will be used to export metadata rows
p_new_owner_name – owner name which will be used for generated INSERT
p_new_table_name – table name which will be used for generated INSERT

You can find original source code here:

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

If I understand what you're seeing, you need to either strip the null character from the value:

  IF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'CHAR') > 0
  THEN
     LC$COLOUMN :=
           '''''''''||REPLACE(REPLACE('
        || LREC$TAB_COLUMNS.COLUMN_NAME
        || ',CHR(0),NULL),'''''''','''''''''''')||''''''''';

which gives your function the output:

SELECT 'INSERT INTO T42 (STR) VALUES ('||DECODE(STR,NULL,'NULL',''''||REPLACE(REPLACE(STR,CHR(0),NULL),'''','''''')||'''')||');' FROM T42;

and with a dummy table called t42 with a single column str containing 'hello' followed by a null character, the output is:

INSERT INTO T42 (STR) VALUES ('Hello');

Or preserve it during the insert:

     LC$COLOUMN :=
           '''''''''||REPLACE(REPLACE('
        || LREC$TAB_COLUMNS.COLUMN_NAME
        || ','''''''',''''''''''''),CHR(0),''''''||CHR(0)||'''''')||''''''''';

which gives:

SELECT 'INSERT INTO T42 (STR) VALUES ('||DECODE(STR,NULL,'NULL',''''||REPLACE(REPLACE(STR,'''',''''''),CHR(0),'''||CHR(0)||''')||'''')||');' FROM T42;

and finally:

INSERT INTO T42 (STR) VALUES ('Hello'||CHR(0)||'');

So in the second version the null character is removed from the fixed string (anywhere, not just at the end), and is put back as part of the insert statement.

This seems like a horrible approach though, when you could export the data, or let SQL Developer or some other IDE generate the insert statements for you. There maybe other data types and values that give you headaches, and which someone else has already worked hard to overcome. Unless you really need to be able to see the actual statements, using expdp would be far simpler.

Upvotes: 2

Related Questions