Re: Data source for SQLLOADER

[Quoted] I tried using your script with Oracle 8 butr it did not work. It could not write to the _dump.sql file. What version of oracle did you write this for.

Yass Khogaly wrote:

> Hi,
>
> You can use scripts to output your data from an Oracle table to a flat file
> :
>
> --
> -- dump a table to a comma delimited ASCII file
> -- only drawback is line length is likely to be padded with
> -- quite a few spaces if the 'set trimspool on' option is
> -- not in your version of SQLPLUS
> --
> -- also builds a control file and a parameter file for SQL*LOADER
>
> 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;
> /
> print linelen
> print maxcol
> spool ./_dump.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 '/' from dual;
> select 'spool off' from dual;
> spool off
> _at_@_dump
> set line 79
> -- build a basic control file
> spool _dtmp.sql
> select 'spool ' || lower('&dumptable') || '.par' from dual;
> spool off
> _at_@_dtmp
>
> select 'userid = /' || chr(10) ||
> 'control = ' || lower('&dumptable') || '.ctl' || chr(10) ||
> 'log = ' || lower('&dumptable') || '.log' || chr(10) ||
> 'bad = ' || lower('&dumptable')|| '.bad' || chr(10)
> from dual;
> spool _dtmp.sql
> select 'spool ' || lower('&dumptable') || '.ctl' from dual;
> spool off
> _at_@_dtmp
> select 'load data' || chr(10) ||
> 'infile ' || ''''|| lower('&dumptable') || '.txt' || '''' ||
> chr(10) ||
> 'into table &dumptable' || chr(10) ||
> 'fields terminated by ' || '''' || ',' || '''' ||
> 'optionally enclosed by ' || '''' || '"' || '''' || chr(10)
> from dual;
> select '(' from dual;
> select ' ' || column_name || ',' ,
> 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 Thu Dec 09 1999 - 22:30:30 CET