Re: Data source for SQLLOADER

From: Marc Weinsock <MWeinstock_at_lds.com>
Date: Thu, 09 Dec 1999 23:21:45 GMT
Message-ID: <38503A61.8ADE5E3A@lds.com>

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
> > @@_dump
> > set line 79
> > -- build a basic control file
> > spool _dtmp.sql
> > select 'spool ' || lower('&dumptable') || '.par' from dual;
> > spool off
> > @@_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
> > @@_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.

--

---

Marc Weinstock
Logical design Solutions

55 Broadway - 21st Floor
New York, New York 10006

Phone: 800.221.5327 x2136
Fax: 212-825-2245
E-Mail: MWeinstock_at_lds.com
Web: http://www.lds.com Received on Thu Dec 09 1999 - 17:21:45 CST