SQL Loader problems with <CR> in LONG fields

I am using SQL Loader 8.1.7.0.0 to load a text file containing four fields, one of those four being of the LONG datatype. This LONG field is for comments and often contains Carriage Returns. I've read everything I can in here and at ORACLE to try to KEEP those carriage returns in the data, but not allow them to start new records in ORACLE. I've used CONTINUE IF, I've tried fixed length loads and delimited loads. Nothing I seem to do will prevent the load to start a new record in Oracle wherever there is a carriage return.

Any suggestions?

Here's my basic ctl file using fixed length fields:

LOAD DATA
INFILE      'C:\data\sql\jsht_long_desc.txt'
    BADFILE     'C:\data\sql\badfile.bad'
    DISCARDFILE 'C:\data\sql\discardfile.dsc'
REPLACE INTO
    TABLE rrcfat.WO_LD_TEST_TBL
 
 (WONUM POSITION (1:10), LDKEY POSITION (11:48), REPORTDATE POSITION (49:67) date "mm/dd/YYYY HH24:MI:SS", LDTEXT POSITION (68:579))
joelhoffmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dnarramoreCommented:
We had a similar problem trying to import LONG datatypes, and we ended up not being able to use SQL*Loader.  We went with a product called Data Junction.  But that was about three years ago, maybe there is a better solution now.
0
joelhoffmanAuthor Commented:
dnarramore:

Thanks for the comment. I notice a lot of questions in here that are similar regarding ignoring the <CR> are very old so I'm hoping, as you mentioned, that such an obvious challenge with SQL*Loader would have been resolved by now.
0
MikeOM_DBACommented:
You could try enclosing the LOB in either some text delimiters or Hex character delimiters like this:

LOAD DATA
INFILE      'C:\data\sql\jsht_long_desc.txt'
    BADFILE     'C:\data\sql\badfile.bad'
    DISCARDFILE 'C:\data\sql\discardfile.dsc'
REPLACE INTO
    TABLE rrcfat.WO_LD_TEST_TBL
 ( WONUM POSITION (1:10)
 , LDKEY POSITION (11:48)
 , REPORTDATE POSITION (49:67) date "mm/dd/YYYY HH24:MI:SS"
 , LDTEXT POSITION (68:579) ENCLOSED BY '<startlob>' AND '<endlob>'
 )
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

joelhoffmanAuthor Commented:
MikeOM:

Thanks for the suggestion. I gave it a try as follows:

LOAD DATA
INFILE      'C:\data\sql\jsht_long_desc.txt'
    BADFILE     'C:\data\sql\badfile.bad'
    DISCARDFILE 'C:\data\sql\discardfile.dsc'
REPLACE INTO
    TABLE rrcfat.WO_LD_TEST_TBL
 
 (WONUM POSITION (1:10), LDKEY POSITION (11:48), REPORTDATE POSITION (49:67) date "mm/dd/YYYY HH24:MI:SS", LDTEXT POSITION (68:579) ENCLOSED BY '%%' AND '%%')

When I pulled up the Oracle table after the load, the LONG data was still wrapped after the carriage return into a new record. It sure seemed like that would work but I guess I'm still looking. Thanks again!
0
riazpkCommented:
Please post some sample data as in ur datafile is so that we can play with that in order to answer the question.
0
morphmanCommented:
Can you do a find/replace on all carriage returns, and change them to some other character, and then after load, perform an update on the long fields replacing the character back for a carriage return? I know its not exactly a scientific solution, but depending on your volumes, fairly straight forward.

You may be able to perform the replace on the actual load process by putting an sql filter on the long field being loaded?
0
joelhoffmanAuthor Commented:
riazpk:

I'm now trying delimited fields using "|" as the delimiter - still not working. Here's some sample data:

EH0432552|664837559|9/8/2004 10:39:52|MILEAGE 73692
EH0432544|664837544|9/8/2004 09:56:59|UNIT IS A  L M 228-246-86
EH0432545|664837547|9/8/2004 10:00:33|UNIT IS CURRENTLY DOING 6.09 SPM
EH0432643|664837644|9/8/2004 17:55:23|COMPRESSOR IS LOCATED IN 7R ACROSS FROM 07R 1STV
EH0432618|664837626|9/8/2004 16:06:50|increase spms from 7.4 to 9.0 & check balance (2400)
EH0432591|664837600|9/8/2004 14:02:47|TAKE PICTURE OF NAME PLATE AFTER IT HAS BEEN INSTALLED ON VALVE
EH0432535|664837541|9/8/2004 08:39:28|FROM EXPOSURE TO UNAUTHORIZED EMPLOYEES. LOCATED
IN OLD CONTROL ROOM
EH0432656|664837645|9/9/2004 08:26:24|the totals between the floco and noc match but the oil side sample shows water that the noc is not counting.
EH0432549|664837646|9/8/2004 10:19:02|09-08-2004 KBR MECH-REMOVE CAM GEAR COVER DOORS
INSPECTED GEARS CLEAN DOORS & ENGINE BLOCK.
INSTALLED DOOR GASKETS & REINSTALLED DOORS.
0
joelhoffmanAuthor Commented:
morphman:

I've thought about doing the replace scenario but have not done it yet because of the extra steps. I'll give that a try and let you know. In the meantime, how would I do as you suggest and put "an sql filter on the long field being loaded?"

