Generate Insert Script

2 min read

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.

/*
Try this :-

Arg1 is table name
Arg2 is whereclause on the table (make sure this remain a string by using escape character ” ‘ ” )
*/
CREATE OR REPLACE FUNCTION createinsertsql(TABLENAME VARCHAR2,WHERECLAUSE VARCHAR2)
RETURN clob
IS

CURSOR C1 IS
SELECT * FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = TRIM(UPPER(TABLENAME));

ALLCOLUMNS clob;

TEMP clob ;

ALLCOLUMNSVALUES clob;

BEGIN

FOR CURRENT_RECORD IN C1
LOOP
ALLCOLUMNS := ALLCOLUMNS || ‘ ‘ || CURRENT_RECORD.COLUMN_NAME;
END LOOP;

ALLCOLUMNS := REPLACE(TRIM(ALLCOLUMNS),’ ‘,’,’);

IF ALLCOLUMNS IS NULL THEN
RETURN (‘Table dosen”t exist !!! ‘);
END IF;

TEMP := ”’INSERT INTO ‘ || TABLENAME ;

ALLCOLUMNSVALUES := REPLACE(ALLCOLUMNS,’,’,’||”””,”””||’) ;

TEMP := ‘SELECT ‘ || TEMP || ‘(‘ || ALLCOLUMNS || ‘) VALUES (”’ || CHR(39) || CHR(39) || CHR(124) || CHR(124) || ALLCOLUMNSVALUES || ‘||”””); ”’ || ‘ INSERTSCRIPT FROM ‘ || TABLENAME || ‘ ‘ || WHERECLAUSE || ‘ ;’ ;

RETURN TEMP;

END;
/

Oracle Password Expired

dadin
17 sec read

Job not Running

dadin
18 sec read