Generatting insert statement for given table/column value dynamically

Question

I need to write a procedure, which should return insert script for any given table dynamically. Below is the signature of the procedure. P_export_data_dml(table_name IN, column_name IN, column_value IN, data_dml OUT)

Ex: table T1

    C1      |    C2  
----------  |   ----------
    10      |    20
    10      |    21

table T2

    C1      |    C2   |    C3
  -------   | ------- | -------- 
    10      |    20   |    30
    10      |    21   |    31

case1

Input:
P_export_data_dml(T1, C1, 10, data_dml)

Expected output:
Insert into T1(C1, C2) values(10,20);
Insert into T1(C1, C2) values(10,21);

case2

Input:
P_export_data_dml(T2, C1, 10, data_dml)

Expected output:
Insert into T2(C1, C2, C3) values(10, 20, 30);
Insert into T2(C1, C2, C3) values(10, 21, 31);

I am able to generate an insert statement when there is only one record in a table for given input. (Using all_tab_columns and then fetching given table for each column to form parts of the Insert statement, and then finally concatenating all the strings to form final INSERT statement).

But I am facing a challenge to form an insert statement when there are multiple records. Can you please help me with the logic to loop through the records and to form INSERT statement for all the records.

Answer 1

Try this procedure. Note that it is good for NUMBER, VARCHAR, DATE (when has a default format) data types

