Solved

SQL Loader problems with <CR> in LONG fields

Posted on 2004-11-02
1,674 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
Question by:joelhoffman
    15 Comments
     
    LVL 3

    Expert Comment

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

    Author Comment

    by:joelhoffman
    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
    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
    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
    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
    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
    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
    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:
    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
    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
    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
    Or maybe "FIX 580"?

    0
     

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    933 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now