Re: Data source for SQLLOADER

I agree, TOAD rocks :)

~bob :)

Marc Weinsock wrote:
> U can use many tools, a good tool is toad for exporting and many, many other
> things, www.toadsoft.com
>
> Pete Shankey wrote:
>
> > 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?
Received on Sat Dec 11 1999 - 03:15:53 CET