时间戳

首页 > 解决方案 > Sqlldr 日志在面对 ORA-01722 时显示错误的列名

oracle - Sqlldr 日志在面对 ORA-01722 时显示错误的列名

问题描述

我有一个三列的简单表格。

表名 DUMMY20210527

desc DUMMY20210527

PID        NUMBER(10) 
VDATE      DATE       
SID        NUMBER(10)

控制文件

LOAD DATA CHARACTERSET JA16SJIS
APPEND INTO TABLE "DUMMY20210527" fields terminated by '\t' trailing nullcols
(
pid ,
vdate date "YYYY/MM/DD",
sid
)

数据文件

a   b   c
1   2019/01/10  X

尝试加载此文件时，日志在 vdate 列中显示错误。但是在 sid 列中提供了错误的值 x

日志

Record 1: Rejected - Error on table "DUMMY20210527", column VDATE.
ORA-01722: invalid number
Table "DUMMY20210527":
  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.

但是，如果加载了以下文件，则会显示正确的错误消息

a   b   c
X   2019/01/10  1

日志

Record 1: Rejected - Error on table "DUMMY20210527", column PID.
ORA-01722: invalid number


Table "DUMMY20210527":
  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.

使用的 Oracle 版本：Oracle 19c

标签： oraclesql-loaderoracle19c

解决方案

推荐阅读