does not load data through sqlloader

Question

I have a table in the oracle database: TABLESPACES and, columns DATA_TIME is type: date. And a data file that wants to patch like this:

    ALTAIR          ;08-01-2019 10:33:28       ;SUB_DATA
    ALTAIR          ;08-01-2019 10:33:28       ;SUB_IDX
    ALTAIR          ;08-01-2019 10:33:28       ;SYSAUX

my control file looks like:

    load data
    infile '/home/oracle/space_monitor/logs/space_monitor.log'
    INTO TABLE TABLESPACES
    APPEND
    Fields terminated by ";"
    (
    DB "TRIM(:DB)",
    DATA_TIME "TRIM(:DATA_TIME)",
    NAME "TRIM(:NAME)"
    )

when trying to load data i have error:

    Record 1: Rejected - Error on table TABLESPACES, column DATA_TIME.
    ORA-01843: not a valid month

when I make changes to the control with TIMESTAMP file like this:

    load data
    infile '/home/oracle/space_monitor/logs/space_monitor.log'
    INTO TABLE TABLESPACES
    APPEND
    Fields terminated by ";"
    (
    DB "TRIM(:DB)",
    DATA_TIME "to_date(:TIMESTAMP, 'DD-MM-YYYY HH24:MI:SS')",
    NAME "TRIM(:NAME)"
    )

i have error:

    SQL*Loader-291: Invalid bind variable :TIMESTAMP in SQL string for column DATA_TIME.

In my database oracle parameters:

    NLS_DATE_FORMAT like RR/MM/DD
    NLS_TIMESTAMP_FORMAT like RR/MM/DD HH24:MI:SSXFF

Please help me.

Answer 1

You have to use on of these sqlloder function (DATE fmt or TIMESTAMP fmt). Trim will be done automagicly

(
DB "TRIM(:DB)",
DATA_TIME  timestamp "DD-MM-YYYY HH24:MI:SS",
NAME "TRIM(:NAME)"
)