+ Reply to Thread
Results 1 to 2 of 2

Using SQLLDR to load CLOB data

  1. Using SQLLDR to load CLOB data

    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


  2. Re: Using SQLLDR to load CLOB data

    Jeffrey.D.Furlong@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

+ Reply to Thread