SQL*Loader question
From: Miguel Camba <mcambab_at_100mbps.es>
Date: Mon, 26 Feb 2001 19:24:24 +0100
Message-ID: <3A9A9F58.B522F94D_at_100mbps.es>
DOCUMENTO POSITION(56:105) CHAR "UPPER(:DOCUMENTO)") SQL*Loader work fine.
Date: Mon, 26 Feb 2001 19:24:24 +0100
Message-ID: <3A9A9F58.B522F94D_at_100mbps.es>
Let this fixed-length report file
|Objeto |Fe.contab. |NÂșdoc.ref. | Val/Mon.so.CO|Texto
cab.documento | ----------------------------------------------------------------------------------------------------------
|PW.70010003 |30.01.2001|31400185 |
122.000,76-|GA2736/01 |
|PW.70010005 |16.01.2001|31640630 |
169.000-|AC2000/01170W |
|PW.70010009 |31.01.2001|34223553 | 55.660
|G.4343/01 |
|PW.70050006 |18.01.2001|31641241 | 7.800
|AC2001/01530W |
----------------------------------------------------------------------------------------------------------
:
where Fe.contab field is a date in spanish format and Val/Mon.so.CO is a quantity in this form:
fix lenght field: 15 bytes
sign: - in byte 15 (negative) or space (positive)
decimal part (optional): 2 bytes
"," is decimal separator
"." is thousands separator
Using SQL*Loader, I need to load that file in an Oracle table like this:
INVERSION NOT NULL VARCHAR2(7) APUNTE NOT NULL VARCHAR2(8) FECHA NOT NULL DATE IMPORTE NOT NULL NUMBER(15,2) DOCUMENTO VARCHAR2(20)
I use this control file:
LOAD DATA
INFILE LOAD.DAT
APPEND
INTO TABLE TEST
WHEN (2) != '-' AND (2) != 'O'
(INVERSION POSITION(6:12) CHAR, FECHA POSITION(18:27) DATE 'DD.MM.YYYY', APUNTE POSITION(29:36) CHAR, IMPORTE POSITION(40:54) DECIMAL EXTERNAL "DECODE(SUBSTR(:IMPORTE, 15,1), '-', -1, 1) * TRANSLATE(REPLACE(SUBSTR(:IMPORTE, 1, 14), '.'), ',', '.')",
DOCUMENTO POSITION(56:105) CHAR "UPPER(:DOCUMENTO)") SQL*Loader work fine.
It's possible load data with a simplified control file? Received on Mon Feb 26 2001 - 19:24:24 CET