Generate Insert Script for a table For Oracle

Generating Insert Script may be a simple task if your are using Toad or any Editor. But If you are working with Oracle PL SQL directly, it IS a tough task. You will have to write script. Below function will help you to generate the insert script of the table. First I will list the function then the implementation of the same

create or replace Function GenInsScript(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 user_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 user_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;

Now to the Implementation

--------------SAMPLE TABLE -------------------------

CREATE TABLE TEST_TABLE

(

  COL1  NUMBER,

  COL2  VARCHAR2(100 BYTE)

)

insert into TEST_TABLE (

COL1,

COL2) values (1,

'test');

insert into TEST_TABLE (

COL1,

COL2) values (2,

'test2');

insert into TEST_TABLE (

COL1,

COL2) values (3,

'test3');

commit;

-----------------------------------

Steps for generating the Script

select GenInsScript('test_table') from dual

This Query would give the below output

select 'insert into TEST_TABLE ('||chr(10)||'COL1,'||chr(10)||'COL2) values ('||decode(COL1,Null,'Null',COL1)||','||chr(10)||''||decode(COL2,Null,'Null',''''||COL2||'''')||');' from TEST_TABLE;

select '-- commit;' from dual;

Executing this Query would result in

insert into TEST_TABLE (

COL1,

COL2) values (1,

'test');

insert into TEST_TABLE (

COL1,

COL2) values (2,

'test2');

insert into TEST_TABLE (

COL1,

COL2) values (3,

'test3');

commit;