[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Loader problems with <CR> in LONG fields

Posted on 2004-11-02
15
Medium Priority
?
1,720 Views
Last Modified: 2011-04-14
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))
0
Comment
Question by:joelhoffman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +2
15 Comments
 
LVL 3

Expert Comment

by:dnarramore
ID: 12475310
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
 

Author Comment

by:joelhoffman
ID: 12475403
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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 80 total points
ID: 12475619
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:joelhoffman
ID: 12476250
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
 
LVL 13

Expert Comment

by:riazpk
ID: 12481101
Please post some sample data as in ur datafile is so that we can play with that in order to answer the question.
0
 
LVL 6

Assisted Solution

by:morphman
morphman earned 1920 total points
ID: 12481534
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
 

Author Comment

by:joelhoffman
ID: 12484311
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
 

Author Comment

by:joelhoffman
ID: 12484332
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
 
LVL 6

Expert Comment

by:morphman
ID: 12484463
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
 

Author Comment

by:joelhoffman
ID: 12485009
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
 
LVL 6

Accepted Solution

by:
morphman earned 1920 total points
ID: 12485063
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
 

Author Comment

by:joelhoffman
ID: 12485700
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 12485935
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 12485942
Or maybe "FIX 580"?

0
 

Author Comment

by:joelhoffman
ID: 12486246
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question