Oracle Scripts - dbapool.com

REM: Script to convert table data into comma delimited format.
REM:
REM: Author: Puneet Sharma
REM: Date Submitted:  12/11/2004
REM: Date Posted: 12/11/2004
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

set pagesize 0
set trimspool on 
set serverout on 
clear buffer 
undef dumpfile 
undef dumptable 
undef dumpowner 
var maxcol number 
var linelen number 
var dumpfile char(40) 
col column_id noprint 
set pages 0 feed off termout on echo off verify off 
accept dumpowner char prompt 'Owner of table to dump: ' 
accept dumptable char prompt 'Table to dump: '

begin 
  select max (column_id) 
  into   :maxcol 
  from   all_tab_columns 
  where  table_name = rtrim (upper ('&dumptable')) and 
         owner      = rtrim (upper ('&dumpowner')); 
         
  select sum (data_length) + (:maxcol * 3)
  into   :linelen 
  from   all_tab_columns 
  where  table_name = rtrim (upper ('&dumptable')) and 
         owner      = rtrim (upper ('&dumpowner')); 
end; 
/

print linelen 
print maxcol

spool tbdump.sql 
select 'set trimspool on' from dual; 
select 'set termout off pages 0 heading off echo off' from dual; 
select 'set line '||:linelen from dual; 
select 'spool '||lower ('&dumptable')||'.txt' from dual; 
select 'select'||chr (10) from dual; 
select '   '||''''||'"'||''''||'||'|| 
       'replace ('||column_name||', '||''''||'"'||''''||') '||
       ' ||'||''''||'", '||''''||' || ', column_id 
from   all_tab_columns 
where  table_name = upper ('&dumptable') and 
       owner      = upper ('&dumpowner') and 
       column_id  < :maxcol 
union 
select '   '||''''||'"'||''''||'||'|| 
       'replace ('||column_name||', '||''''||'"'||''''||') '||
       ' ||'||''''||'"'||'''', column_id 
from   all_tab_columns 
where  table_name = upper ('&dumptable') and 
       owner      = upper ('&dumpowner') and 
       column_id  = :maxcol 
order  by 2;
select 'from &dumpowner..&dumptable;' from dual; 
select 'spool off' from dual; 
spool off ;

Home Discussion Forum FAQs Articles Jobs Newsletters Directory Downloads

Our Premium Sponsor