Oracle SQL Loader won't load data while INSERT can (date column, ORA-01858)-CodePudding

I have a simple table

  CREATE TABLE MYTABLE
   (    
    MYDATE DATE,
    ID NUMBER
   )

MYDATE is of default format 'dd-MON-yy'

With matching date format insert works without any problem

INSERT INTO mytable values ('01-JAN-01',1)

As well as SQL Loader

LOAD DATA
INFILE "mytable.dat" "str '#@\n'"
INTO TABLE mytable TRUNCATE
FIELDS TERMINATED BY '&,#' TRAILING NULLCOLS
(MYDATE ,ID)

.dat file

01-JAN-01&,#1#@

Problem comes when date format of inserted date is not default (which I need), then I get ORA-01861: literal does not match format string Thus I use TO_DATE and it works for INSERT

INSERT INTO mytable values (TO_DATE('1901-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),2)

But not for SQL Loader for some reason

.dat file

TO_DATE('1901-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')&,#2#@

Log File

SQL*Loader: Release 19.0.0.0.0 - Production on Fri Sep 16 13:32:33 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Control File:   mytable.ctl
Data File:      mytable.dat
  File processing option string: "str '#@
'"
  Bad File:     mytable.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     250 rows, maximum of 1048576 bytes
Continuation:    none specified
Path used:      Conventional

Table MYTABLE, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MYDATE                              FIRST     *           CHARACTER            
    Terminator string : '&,#'
ID                                  NEXT     *           CHARACTER            
    Terminator string : '&,#'

Record 1: Rejected - Error on table MYTABLE, column MYDATE.
ORA-01858: a non-numeric character was found where a numeric was expected


Table MYTABLE:
  0 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 129000 bytes(250 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Fri Sep 16 13:32:33 2022
Run ended on Fri Sep 16 13:32:33 2022

Elapsed time was:     00:00:00.05
CPU time was:         00:00:00.02

I am not sure if I am just missing some syntax here or if it is some limitation of SQL Loader.

CodePudding user response：

Don't set "correct" date format in the CSV file; SQL*Loader can't recognize it.

Contents of the file should be e.g.

1901-02-01 00:00:00&,#1#@

but you'd then modify control file to

LOAD DATA
INFILE "mytable.dat" "str '#@\n'"
INTO TABLE mytable TRUNCATE
FIELDS TERMINATED BY '&,#' TRAILING NULLCOLS
(MYDATE "to_date(:mydate, 'yyyy-mm-dd hh24:mi:ss')",         --> this
 ID)