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).

About these ads

79 Responses

  1. Loading this stored procedure gives the following errors:

    SQL> SHOW ERRORS
    Errors for FUNCTION GET_INSERT_SCRIPT:

    LINE/COL ERROR
    ——– —————————————————————–
    23/37 PLS-00103: Encountered the symbol “;
    ELSE
    V_TEMPA := V_TEMPA || `,”||chr(10)||”” when expecting one of the
    following:
    . ( * @ % & = – + ; at in is mod remainder not rem
    or != or ~= >= <= and or like LIKE2_
    LIKE4_ LIKEC_ between || member SUBMULTISET_
    The symbol “*” was substituted for “;
    ELSE
    V_TEMPA := V_TEMPA || `,”||chr(10)||”” to continue.

    26/23 PLS-00103: Encountered the symbol “`” when expecting one of the
    following:
    ( – + case mod new null
    avg
    count current max min prior sql stddev sum variance execute
    forall merge time timestamp interval date

    pipe

    SQL>

    • Can you please send me the insert script ?, i ad get using the above script i am facing the problem mentioned below
      Error(20,37): PLS-00103: Encountered the symbol “; ELSE V_TEMPA := V_TEMPA || ” when expecting one of the following: . ( * @ % & = – + ; at in is mod remainder not rem or != or ~= >= <= and or like LIKE2_ LIKE4_ LIKEC_ between || member SUBMULTISET_

      Thanks in advance.
      Awaiting for the reply…

    • I i updated the script changing the quotes and the correct table names.

      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;
      /

      • i don’t know how to paste it on wordpress with original qoute,
        may be i try again to edit this post, thx

      • Please sent the insert script at my email
        jitendra.saha@srl.in

      • ok

  2. when i put this info on wordpress, some character display not properly, so you can try to get this file back.

    you can get GET_INSERT_SCRIPT on wiBox at left sidebar

    Thx for response

  3. ALL_COLUMNS should be ALL_TAB_COLUMNS….

  4. Good luck compiling that mess.

  5. when i paste above code in Notepad, some character display not properly, so you provide me above code on my email
    mailbox.sushil@gmail.com with txt format

    thanx in Advance

  6. you can get GET_INSERT_SCRIPT on wiBox at left sidebar

    If you can’t get it, so i send it to your email, please check it out, thx

  7. Hi,
    can u plz send me the get_insert_script on my mail id ..
    I am not able to copy it without errors.
    thanx in advance..
    mail id : saket.tcs@gmail.com

  8. I really apreciate your code !!!
    Thanks

    Best regards from Brasil

  9. Hi Pandazen,
    Your generate insert script is pretty interesting, could you send me the copy? Thanks a lot.

    Best Regards from Malaysia

  10. the script had send to saket bansal and ckwan

  11. Hi Pandazen,

    Can you please send me the insert script ?
    Really appreciate your help.
    Thanks in advance.

    Best Regards.

    • Hi sachin, script in your mail now

      thx

    • sorry for the long answer

  12. Excellent.
    Thanks.

  13. Hey Pandazan,

    Intresting script you made. Can you please send me a copy?

    Thanks in advance,

    • email in ericvde gmail

  14. Hi pandazen,

    The script seems to be very useful.
    Can you please send the Script in my email_id !!

    Thanks & regards

    srinivas

  15. Hi,
    can u plz send me the get_insert_script on my mail id ..
    I am not able to copy it without errors.

    thanks
    srini

  16. ok, in your mail srini

  17. Hi panda, Is it possible to send a copy to me as well!!Appreciate it mate!

  18. Can you explain what teh codes does…and I am not getting teh right results…

    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;
    How did you get to thsi step?…it woudl be gerat if you coiuld explain step by step

  19. I had modified the pandazan script to resolved variable assign constrain problem.

    Thanks to Pandazen for being come out the idea…

  20. After correcting the ALL_COLUMNS to ALL_TAB_COLUMNS, it works for a small test table, but when I ask for a script for a table having 125 columns, I get:

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at “STAGEADM.GET_INSERT_SCRIPT”, line 38

    Is there any way to avoid this?

  21. try resize V_TEMPA & V_TEMPB to larger size

  22. I copied the stored proc and ran for small table
    i am getting following error
    numeric or value error: character string buffer too small
    i also increased size V_TEMPA & V_TEMPB to 32500 then also no results. pls. help me.

  23. Thank’s it worked for me.
    But while executing this script
    I got scripts containg data like
    ‘abc’def’ but i want it to be in this format ‘abc”def’ .
    pls help me

  24. create or replace function xxfin.xx_d_gen_ins(v_owner varchar2,v_table_name varchar2)
    return varchar2 as
    b_found boolean := false;
    v varchar2(32000);
    v1 varchar2(32000);
    v2 varchar2(32000);
    begin
    for s in (
    select *
    from all_tables
    where table_name=upper(v_table_name)
    and owner=upper(v_owner)
    ) loop
    b_found := true;

    for ss in (
    select *
    from all_tab_columns –where owner=’GL’ and table_name=’GL_BALANCES’
    where table_name = s.table_name
    and owner=s.owner
    order by column_id
    ) loop
    if ss.data_type=’NUMBER’ then
    v1:=v1||’,’||ss.column_name;
    v2:=v2||’,”””||to_char(‘||ss.column_name||’)||”””';
    end if;
    if ss.data_type in (‘VARCHAR2′,’CHAR’) then
    v1:=v1||’,’||ss.column_name;
    v2:=v2||’,”””||replace(replace(‘||ss.column_name||’,””””,””””””),”&”,””)||”””';
    end if;
    if ss.data_type=’DATE’ then
    v1:=v1||’,’||ss.column_name;
    v2:=v2||’,to_date(”””||to_char(‘||ss.column_name||’,”dd.mm.yyyy hh:mi:ss”)||”””,””dd.mm.yyyy hh:mi:ss””)';
    end if;
    end loop;

    v:=’select ”insert into ‘||s.owner||’.’||s.table_name||’ (‘;
    v:=v||substr(v1,2,9999)||’) ‘||chr(10)||’ values (‘||substr(v2,2,9999)||’); ” txt from ‘||s.owner||’.’||s.table_name;

    end loop;

    if not b_found then
    v:=’– Table ‘ || v_table_name || ‘ not found';
    else
    v:=v;
    end if;

    return v;
    end;
    /

    • Please send me the script by email. I have difficultuy in compilng this code.

      Thanks

    • Hi Pavlo,

      Could you send me the script to ponicpoolatgmail.com

      Thanks and appreciated

  25. Hi,

    really useful script, but is there any way to include CLOB, BLOB data types in the insert statement cause am getting “ORA-00932: inconsistent datatypes”

    Thanks anyways!

  26. Hi Pandazan,

    Could you kindly send the GET_INSERT_SCRIPT to ponicpoolatgmail.com

    Thanks and appreciated.

  27. Hi Pandazan,

    I could’t find the file. Can you please send me the GET_INSERT_SCRIPT to lihodinho@yahoo.com

    Thanks and sorry for troublesome.

    • pandazen not pandazan,

      script has been sent

  28. Pandazen,

    can i also get a copy of the script ?

    Many thanks

  29. Can you please send me the GET_INSERT_SCRIPT to pradeep_puru@yahoo.co.in

    Thanks in advance

  30. Can you send the script to my pradeep_puru@yahoo.co.in

    Thanks in advance

  31. Hi Pandazan,

    Could you please send me the GET_INSERT_SCRIPT script to my pradeep_puru@yahoo.co.in

    Thanks in advances

  32. Hi Pendazan,

    When i am firing the GET_Insert_Script for a table which i am using for the project, i am getting the following error

    SQL Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at “NXOWNER.GET_INSERT_SCRIPT”, line 76
    06502. 00000 – “PL/SQL: numeric or value error%s”

    Can you please let me know what would be problem or assist me on this.

    Thanks in advance
    Pradeep

    • You could have a lot of fields in your table. You could try replacing the CHR(10) parts of the script with spaces, or you could try using CLOB instead of VARCHAR2.

  33. Hi PandaZen,

    could you please send me the script to my email address: roozii_1@yahoo.com

    because I’ve got some errors when I try to compile your script..

    Many thanks in advance =)

  34. Upps..

    Sorry, i missed one character, my email address is: roozii_01@yahoo.com

    thank you

    • send it…

  35. Good, we can make code as per our requirement with minor changes.

    thanks a lot

  36. Hi,

    Having difficulty copying the text, could you please send me the script on mail?
    Thanks
    Michael

    • send it

  37. Good!

    Pandazen, I thing you should consider changing the fonts type of your blog… it’s nice, but as you can see, it doesn’ t work pretty weel for scripts, IMHO.

    thanks a lot

  38. yeah, i think i have problem when posting script to wordpress. The character ” change to ` when i paste to wordpress editor.

    Instead of copy from wordpress, please download the script form widget on left sidebar, “wiBox”, look for DBF2ORA. ^_^

  39. Good day blogger, I notice your web blog from delicious as well as I start reading a few of your several other content articles.They are great. If you please keep on this excellent work.

  40. Thanks for this function. I have found a couple of issues when using it:
    1. If you have a table in another schema with the same name as the one you are trying to export, the script will include the fields from that table in the script.
    2. If you have a lot of fields, the script can exceed the maximum size allowed for the varchar2 variables.

  41. Oh, and by the way, this page hangs up my browser (Firefox on OSX Leopard) for several seconds each time I access it.

  42. CAN ANYONE SEND IT TO MY EMAIL
    ENG_SALLAL@YAHOO.COM

    THANKS IN ADVANCE

  43. Pandazen, could you send me the script by email, please?

    My account is: proprietario@gmail.com

    Thank you in advance for your kind help.

  44. hi please any one send me correct code with execute statment to my mail id premit444@gmail.com

    • has been sent ^_^

  45. Good one. Very useful.

  46. USE THIS WORKS FINE
    Generate insert into dump of table

    http://www.xinotes.org/notes/note/597/

  47. Can you please send me the GET_INSERT_SCRIPT to prashant.turuk@igatepatni.com

    Thanks in advance

  48. ok

  49. Please, can you send me the script as well to
    sleek_maleeq@yahoo.com

    Thanks alot

    • send

  50. can you please send me this script on ravinder35@gmail.com

  51. /*
    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;
    /

    • Can you please send the script to veeresh2222@gmail.com

  52. To avoid the irksome ORA-06512 errors:
    1) replace ALL_TABLES with USER_TABLES
    2) replace ALL_TAB_COLUMNS with USER_TAB_COLUMNS
    3) get rid of all the chr(10)’s … they chew up lots of space in the temp varchars and, imho, make the resulting queries much more difficult to work with.

    After making the above changes, I can export tables with 45+ columns.

    –Ken

  53. Could you send me the script to federicosibilio@gmail.com.
    thanks a lot :)

  54. Hi pandazen, could you please send the final version of the script to proprietario@gmail.com ?

  55. Hi pandazen, kindly send me the script to nandana252@yahoo.com

    • sorry not respond for long time, emailed

  56. Its a good script.. very helpful pls mail it to venkateshkmr.m@gmail.com

  57. hi this is a super script, can u send the script to my mail
    famethree@gmail.com

  58. Can you please send me the final bug free copy of the script to sugath29@hotmail.com
    Thanks. for your help.

  59. Hi this is useful..
    can u send me the along with the clob data type

  60. Hi this is very useful.
    But I need to have for the CLOB data type

  61. its too good.. can i have script for all data types

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 46 other followers

%d bloggers like this: