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.
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)