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

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 28 Mar 2002 17:57:17 -0800
Message-ID: <a80hlt0hve_at_drn.newsguy.com>


[Quoted] In article <8fa56fe5.0203280227.17ede8ea_at_posting.google.com>, soazig.forterre_at_laposte.net says...

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

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

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

put it on one line

>
>Hello,
>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

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
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