The following was tested on Oracle 11g.
1. Create a function as below
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)||”’;
ELSE
V_TEMPA := V_TEMPA || ‘,”||chr(10)||”’;
V_TEMPB := V_TEMPB || ‘,”||chr(10)||”’;
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”)||”””,””mm/dd/yyyy hh24:mi””)”’;
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;
2. Call the function like
select get_insert_script(<tablename>) from dual
eg. select get_insert_script(‘hosts’).
I get a consolidated sql script like below :
select ‘insert into HOSTS (‘||chr(10)||’HOSTNAME,’||chr(10)||’SUPPORT_GROUP,’||chr(10)||’ESCALATION_GROUP) values (‘||decode(HOSTNAME,Null,’Null’,””||HOSTNAME||””)||’,'||chr(10)||”||decode(SUPPORT_GROUP,Null,’Null’,””||SUPPORT_GROUP||””)||’,'||chr(10)||”||decode(ESCALATION_GROUP,Null,’Null’,””||ESCALATION_GROUP||””)||’);’ from HOSTS;
Running the above script in sql prompt I get the insert statements for all the rows in ‘hosts’ table.
