Delimited data set generator

This tip is useful for making a backup of a given table's data in a delimited format. The spool file generated can then be used for future data loads using a tool like SQL*Loader or any VB application. The script works on Oracle 7.x, 8.x and 9.x.

REM This script should be run as SYSTEM/SYS ONLY.
REM This script generates a text file with data for the given table, owner and delimiter.
REM This script works for tables with columns of datatypes CHAR, VARCHAR2, NUMBER and 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
:<>: '; 
ACCEPT dl CHAR prompt 'Enter Value For Delimiter (Eg: | Or , Or : Or ;)
:<>: '; 
prompt
prompt Generating The Delimited Data Sets.... Please Wait...
prompt

set ver off

create table ins_text (lineno NUMBER,text varchar2(2000))
storage(initial 200k next 200k maxextents 512)
/

declare
cursor c1 is select owner,table_name,column_id,column_name from 
dba_tab_columns where owner=upper('&&own') and
table_name=upper('&&tn') 
order by column_id;

v_colid number;
v_maxcolid number;
v_line number:=1;
v_colname varchar2(100);
v_tname varchar2(100);
v_owner varchar2(100);
v_str varchar2(2000);
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(COLUMN_ID)INTO v_maxcolid FROM DBA_TAB_COLUMNS WHERE
OWNER=upper('&&own')
AND TABLE_NAME=upper('&&tn');

v_str:='Select';
write_out(v_line,v_str);
v_line:=v_line+1;

open c1;
loop
fetch c1 into v_owner,v_tname,v_colid,v_colname;
exit when c1%notfound;

if v_colid < v_maxcolid then 
v_str:=v_colname||'||'||chr(39)||'&dl'||chr(39)||'||';
write_out(v_line,v_str);
v_line:=v_line+1;
else 
v_str:=v_colname;
write_out(v_line,v_str);
v_line:=v_line+1;
end if;
end loop;
close c1;

v_str:='from '||v_owner||'.'||v_tname||';';
write_out(v_line,v_str);
v_line:=v_line+1;
end; 
/

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

DROP TABLE ins_text
/
set termout on
set feed on
set ver on
set head on
set linesize 80
set wrap off
undefine own
undefine tn
undefine dl
set echo on
ed TabToText.lst