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;
