Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: exporting ASCII file from Oracle8
Here is a script that can give you the ASCII dump. As you can see, I did not write it. Jared Still wrote it. Its beautiful !!!
suresh.bhat_at_mitchell-energy.com
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 pages0 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;
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
'replace(' || column_name || ',' || '''' || '"' || '''' || ') ' || ' ||' || '''' || '"' || '''', column_id
select 'from &dumpowner..&dumptable' from dual; select '/' from dual; select 'spool off' from dual;
select 'userid = /' || chr(10) ||
'control = ' || lower('&dumptable') || '.ctl' || chr(10) || 'log = ' || lower('&dumptable') || '.log' || chr(10) || 'bad = ' || lower('&dumptable')|| '.bad' || chr(10)from dual;
'infile ' || ''''|| lower('&dumptable') || '.txt' || '''' || chr(10) ||
'into table &dumptable' || chr(10) || 'fields terminated by ' || '''' || ',' || '''' || 'optionally enclosed by ' || '''' || '"' || '''' || chr(10)from dual;
column_id
from all_tab_columns
where table_name = upper('&dumptable')
and owner = upper('&dumpowner')
and column_id < :maxcol
union
select ' ' || 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 dual;
exit
mustafah_at_criticalsys.com wrote in article
<6khji2$qfb$1_at_nnrp1.dejanews.com>...
> I am trying to export specific columns from an Oracle8 table into an
ASCII
> tab delimited file, I can't seem to find the utility that will perform
this
> functionality. The utility has to be compatible with Oracle7.3 also. > > Another related question is: can I append the exported data to anyexisting
> ASCII file. > > Any suggestions are greatly appreciated. > > Thanks > Mustafa > > -----== Posted via Deja News, The Leader in Internet Discussion ==----- > http://www.dejanews.com/ Now offering spam-free web-based newsreading >Received on Thu Jun 04 1998 - 00:00:00 CDT