Re: Need help with Importing into LONG with SQL Loader

On Thu, 03 Apr 1997 13:35:41 GMT, "cris brady" <cbrady_at_alpinenet.com> wrote:

I had the same problem. In the end, I attached the empty Oracle table to Access and used an Access append query to populate the Oracle table and let ODBC handle the conversions. Slow, but effective.

Regards
Paul John

>I have an Microsoft Access table that I need to import into Oracle. (7.3)
>I've created routines that create a control file for SQL Loader and
>exports the Access data into an text file.
>
>My problem is the table has one formatted text (memo) field that contains
>carriage returns/linefeeds in the field. So one record of the text file
>will look like this:
>
>field1 = "11111"
>field2 = "22222"
>field3 = "This is field three (cr/lf) this is line two (cr/lf) etc."
>
>The actual exported text file will look like this:
>
>1111122222This is field three
>this is line two
>etc
>
>Now to SQL Loader, this looks like 3 physical records. So I have to
>use CONTINUEIF in the control file to merge field 3 back to one field
>and the 3 lines back to one logical record in Oracle. However the fly
>in the ointment is SQL Loader strips the line feed/carriage returns in
>my 3rd field, resulting in a single long string which is not the
>desired result. The line feeds are part of the original field and need
>to be preserved.
>
>I've tried adding a position holder marker ("~~") for the cr/lf and
>importing
>the text file and then using a SQL procedure to do a REPLACE on the
>marker, but that takes forever.( i.e. 300 records/3mb text file
>SQL Loader import ~ 2min , REPLACE procedure 45 mins and still
>running before I killed it.)
>
>Does anybody have any suggestions? I'm open to anything. Even other
>approaches.
>
>-- Cris
>=====================================================
>mailto:cbrady_at_alpinenet.com software with altitude!
>http://www.alpinenet.com alpine enterprise, ltd.
> Microsoft Solution Provider
> Microsoft Certified Systems Engineer
>
>
Received on Sun Apr 13 1997 - 00:00:00 CDT