SQL*Loader and excess field length in CSV input

From: Ian Jones <ianj_at_gene.com>
Date: 5 Dec 2002 15:06:28 -0800
Message-ID: <5b9fed14.0212051506.28ec9338_at_posting.google.com>

I want to be able to limit my load to the first 4000 characters for one field. I thought my control file syntax would apply substr to the field in the input file, but it's not working.

Here's the control file:

LOAD DATA
INFILE '/home/oz/sds_share/data/cact.csv' TRUNCATE
INTO TABLE corrective_actions
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (ca_number,
date_initiated DATE "mm/dd/rr" NULLIF date_initiated=BLANKS, status,
completion_date DATE "mm/dd/rr" NULLIF completion_date=BLANKS, due_date DATE "mm/dd/rr" NULLIF due_date=BLANKS, assigned_to,
cost_center,
description char(4000),
justification char(4000),
documentation_code,
category,
category_code,
completed_by,
conclusion char(4000) "substr(:conclusion,1,4000)", site)

when I run this, the substring doesn't actually truncate the input field. Could someone please explain how I might be able to do this? I'm getting two record errors.

Here's an excerpt from the log file. Thanks!

CONCLUSION                           NEXT  4000   ,  O(") CHARACTER
    SQL string for column : "substr(:conclusion,1,4000)"
SITE                                 NEXT     *   ,  O(") CHARACTER

Record 835: Rejected - Error on table CORRECTIVE_ACTIONS, column CONCLUSION.
Field in data file exceeds maximum length Record 2360: Rejected - Error on table CORRECTIVE_ACTIONS, column CONCLUSION.
Field in data file exceeds maximum length Received on Fri Dec 06 2002 - 00:06:28 CET