Re: Unix Data Export Script for SQL Loader?

Don't Bother wrote:

> Anyone have a Unix script that extracts data from tables into a file
> that can be used with SQL Loader? I would like one that uses ltrim or
> rtrim so my fields have all leading or trailing blanks left out.
>
> Any help is much appreciated.
>
> DavidK
>
> Sent via Deja.com
> http://www.deja.com/

David,

You can use a select statement with separators included and spool that to a textfile so you can use it as a datafile for SQL-loader. An example might help:

I have a simple table TEST which looks like this:

NAME        NUM
a           1
b           2
c           3

If you use a select statement like

SELECT ltrim(rtrim(name))||','||num from test

the output will look like

a,1
b,2
c,3

and that can be used by SQL-loader as a datafile. You can automate this by starting up SQL+ like

SQLPLUS @extract

via crontab. The script could look something like this:

set heading off
set line 9999

spool david.txt
SELECT ltrim(rtrim(name))||','||num from test

spool off

You can also add SQL-loader to your crontab and it'll run while you're out having a beer. Ain't that good news?

Hope this helps a bit,

Marc Received on Wed Dec 20 2000 - 05:46:03 CST