Generate Insert Statement Script to Extract Data from Oracle Table

Here, you can use the following code to generate insert statement to extract the existing data from an oracle table. The generated script can be run at a later time to re-create your data. The code handles only date, char, varchar2, and numeric data types. I got this code from googling, original version by Ashish Kumar, but i have modify user_tables to ALL_TABLES, user_tab_columns to ALL_COLUMNS, because i want to get insert script from another user schema, not only from my user schema.

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_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;
/
SHOW ERRORS

After you create this procedure, you can just use it, for example:
SELECT GET_INSERT_SCRIPT('TMP_VINTAGE') FROM DUAL;

you will have the result like this:

select 'insert into DEPT ('||chr(10)||'DEPTNO,'||chr(10)||'DNAME,'||chr(10)||'LOC) values ('||decode(DEPTNO,Null,'Null',DEPTNO)||','||chr(10)||''||decode(DNAME,Null,'Null',''''||DNAME||'''')||','||chr(10)||''||decode(LOC,Null,'Null',''''||LOC||'''')||');' from DEPT;select '-- commit;' from dual;

Then you get this,

insert into DEPT (DEPTNO,DNAME,LOC) values (10,’ACCOUNTING’,’NEW YORK’);
insert into DEPT (DEPTNO,DNAME,LOC) values (20,’RESEARCH’,’DALLAS’);
insert into DEPT (DEPTNO,DNAME,LOC) values (30,’SALES’,’CHICAGO’);
insert into DEPT (DEPTNO,DNAME,LOC) values (40,’OPERATIONS’,’BOSTON’);
–commit;

The other way, use this script,

set head off
set pages 0
set trims on
set lines 2000
set feed off
set echo off
var retline varchar2(4000)
spool c:\temp.sql
select 'set echo off' from dual;
select 'spool c:\getinsert.sql' from dual;
select 'select ''-- This data was extracted on ''||to_char(sysdate,''mm/dd/yyyy hh24:mi'') from dual;' from dual;-- Repeat the following two lines as many times as tables you want to extract
exec :retline:=GET_INSERT_SCRIPT('DEPT');
print retline;

exec :retline:=GET_INSERT_SCRIPT(‘EMP’);
print retline;

select ‘spool off’ from dual;
spool off
@c:\temp

— FINALLY: Run the spooled output c:\getinsert.sql to recreate data.

If anyone want to get this script, please download it from “wiBox”. It’s a widget from left sidebar (see GET_INSERT_SCRIPT.sql).