Loading cr/lf into LONG field

I'm in the situation of taking data out of a Sybase database and loading it into Oracle. My method of doing this was to use Sybase bulk copy (bcp) to extract the data into a delimited file, then use SQL*Loader to load it into Oracle. (The table structures are the same in both).

For the most part, this worked fine. I ran into problems with a table that has as its last column a field of type LONG, which contains ascii text with embedded carriage returns/linefeeds. It's a free-form, variable length description field, and I need to retain the paragraph spacing.

SQL*Loader apparently can't handle these embedded carriage returns - it either interprets the carriage return as end-of-record, or, if using CONTINUEIF, it strips the carriage return/linefeed so I end up with one long string of characters. The solution from the Oracle support hotline is to use the VAR processing option on the INFILE parameter (unix only), but that means I have to count the number of characters on each logical record (variable length, I'll have to do each individually!) and insert that count at the beginning of each logical record. Since I'm potentially dealing with a couple of hundred logical records, made up of several thousand physical records, this seems a very error-prone, time-consuming workaround.

Anybody have a better way of getting ascii text with embedded carriage returns into an Oracle LONG column? I'm actually thinking that creating INSERT statements is a faster way than the workaround I got from Oracle. At least then I'm making repetitive changes that I can do in a more bulk manner using vi - the major pain there is that a totally blank line is interpreted by SQL*Plus as cancelling the statement. I have to edit those lines to append chr(13) and chr(10) to get around that.

I can't be the first or only person to do this...there must be a better way!

Thanks for any suggestions,
Terri McGuire
Hewlett-Packard
terri_mcguire_at_hp.com Received on Wed Sep 10 1997 - 00:00:00 CEST