Re: need help with sql*loader problem

From: Shawn Ferris <shawn_at_virtualsmf.net>
Date: Wed, 26 May 2004 13:22:02 -0600 (MDT)
Message-ID: <39397.168.215.22.23.1085599322.squirrel@mail.virtualsmf.net>

> this may be slow.... but could you make an external table? and use pl/sql
> to insert it into a table with code? sqlloader is not very robust.

I used to think the same about sqlldr.. but actually it's extremely robust.. csv is the problem. If you can define your delimiters better, sqlldr should have no problems loading it.

I posted a perl script a week or so ago to extract data from oracle into a file suitable for sqlldr. With a few minor modifications, it should work on windows (using activestate perl) and connecting to an ODBC datasource. (IE: Your access database)

This would build you a suitable file for sqlldr. If there is enough interest, I'll test it out and send the resulting script.

Of course, this is all theoretical.. (I have done enough DBD::ODBC on active state, that I don't think this would be a problem)

Shawn

PS.. the control file I used would include:

infile FILE.dat "str '<eorecord>'"
fields terminated by ',' optionally enclosed by '<boc>' and '<eoc>'

and the data would look something like:

<boc>1<eoc>,<boc>Foo<eoc>,<eorecord>
<boc>2<eoc>,<boc>Bar<eoc>,<eorecord>
<boc>3<eoc>,<boc>Baz<eoc>,

This would accomodate embedded quotes, newlines, etc, within the data. The only problems you'd likely run into, is if my delimeters were found in the data. (<boc>, <eoc> and <eorecord> -- Highly doubtful)

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed May 26 2004 - 14:10:01 CDT