SQL*Loader question

From: Miguel Camba <mcambab_at_100mbps.es>
Date: Mon, 26 Feb 2001 19:24:24 +0100
Message-ID: <3A9A9F58.B522F94D@100mbps.es>

Let this fixed-length report file

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 - 12:24:24 CST