Re: Using SQLLDR to load CLOB data

Jeffrey.D.Furlong_at_irs.gov wrote:
> My department is starting a conversion from Informix to Oracle.
> I am in the process of loading the unloaded data from Informix into
> our Oracle database using SQLLDR. I have been able to load all of
> our tables but one. Here is what the table schema looks like:
>
> SQL> desc letter_bdy
> Name Null? Type
> ----------------------------------------- -------- ---------------
> PID NOT NULL NUMBER(38)
> LETTER NOT NULL CHAR(10)
> REVISE_DT NOT NULL DATE
> SEL_ORDER NOT NULL NUMBER(38)
> SEL NOT NULL CHAR(5)
> ATTACHMENT NOT NULL NUMBER(38)
> REQUIRED NOT NULL NUMBER(38)
> NOTES CLOB
> PARAGRAPH NOT NULL CLOB
> USERNAME NOT NULL CHAR(10)
> CREATE_DT NOT NULL TIMESTAMP(6)
>
>
> My .dat file data looks something like this:
>
> 1771|86C|08/04/2003|18|Q|0|0|In fill-ins 41 use "correspondence",^M\
> "inquiry", "return", "claim", etc.|We're sending your [41 14V] to
> the Bureau of Alcohol, Tobacco ^M\
> and Firearms, Tax Processing Center, P.O. Box 145433, ^M\
> Cincinnati, OH 45250-5433 because:|abcdef11|2004-02-26
> 11:20:38|
> 1772|86C|08/04/2003|19|R|0|0||We're sending your Form W-4, Employee's
> Withholding Allowance ^M\
> Certificate, to the Internal Revenue Service office at the^M\
> address shown at the end of this letter
> because:|abcdef11|2004-02-26 11:22:01|
> 1773|86C|08/04/2003|20|S|0|0|In fill-in 42 use "correspondence",^M\
> "inquiry", "return", "claim", etc.|We sent your [42 12V] return to
> that office on [43 13D].|abcdef11|2004-02-26 11:2
> 2:43|
> 1774|86C|08/04/2003|21|T|0|0||Your account and records are kept at that
> office. |abcdef11|2004-02-26 11:24:17|
> 1775|86C|08/04/2003|22|U|0|0||The Philadelphia Submission Processing
> Center processes returns of^M\
> U.S. citizens with foreign addresses or returns claiming an
> ^M\
> exclusion from foreign earned income. Since you didn't file
> your ^M\
> return with Philadelphia, we will forward it for you.
> However, this ^M\
> will cause a delay in filing and Philadelphia may receive
> your^M\
> return late. |abcdef11|2004-02-26 11:24:48|
>
> Here is a copy of my control file:
>
> LOAD DATA
> INFILE '86ctest.dat'
> APPEND
> INTO TABLE letter_bdy
> FIELDS TERMINATED BY "|"
> ( pid,
> letter,
> revise_dt DATE(11) "mm/dd/yyyy",
> sel_order,
> sel,
> attachment,
> required,
> notes CHAR(255) NULLIF (notes=BLANKS),
> paragraph CHAR(4000),
> username,
> create_dt DATE "yyyy-mm-dd hh24:mi:ss"
> )
>
> I am getting these errors from my .log file:
>
>
>
> SQL*Loader: Release 10.1.0.2.0 - Production on Fri Sep 23 12:41:29 2005
>
> Copyright (c) 1982, 2004, Oracle. All rights reserved.
>
> Control File: 86cbody2.ctl
> Data File: 86ctest.dat
> Bad File: 86ctest.bad
> Discard File: none specified
>
> (Allow all discards)
>
> Number to load: ALL
> Number to skip: 0
> Errors allowed: 50
> Bind array: 64 rows, maximum of 256000 bytes
> Continuation: none specified
> Path used: Conventional
>
> Table LTR_BODY, loaded from every logical record.
> Insert option in effect for this table: APPEND
>
> Column Name Position Len Term Encl Datatype
> ------------------------------ ---------- ----- ---- ----
> ---------------------
> PID FIRST * | CHARACTER
> LETTER NEXT * | CHARACTER
> REVISE_DT NEXT 11 | DATE
> mm/dd/yyyy
> SEL_ORDER NEXT * | CHARACTER
> SEL NEXT * | CHARACTER
> ATTACHMENT NEXT * | CHARACTER
> REQUIRED NEXT * | CHARACTER
> NOTES NEXT 255 | CHARACTER
> NULL if NOTES = BLANKS
> PARAGRAPH NEXT 4000 | CHARACTER
> USERNAME NEXT * | CHARACTER
> CREATE_DT NEXT * | DATE
> yyyy-mm-dd hh24:m
> i:ss
>
> value used for ROWS parameter changed from 64 to 40
> Record 1: Rejected - Error on table LTR_BODY, column PARAGRAPH.
> Column not found before end of logical record (use TRAILING NULLCOLS)
> Record 2: Rejected - Error on table LTR_BODY, column REVISE_DT.
> Column not found before end of logical record (use TRAILING NULLCOLS)
> Record 3: Rejected - Error on table LTR_BODY, column LETTER.
> Column not found before end of logical record (use TRAILING NULLCOLS)
> Record 4: Rejected - Error on table LTR_BODY, column REVISE_DT.
> Field in data file exceeds maximum length
> Record 5: Rejected - Error on table LTR_BODY, column USERNAME.
> Column not found before end of logical record (use TRAILING NULLCOLS)
> Record 6: Rejected - Error on table LTR_BODY, column LETTER.
> Column not found before end of logical record (use TRAILING NULLCOLS)
> Record 7: Rejected - Error on table LTR_BODY, column REVISE_DT.
> Field in data file exceeds maximum length
>
> I am new at using Oracle and SQLLDR. What am I doing wrong?
>
> Thank you in advance.
>
> Sincerly,
> JD
>

I think sqlldr is getting confused by the control-m characters within the CLOB data, and interpreting it as end-of-record.

I think your options are (pick one):

Convert the control-m characters in the file to something else, at least temporarily, and fixup after loading.

Have sqlldr read the file in "streams" format (although you may need some other way of signalling end-or-record).

Use one of the documented approaches to reading CLOBS (trailing key phrase, varchar length/content pair, fully padded field).

--Peter Received on Mon Sep 26 2005 - 20:39:31 CEST