Insert actual date time in a row with SQL*loader

Question

I have a Oracle SQL*Loader. I need to insert lots of records in a Oracle Database. Here is my .ctl file:

OPTIONS (DIRECT=TRUE)                           
     UNRECOVERABLE                          
     LOAD DATA                                       
     CHARACTERSET WE8EBCDIC500                       
     infile * "fix 12"                             
     APPEND                                          
     INTO TABLE  IPERCAI1.TDE_MIDINERO            
     (                                               
        ACTIVA  POSITION(1:20) VARCHAR2(20),         
        TIPUSACT POSITION(21:21) VARCHAR2(1),        
        IMPREINT POSITION(22:32) NUMBER(11,2),
        NUMREINT POSITION(33:37) NUMBER(5),
        REINTEGROS_MES1 POSITION(38,48) NUMBER(11,2),
        REINTEGROS_MES2 POSITION(49,59) NUMBER(11,2),
        REINTEGROS_MES3 POSITION(60,70) NUMBER(11,2),
        REINTEGROS_MES4 POSITION(71,81) NUMBER(11,2),
        REINTEGROS_MES5 POSITION(82,92) NUMBER(11,2),
        REINTEGROS_MES6 POSITION(93,103) NUMBER(11,2),
        REINTEGROS_MES7 POSITION(104,114) NUMBER(11,2),
        REINTEGROS_MES8 POSITION(115,125) NUMBER(11,2),
        REINTEGROS_MES9 POSITION(126,136) NUMBER(11,2),
        REINTEGROS_MES10 POSITION(137,147) NUMBER(11,2),
        REINTEGROS_MES12 POSITION(148,158) NUMBER(11,2),
        ACUMULADO POSITION(159,171) NUMBER(13,2),
        IMPMAX POSITION(172,184) NUMBER(13,2),
        IMPLIMIT  POSITION(185,197) NUMBER(13,2),
        CAJERO POSITION(198,202) NUMBER(5),
        DATUDIA POSITION(203,211) NUMBER(9),
        DATUMES POSITION(212,220) NUMBER(9)
        )

I need to add a new field named DATULT. This field has to be the actual date time, and I can't get the value from the file. I have to calculate it. Can anybody tell me how can I add this field in my .ctl file? This field has to be a DATE type in Oracle

Thank you in advance!!

Please explain how this is related to Spring or Spring-Batch. — FGreg, Oct 09 '13 at 16:13
You mean it has to be the system date the data was loaded on, i.e. the sysdate? — Ben, Oct 09 '13 at 17:11
Ben, I have to insert a new field with the sysdate, but I don't know how can I do this. I'm new in Spring-batch. Thank you — eloweyn, Oct 10 '13 at 07:01
SQL*Loader is an Oracle product that has nothing to do with Spring-Batch. As your question stands it seems you are asking how to tell SQL*Loader to use `sysdate` for this new `DATULT` column which does not concern Spring-Batch. If that is not truly your problem, please edit your question. — FGreg, Oct 10 '13 at 17:29
For that matter, this might answer your question: http://stackoverflow.com/questions/1233254/how-to-populate-a-timestamp-field-with-current-timestamp-using-oracle-sql-loader?rq=1 — FGreg, Oct 10 '13 at 17:35

score 0 · Accepted Answer · answered Oct 25 '13 at 08:52

This is the solution:

OPTIONS (DIRECT=FALSE)
LOAD DATA
INFILE *
BADFILE *                                                          
REPLACE                                       
INTO TABLE IPERCAI1.TDE_MIDINERO            
(                                               
    ACTIVA  POSITION(1:20) CHAR(20),         
    TIPUSACT POSITION(21:21) CHAR(1),        
    IMPREINT POSITION(22:32) DECIMAL EXTERNAL(11) ":IMPREINT/100",
    NUMREINT POSITION(33:37) INTEGER EXTERNAL,
    REINTEGROS_MES1 POSITION(38:48) DECIMAL EXTERNAL(11) ":REINTEGROS_MES1/100",
    REINTEGROS_MES2 POSITION(49:59) DECIMAL EXTERNAL(11) ":REINTEGROS_MES2/100",
    REINTEGROS_MES3 POSITION(60:70) DECIMAL EXTERNAL(11) ":REINTEGROS_MES3/100",
    REINTEGROS_MES4 POSITION(71:81) DECIMAL EXTERNAL(11) ":REINTEGROS_MES4/100",
    REINTEGROS_MES5 POSITION(82:92) DECIMAL EXTERNAL(11) ":REINTEGROS_MES5/100",
    REINTEGROS_MES6 POSITION(93:103) DECIMAL EXTERNAL(11) ":REINTEGROS_MES6/100",
    REINTEGROS_MES7 POSITION(104:114) DECIMAL EXTERNAL(11) ":REINTEGROS_MES7/100",
    REINTEGROS_MES8 POSITION(115:125) DECIMAL EXTERNAL(11) ":REINTEGROS_MES8/100",
    REINTEGROS_MES9 POSITION(126:136) DECIMAL EXTERNAL(11) ":REINTEGROS_MES9/100",
    REINTEGROS_MES10 POSITION(137:147) DECIMAL EXTERNAL(11) ":REINTEGROS_MES10/100",
    REINTEGROS_MES11 POSITION(148:158) DECIMAL EXTERNAL(11) ":REINTEGROS_MES11/100",
    REINTEGROS_MES12 POSITION(159:169) DECIMAL EXTERNAL(11) ":REINTEGROS_MES12/100",
    ACUMULADO POSITION(170:182) DECIMAL EXTERNAL(13) ":ACUMULADO/100",
    IMPMAX POSITION(183:195) DECIMAL EXTERNAL(13) ":IMPMAX/100",
    IMPLIMIT POSITION(196:208) DECIMAL EXTERNAL(13) ":IMPLIMIT/100",
    DATULTM sysdate,
    CAJERO POSITION(217:221) INTEGER EXTERNAL,
    DATUDIA POSITION(222:229) INTEGER EXTERNAL,
    DATUMES POSITION(230:237) INTEGER EXTERNAL
)

Insert actual date time in a row with SQL*loader

1 Answers1