Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    4

    Question Unanswered: Problem with SQLLoader

    System Info - HP-UX v11, Oracle 9.2.0.4.0

    I receive the following error when attempting to load a pipe-delimited flat file that has a 500 character field into a table that has a VARCHAR2(500) defined column for it.

    Record 1: Rejected - Error on table CPP_STAGE.STG_F1005S0_DG_AUDIT, column D1005_AUDIT_RECORD_IMAGE_DSC.
    Field in data file exceeds maximum length

    When I modify the table to add four more VARCHAR(500) fields and then insert pipe characters at the end of each line using vi, it loads successfully. The same is true if I don't add the additional columns at the end of the table after "piping" the file. Any ideas how this can be resolved?

  2. #2
    Join Date
    May 2006
    Posts
    132
    You may want to specify CHAR(500) for this column in the control file.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    add TRAILING NULLCOLS and please post your controlfile.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2006
    Posts
    4

    Here's the Control File

    Hi,

    The control file (below) contains the "trailing nullcols" statement, and I've also tried redefining the table column as CHAR(500), all to no avail.


    load data
    infile '/apps/cpp/polling/DBSNH/stg_f1005s0_dg_audit'
    badfile '/apps/cpp/polling/DBSNH/bad/stg_f1005s0_dg_audit'
    into table cpp_stage.stg_f1005s0_dg_audit partition (DBSNH)
    truncate
    fields terminated by '|'
    trailing nullcols



    (SOURCE_PLAN,
    RECORD_ID "(TRIM(:RECORD_ID))",
    P1005_AUDIT_OPID_NBR "(TRIM(:P1005_AUDIT_OPID_NBR))",
    P1005_AUDIT_TIME "(TRIM(:P1005_AUDIT_TIME))",
    P1005_AUDIT_DATE "(TRIM(:P1005_AUDIT_DATE))",
    D1005_DATE_STMP,
    D1005_TIME_STMP,
    D1005_AUDIT_OPID_NBR "(TRIM(1005_AUDIT_OPID_NBR))",
    D1005_AUDIT_TIME "(TRIM(1005_AUDIT_TIME))",
    D1005_AUDIT_DATE "(TRIM(1005_AUDIT_DATE))",
    D1005_AUDIT_FUNCTION_NAME "(TRIM(1005_AUDIT_FUNCTION_NAME))",
    D1005_AUDIT_FUNCTION_MODE_CODE "(TRIM(1005_AUDIT_FUNCTION_MODE_CODE))",
    D1005_AUDIT_RECORD_TYPE_CODE "(TRIM(1005_AUDIT_RECORD_TYPE_CODE))",
    D1005_AUDIT_IMAGE_TYPE_IND "(TRIM(1005_AUDIT_IMAGE_TYPE_IND))",
    D1005_AUDIT_SEQUENCER_VALUE,
    D1005_AUDIT_RECORD_IMAGE_DSC "(TRIM(1005_AUDIT_IMAGE_TYPE_IND))")

  5. #5
    Join Date
    May 2006
    Posts
    132

    Talking

    I think it's due to the in your control file. Just kidding !!

    Actually, I meant to define CHAR(500) in your controlfile itself for this particular column, the table can stay VARHCAR2(500).

    For example,

    Code:
    D1005_AUDIT_RECORD_IMAGE_DSC CHAR(500) "(TRIM(:D1005_AUDIT_IMAGE_TYPE_IND))"
    Last edited by ebrian; 07-11-06 at 14:02.

  6. #6
    Join Date
    Jul 2006
    Posts
    4
    Well I'll be dipped in dogs--t! The first pass I left in the TRIM statement and it "worked" - the row loaded but the CHAR(500) was trimmed to a mere shadow of its former flat file self. When I removed the TRIM statement, the data loaded fine. Thanks!!

    p.s. Interesting how pasting in the ctl file from a Putty window turned all of the internal (colon)D1005s into s

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, you should have posted using the "Post reply" button instead of "Post Quick Reply"; in there, there's a checkbox which says "Disable smilies in text"

  8. #8
    Join Date
    Jul 2006
    Posts
    4
    C'est la vie. Such is the curse of being the new kid on the block!

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, believe me, curse of the Black Pearl is much more terrifying

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •