• Login
• Become a member
• RSS
• Part of the TechTarget network

I have seen many DBAs asking for a script that generates "INSERT into values...." statements from an existing table's data that could be used as a backup or source script for a future data load. So I thought why not write one for the benefit of everybody. It has been tested on Oracle 7/8i/9i.

This script is dynamic and very user-interactive. When run, it will ask for values for the parameters and then opens up a result file for the user. If the table is very large, WHERE conditions can be included in v_str6.

set echo off

REM This script should be run as SYSTEM/SYS ONLY.
REM This script works for tables with columns of datatypes CHAR,VARCHAR2,NUMBER & DATE ONLY.

set feed off
SET TERMOUT ON
ACCEPT own CHAR prompt 'Enter Value For Schema Name  :<>: '; 
ACCEPT tn CHAR prompt 'Enter Value For Table Name  :<>: '; 
set ver off
 create table ins_cols(owner,tname,colid,colname,datatype) 
 storage(initial 200k next 200k maxextents 512)
 as select owner,table_name,column_id,column_name,data_type from 
 dba_tab_columns where table_name=upper('&tn') and owner=upper('&own')
/
create table ins_text (lineno NUMBER,text varchar2(4000))
storage(initial 200k next 200k maxextents 512)
/

declare
 cursor c1 is select owner,tname,colid,colname,datatype from ins_cols
 order by colid;
 v_colid  number;
 v_maxcolid number;
 v_line  number:=1;
 v_colname varchar2(4000);
 v_tname  varchar2(100);
 v_datatype varchar2(100);
 v_owner  varchar2(100);
 v_str

  varchar2(4000);
 v_str1  varchar2(4000);
 v_str2  varchar2(4000);
 v_str3  varchar2(4000);
 v_str4  varchar2(4000);
 v_str5  varchar2(4000);
 v_str6  varchar2(4000);
 
 procedure write_out(p_line INTEGER,p_str VARCHAR2) is
    begin
         insert into ins_text (lineno,text) values (p_line,p_str);
  commit;
    end;
begin
 select max(colid) into v_maxcolid from ins_cols;
 open c1;
 loop
 fetch c1 into v_owner,v_tname,v_colid,v_colname,v_datatype;
 exit when c1%notfound;

 if v_colid < v_maxcolid then 
  v_str:=v_colname||',';
  v_str1:=v_str1||v_str;
 else  
  v_str:=v_colname||')';
  v_str1:=v_str1||v_str;
 end if;
 end loop;
 close c1;
 v_str2:='select '||chr(39)||'insert into '||v_owner||'.'||v_tname||'('||v_str1||' VALUES ('||CHR(39)||',';

 write_out(v_line,v_str2);
 v_line:=v_line+1;
 
 open c1;
 loop
 fetch c1 into v_owner,v_tname,v_colid,v_colname,v_datatype;
 exit when c1%notfound;
 if v_colid < v_maxcolid then 
  
  if instr(v_datatype,'CHAR')>0 then 
                        v_str3:='decode('||v_colname||','||''''''||','||chr(39)
   ||'NULL'||chr(39)||','||'chr(39)||'||'TRIM('||v_colname||')||chr(39)'||')||'
   ||'chr(44)||';

   write_out(v_line,v_str3);
   v_line:=v_line+1;

      elsif v_datatype='NUMBER' then 
         v_str4:='NVL('||'TO_CHAR('||v_colname||')'||','||CHR(39)
   ||'NULL'||CHR(39)||')'||'||chr(44)||'; 

   write_out(v_line,v_str4);
   v_line:=v_line+1;

      elsif v_datatype='DATE' then 
                        v_str5:='decode('||v_colname||','||''''''||','||chr(39)||'NULL'||chr(39)||','||chr(39)
||'TO_Date('||chr(39)||'||'||'chr(39)||'||'TO_Char('||v_colname||','||'''DD-MON-YYYY HH:MI:SS AM'''||')'
   ||'||chr(39)||'||CHR(39)||','||CHR(39)||'||'||CHR(39)||'''''DD-MON-YYYY HH:MI:SS AM'''''
   ||CHR(39)||'||'||CHR(39)||')'||CHR(39)||')||'||'chr(44)||';

   write_out(v_line,v_str5);
   v_line:=v_line+1;

  end if;
 else
  if instr(v_datatype,'CHAR')>0 then 
                        v_str3:='decode('||v_colname||','||''''''||','||chr(39)
   ||'NULL'||chr(39)||','||'chr(39)||'||'TRIM('||v_colname||')||chr(39)'||')||'
   ||''');''';

   write_out(v_line,v_str3);
   v_line:=v_line+1;

      elsif v_datatype='NUMBER' then 
         v_str4:='NVL('||'TO_CHAR('||v_colname||')'||','||CHR(39)
   ||'NULL'||CHR(39)||')'||'||'||''');'''; 

   write_out(v_line,v_str4);
   v_line:=v_line+1;

      elsif v_datatype='DATE' then 
                        v_str5:='decode('||v_colname||','||''''''||','||chr(39)||'NULL'||chr(39)||','||chr(39)
||'TO_Date('||chr(39)||'||'||'chr(39)||'
   ||'TO_Char('||v_colname||','||'''DD-MON-YYYY HH:MI:SS AM'''||')'
   ||'||chr(39)||'||CHR(39)||','||CHR(39)||'||'||CHR(39)||'''''DD-MON-YYYY HH:MI:SS AM'''''
   ||CHR(39)||'||'||CHR(39)||')'||CHR(39)||')||'||''');''';

   write_out(v_line,v_str5);
   v_line:=v_line+1;

  end if;
  
 end if;
 end loop;
   select distinct owner,tname into v_owner,v_tname from ins_cols;
   v_str6:='from '||v_owner||'.'||v_tname||';'; 

   write_out(v_line,v_str6);
   v_line:=v_line+1;

 close c1;

end; 
/

set termout off
set linesize 2000
set head off
set trimspool on
set pages 2000
spool Ins
 select text from ins_text order by lineno;
spool off
spool Insert
@ins.lst
spool off

DROP TABLE ins_cols
/
DROP TABLE ins_text
/
set echo on
set termout on
set feed on
set ver on
set head on
set feed on
set linesize 80
ed Insert.lst

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.