Thanks for your suggestions!
0
morphmanCommented:
Try this

When using the | delimiter, use the " also

"EH0432545"|"664837547"|"9/8/2004 10:00:33"|"UNIT IS CURRENTLY DOING 6.09 SPM"
"EH0432643"|"664837644"|"9/8/2004 17:55:23"|"COMPRESSOR IS LOCATED IN 7R ACROSS FROM 07R 1STV"
"EH0432618"|"664837626"|"9/8/2004 16:06:50"|"increase spms from 7.4 to 9.0 & check balance (2400)"

and put an optionally encosed by " clause in your ctl file.

what I mean witht he sql filter after doing the search/replace was as simple as follows.

LOAD DATA
INFILE      'C:\data\sql\jsht_long_desc.txt'
    BADFILE     'C:\data\sql\badfile.bad'
    DISCARDFILE 'C:\data\sql\discardfile.dsc'
REPLACE INTO
    TABLE rrcfat.WO_LD_TEST_TBL
 
 (WONUM POSITION (1:10), LDKEY POSITION (11:48), REPORTDATE POSITION (49:67) date "mm/dd/YYYY HH24:MI:SS", LDTEXT POSITION (68:579) "replace(:LDTEXT,':',chr(10)))

assuming you use : as your search/replace character.


You *might* have to a little more work than a simple search/replace for carriage returns, as you will also lose your carriage return at the end of each row. You should be able to handle that though.
0
joelhoffmanAuthor Commented:
morphman:

Cool! I used the find and replace and got the data stripped of carriage returns. I did a find and replace on the Oracle table and put the carriage returns back in perfectly. However, I didn't get the "replace" function to work. Here's where my ctl is at now:

===============================
LOAD DATA
INFILE      'C:\data\sql\jsht_long_desc.txt'
    BADFILE     'C:\data\sql\badfile.bad'
    DISCARDFILE 'C:\data\sql\discardfile.dsc'
REPLACE  
  INTO TABLE rrcfat.WO_LD_TEST_TBL
 fields terminated by '|' TRAILING NULLCOLS
 (WONUM, LDKEY, REPORTDATE date "mm/dd/YYYY HH24:MI:SS", LDTEXT "replace(:LDTEXT,chr(255),chr(10)))

===============================

With that ctl, I get an error: "SQL*Loader-00625 Multibyte character error in control file."

Also: the reason I'm not using 'optionally encosed by "' in my ctl is because the LDTEXT field contains many single and double-quotes. I didn't want to confuse the Loader.
0
morphmanCommented:
Sorry, looks like I missed the last double quotes on the filter.

===============================
LOAD DATA
INFILE      'C:\data\sql\jsht_long_desc.txt'
    BADFILE     'C:\data\sql\badfile.bad'
    DISCARDFILE 'C:\data\sql\discardfile.dsc'
REPLACE  
  INTO TABLE rrcfat.WO_LD_TEST_TBL
 fields terminated by '|' TRAILING NULLCOLS
 (WONUM, LDKEY, REPORTDATE date "mm/dd/YYYY HH24:MI:SS", LDTEXT "replace(:LDTEXT,chr(255),chr(10))")

===============================
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joelhoffmanAuthor Commented:
Awesome! I think I got it. I made the mistake of replacing a <LF> into the Oracle table with "chr(10)" when I meant to put in a <CR> with "chr(13)". So, for posterity, here's my steps:

1. In my data table: Replace all <CR> characters (chr(13)) in my LONG text field with some other character not found in my original data. I used chr(255).
2. Create a delimited text file from my data table using only a pipe, |, as a field terminator
3. Run SQL*Loader with ctl as follows using an SQL filter to put the <CR> back into the text field:

===============================
LOAD DATA
INFILE      'C:\data\sql\jsht_long_desc.txt'
    BADFILE     'C:\data\sql\badfile.bad'
    DISCARDFILE 'C:\data\sql\discardfile.dsc'
REPLACE  
  INTO TABLE rrcfat.WO_LD_TEST_TBL
 fields terminated by '|' TRAILING NULLCOLS
 (WONUM, LDKEY, REPORTDATE date "mm/dd/YYYY HH24:MI:SS", LDTEXT CHAR(5000) "replace(:LDTEXT,chr(255),chr(13))")
===============================

Thanks, morphman! Awesome!
0
MikeOM_DBACommented:
IF your file is "FIXED" length records, then you could also do this:

LOAD DATA
INFILE      'C:\data\sql\jsht_long_desc.txt'  "FIX 579"
    BADFILE     'C:\data\sql\badfile.bad'
    DISCARDFILE 'C:\data\sql\discardfile.dsc'
REPLACE INTO
    TABLE rrcfat.WO_LD_TEST_TBL
 (WONUM POSITION (1:10), LDKEY POSITION (11:48), REPORTDATE POSITION (49:67) date "mm/dd/YYYY HH24:MI:SS", LDTEXT POSITION (68:579))





0
MikeOM_DBACommented:
Or maybe "FIX 580"?

0
joelhoffmanAuthor Commented:
MikeOM:

I put my text file back into fixed length and I tried both "FIX 579" and "FIX 580" and the result was pretty weird. All data from all fields was forced into the LDTEXT field! There were a couple records that wrapped from LDTEXT into WONUM but only a couple - strange stuff!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.