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

From: Thomas Kyte <>
Date: 28 Mar 2002 17:57:17 -0800
Message-ID: <>

[Quoted] In article <>, says...

    "decode( sign(length(:LOCATION_PHONE_NUMBER)-20),

              1, NULL, :LOCATION_PHONE_NUMBER)", ....

  if (length(phone-number) > 20 ) then null   else phone-number

put it on one line

>I have the following problem, I want to load data from a file using
>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/'
>into table IMPORT_MIND
>fields terminated by ' '
>I would be grateful for anyhelp.
>thank you in advance
>best regards

Thomas Kyte (    
Expert one on one Oracle, programming techniques and solutions for Oracle.  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Fri Mar 29 2002 - 02:57:17 CET

Original text of this message