我被困在将 SQL*Loader 与 to_date() 作为我的日期类型字段之一

Question

我正在使用 SQL*Loader 将表从 db2 加载到 oracle DB 使用

LOAD DATA INFILE '<path><File_name>.del' 
replace
into table schema.tableName fields terminated by ','
(
col1,
col2,
col3,....
)
Rejected - Error on table schema_name.table_name, column col3.
ORA-01861: literal does not match format string.

由于 col3 是日期类型，我们需要将其转换为 oracle 可接受的日期格式。 谁能告诉我如何在 sql 加载器中使用 to_date((？

Answer 1

下面是一个示例：

我的测试表，它将保存输入数据：

SQL> desc test
Name                                      Null?    Type
----------------------------------------- -------- -------------------
ID                                                 NUMBER
DATUM                                              DATE
SQL>

控制文件;注释

• to_date函数调用(双引号，正确的日期格式掩码，必须与您正在加载的数据格式匹配(

• 第 3 行，其格式为"无效">

  load data 
  infile *
  replace
  into table test
  fields terminated by ','
  trailing nullcols
  ( 
  id,
  datum "to_date(:datum, 'yyyy-dd-mm')"
  )
  begindata
  1,2020-20-05
  2,2020-28-12
  3,20200215
  

加载会话：

SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
SQL> $sqlldr scott/tiger control=test20.ctl log=test20.log
SQL*Loader: Release 11.2.0.2.0 - Production on Sri Svi 20 08:10:53 2020
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 2
Commit point reached - logical record count 3
SQL> select * From test;
ID DATUM
---------- ----------
1 20.05.2020
2 28.12.2020
SQL>

如您所见，第 3 行未加载。日志文件内容。请注意ORA-01861错误：

Table TEST, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   ,       CHARACTER            
DATUM                                NEXT     *   ,       CHARACTER            
SQL string for column : "to_date(:datum, 'yyyy-dd-mm')"
Record 3: Rejected - Error on table TEST, column DATUM.
ORA-01861: literal does not match format string

Table TEST:
2 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.

---

因此：确保所有输入数据都遵循相同的格式掩码。