sql*loader delemited fields , if length(field1)>20 then field1=null

From: Soazig Forterre <soazig.forterre_at_laposte.net>
Date: 28 Mar 2002 02:27:10 -0800
Message-ID: <8fa56fe5.0203280227.17ede8ea_at_posting.google.com>

Hello,
[Quoted] I have the following problem, I want to load data from a file using sql*loader.
The file is tab delimited.
There is a field LOCATION_PHONE_NUMBER, which in the file could exceed 20 characters, in my table it is 20 characters long VARCHAR2(20). I want to put null on the field when in the file the values exceed 20 characters long, and leave the field value otherwise. I don't know how to do that, can you help me, please.

I try this on the control file but it doesn't work OPTIONS (skip = 2)
load data
infile '$HOME/rit/mind.in'
truncate
into table IMPORT_MIND
fields terminated by ' '
(
LOCATION_ID,

COMPANY_ID,
COMPANY_FULL_NAME,
COMPANY_SHORT_NAME,
LOCATION_FULL_NAME,
LOCATION_SHORT_NAME,
LOCATION_ADDRESS1,
LOCATION_ADDRESS2,
LOCATION_ADDRESS3,
LOCATION_POSTAL_CODE,
LOCATION_TOWN,
LOCATION_COUNTRY_CODE,
LOCATION_COUNTRY_NAME,

LOCATION_SO2_CODE,
LOCATION_SO2_NAME,

CHANNEL_SEGMENT_CODE,
CHANNEL_SEGMENT_DESCRIPTION,
CHANNEL_SUBSEGMENT_CODE,
CHANNEL_SUBSEGMENT_DESCRIPTION,
LOCATION_PHONE_NUMBER NULLIF (length(:LOCATION_PHONE_NUMBER) > 20),
LOCATION_PHONE_AREA_CODE,
LOCATION_FAX_NUMBER ,

LOCATION_FAX_AREA_CODE,
LOCATION_EMAIL,
HEADQUARTER_FLAG,

LOCATION_URL,
LOCATION_TAXID,
CREATION_DATE,

LAST_UPDATE_DATE,
RECORD_STATUS
)

I would be grateful for anyhelp.

thank you in advance
best regards
Soazig Received on Thu Mar 28 2002 - 11:27:10 CET