set serveroutput on
create or replace
procedure p_export_data_dml(p_table in varchar2, p_filter_column in varchar2, p_filter_value in varchar2, p_dmls in out varchar2)
is
  cursor c_statements(p_table varchar2, p_filter_column varchar2, p_filter_value varchar2) is
    select 'select ''insert into '||p_table||' ('||
        listagg(column_name, ', ') within group (order by column_id) ||') values(''''''||'||
        listagg(column_name, '||'''''', ''''''||') within group (order by column_id)||'||'''''');'' from '||p_table||' where '||p_filter_column||' = '''||p_filter_value||'''' insert_statement
    from user_tab_columns
    where table_name = upper(p_table);

  v_output varchar2(4000);
  v_sql varchar2(4000);
  type t_cursor is ref cursor;
  c_cur t_cursor;
begin
  for r_statements in c_statements(p_table, p_filter_column, p_filter_value) loop
      v_sql := r_statements.insert_statement;
      dbms_output.put_line(v_sql);
      open c_cur for v_sql;
      loop
        fetch c_cur into v_output;
        exit when c_cur%notfound;
        if p_dmls = null then
          p_dmls := v_output;
        else
          p_dmls := p_dmls || '
        '||v_output;
        end if;
      end loop;
      close c_cur;
  end loop;
end;
/

Then you can try executing using the below

declare
  v_text varchar2(32000);
begin
  p_export_data_dml('t1', 'c1', 10, v_text);
  dbms_output.put_line(v_text);
end;
/

The output can be something like this:

    insert into t1 (C1, C2, C3, C4) values('10', '1', '11', '12-SEP-2017 07:54:38');
    insert into t1 (C1, C2, C3, C4) values('10', '2', '12', '12-SEP-2017 07:54:38');
    insert into t1 (C1, C2, C3, C4) values('10', '3', '13', '12-SEP-2017 07:54:38');

Answer 2

It isn't clear from your question what data-type you are planning on using for data_dml OUT.
This could be a collection, or a clob, etc.

Please note, that most Oracle-supporting editors like SQL Developer, Intellij IDEA, TOAD, etc. already have this kind of thing built right in, with robust implementation to transform result sets into INSERTS.

That being said, this kind of thing can be achieved using Dynamic SQL.

Here are a couple light-weight examples, built on the premise you plan on using only NUMBERs for column_value params. Overloads could be added for others, or ANYDATA, as needed.

Example 1: This example will return multiple INSERT statements in a CLOB (a collection return type could be used instead). It will only generate INSERTs for NUMBER DATA_TYPE columns. Note no error handling here.

CREATE OR REPLACE PROCEDURE P_EXPORT_DATA_DML(P_TABLE_NAME IN VARCHAR2, P_COLUMN_NAME IN VARCHAR2, P_COLUMN_VALUE IN NUMBER, V_DATA_DML OUT CLOB)
IS
  P_COLUMN_LIST       CLOB;
  V_VALUES_LIST       CLOB;
  TYPE USER_TAB_COL_TABLE IS TABLE OF USER_TAB_COLUMNS%ROWTYPE;
  V_USER_TAB_COLS     USER_TAB_COL_TABLE;
  V_SELECTER_SQL_TEXT CLOB := '';

  BEGIN

    SELECT *
    BULK COLLECT INTO V_USER_TAB_COLS
    FROM USER_TAB_COLUMNS
    WHERE USER_TAB_COLUMNS.TABLE_NAME = P_TABLE_NAME
          AND USER_TAB_COLUMNS.DATA_TYPE IN ('NUMBER');

    P_COLUMN_LIST := P_COLUMN_LIST || V_USER_TAB_COLS(1).COLUMN_NAME;
    V_SELECTER_SQL_TEXT := V_SELECTER_SQL_TEXT || V_USER_TAB_COLS(1).COLUMN_NAME;

    FOR POINTER IN 2..V_USER_TAB_COLS.COUNT
    LOOP
      P_COLUMN_LIST := P_COLUMN_LIST || ',' || V_USER_TAB_COLS(POINTER).COLUMN_NAME;
      V_SELECTER_SQL_TEXT := V_SELECTER_SQL_TEXT || Q'!||','|| !' || V_USER_TAB_COLS(POINTER).COLUMN_NAME;
    END LOOP;

    V_SELECTER_SQL_TEXT := UTL_LMS.FORMAT_MESSAGE(Q'!SELECT LISTAGG('INSERT INTO %s !', P_TABLE_NAME) || '(' || P_COLUMN_LIST || Q'!) VALUES ( '||!' || V_SELECTER_SQL_TEXT || UTL_LMS.FORMAT_MESSAGE(Q'!||');'||CHR(10)||CHR(13) ) WITHIN GROUP (ORDER BY %s ASC) FROM !', P_COLUMN_NAME) || P_TABLE_NAME || ' WHERE ' || P_COLUMN_NAME || ' = ' || P_COLUMN_VALUE;
    EXECUTE IMMEDIATE V_SELECTER_SQL_TEXT INTO V_DATA_DML;

  END;
/

Then try it out for T1/C1:

DECLARE
  V_RESULT CLOB;
BEGIN
  P_EXPORT_DATA_DML('T1','C1',10,V_RESULT);
  DBMS_OUTPUT.PUT_LINE(V_RESULT);
END;
/

INSERT INTO T1 (C1,C2) VALUES ( 10,20);
INSERT INTO T1 (C1,C2) VALUES ( 10,21);
PL/SQL procedure successfully completed.

Or for T2/C1:

DECLARE
  V_RESULT CLOB;
BEGIN
  P_EXPORT_DATA_DML('T2','C1',10,V_RESULT);
  DBMS_OUTPUT.PUT_LINE(V_RESULT);
END;
/

INSERT INTO T2 (C1,C2,C3) VALUES ( 10,20,30);
INSERT INTO T2 (C1,C2,C3) VALUES ( 10,21,31);
PL/SQL procedure successfully completed.

Or for T2/C2:

...
P_EXPORT_DATA_DML('T2','C2',20,V_RESULT);
...
INSERT INTO T2 (C1,C2,C3) VALUES ( 10,20,30);
PL/SQL procedure successfully completed.

To support other DATA_TYPEs, you'll need to handle DATE/TIMESTAMP -> CHAR conversions, quoting CHARs, etc.

Here's an example that supports NUMBER + VARCHAR2

CREATE OR REPLACE PROCEDURE P_EXPORT_DATA_DML(P_TABLE_NAME IN VARCHAR2, P_COLUMN_NAME IN VARCHAR2, P_COLUMN_VALUE IN NUMBER, V_DATA_DML OUT CLOB)
IS
  P_COLUMN_LIST       CLOB;
  V_VALUES_LIST       CLOB;
  TYPE USER_TAB_COL_TABLE IS TABLE OF USER_TAB_COLUMNS%ROWTYPE;
  V_USER_TAB_COLS     USER_TAB_COL_TABLE;
  V_SELECTER_SQL_TEXT CLOB := '';

  BEGIN

    SELECT *
    BULK COLLECT INTO V_USER_TAB_COLS
    FROM USER_TAB_COLUMNS
    WHERE USER_TAB_COLUMNS.TABLE_NAME = P_TABLE_NAME
          AND USER_TAB_COLUMNS.DATA_TYPE IN ('NUMBER', 'VARCHAR2');

    P_COLUMN_LIST := P_COLUMN_LIST || V_USER_TAB_COLS(1).COLUMN_NAME;

    CASE WHEN V_USER_TAB_COLS(1).DATA_TYPE = 'NUMBER'
      THEN
        V_SELECTER_SQL_TEXT := V_SELECTER_SQL_TEXT || V_USER_TAB_COLS(1).COLUMN_NAME;
      WHEN V_USER_TAB_COLS(1).DATA_TYPE = 'VARCHAR2'
      THEN
        V_SELECTER_SQL_TEXT := V_SELECTER_SQL_TEXT || Q'!''''||!' || V_USER_TAB_COLS(1).COLUMN_NAME || Q'!||''''!';
    END CASE;

    FOR POINTER IN 2..V_USER_TAB_COLS.COUNT
    LOOP
      P_COLUMN_LIST := P_COLUMN_LIST || ',' || V_USER_TAB_COLS(POINTER).COLUMN_NAME;
      CASE WHEN V_USER_TAB_COLS(POINTER).DATA_TYPE = 'NUMBER'
        THEN
          V_SELECTER_SQL_TEXT := V_SELECTER_SQL_TEXT || Q'!||','|| !' || V_USER_TAB_COLS(POINTER).COLUMN_NAME;
        WHEN V_USER_TAB_COLS(POINTER).DATA_TYPE = 'VARCHAR2'
        THEN
          V_SELECTER_SQL_TEXT := V_SELECTER_SQL_TEXT || Q'!||','|| !' || Q'!''''||!' || V_USER_TAB_COLS(POINTER).COLUMN_NAME || Q'!||''''!';
      END CASE;

    END LOOP;

    V_SELECTER_SQL_TEXT := UTL_LMS.FORMAT_MESSAGE(Q'!SELECT LISTAGG('INSERT INTO %s !', P_TABLE_NAME) || '(' || P_COLUMN_LIST || Q'!) VALUES ( '||!' || V_SELECTER_SQL_TEXT || UTL_LMS.FORMAT_MESSAGE(Q'!||');'||CHR(10)||CHR(13) ) WITHIN GROUP (ORDER BY %s ASC) FROM !', P_COLUMN_NAME) || P_TABLE_NAME || ' WHERE ' || P_COLUMN_NAME || ' = ' || P_COLUMN_VALUE;
    EXECUTE IMMEDIATE V_SELECTER_SQL_TEXT INTO V_DATA_DML;

  END;
/

Test it out with a VARCHAR2 table:

CREATE TABLE T3 (C1 VARCHAR2(64), C2 NUMBER, C3 VARCHAR2(64));
INSERT INTO T3 VALUES ('XX',10,'AA');
INSERT INTO T3 VALUES ('XQ',10,'AQ');
INSERT INTO T3 VALUES ('XX',20,'AA');

Getting multiple rows:

DECLARE
  V_RESULT CLOB;
BEGIN
  P_EXPORT_DATA_DML('T3','C2',10,V_RESULT);
  DBMS_OUTPUT.PUT_LINE(V_RESULT);
END;
/

INSERT INTO T3 (C1,C2,C3) VALUES ( 'XQ',10,'AQ');
INSERT INTO T3 (C1,C2,C3) VALUES ( 'XX',10,'AA');
PL/SQL procedure successfully completed.