Oracle SQL Loader 不会加载数据，而 INSERT 可以(日期列，ORA-01858)

Question

我有一个简单的表

CREATE TABLE MYTABLE
(    
MYDATE DATE,
ID NUMBER
)

MYDATE的默认格式为'dd-MON-yy'

与匹配的日期格式插入工作没有任何问题

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

以及SQL加载器

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

.dat文件

01-JAN-01&,#1#@

当插入日期的日期格式不是默认的(我需要(时，问题就来了，然后我得到ORA-01861: literal does not match format string。因此我使用TO_date，它适用于INSERT

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

但由于某种原因，不适用于SQL Loader

.dat文件

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

日志文件

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

我不确定是这里缺少了一些语法，还是SQL Loader的某些限制。

Answer 1

不要设置"正确的"；CSV文件中的日期格式；SQL*加载器无法识别它。

文件内容应为例如

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

但是你会把控制文件修改成

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)