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

c9qzyr3d  于 2022-09-18  发布在  Java
关注(0)|答案(1)|浏览(130)

我有一张简单的table

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的某些限制。

f0brbegy

f0brbegy1#

不要在CSV文件中设置“正确的”日期格式;SQL*Loader无法识别它。

文件的内容应为例如

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)

相关问题