Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Data source for SQLLOADER
Hi,
You can use scripts to output your data from an Oracle table to a flat file :
--
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
select ' ' || '''' || '"' || '''' || ' || ' || '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
<newbie22_at_my-deja.com> wrote in message news:81m7jd$eit$1_at_nnrp1.deja.com...
> HI all, > > I'm familiar with importing data with SQLLOADER. > > But now I'm wondering, > where does the source data come from? > > I can't find any SQLLOADER commands to unload the > data from tables. Can SQLLOADER unload the data? > > Which Oracle utility/command would unload the data to a > Unix flat file? > > Cheers, > > Argosy > > > > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.Received on Fri Nov 26 1999 - 10:35:57 CST