Using SQLLDR to load CLOB data

From: <Jeffrey.D.Furlong_at_irs.gov>
Date: 23 Sep 2005 13:58:34 -0700
Message-ID: <1127509114.034085.28340_at_o13g2000cwo.googlegroups.com>

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\

[Quoted] "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\

[Quoted] "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 Received on Fri Sep 23 2005 - 22:58:34 CEST