Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Loader problems with <CR> in LONG fields

Posted on 2004-11-02
15
Medium Priority
?
1,732 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses

581 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