oracle sqlloader导入时报ORA-01830解决过程

发布时间：2020-08-11 10:11:10

来源：ITPUB博客

阅读：84

作者：水逸冰

目标：

将/home/oracle/xfsqlldr.csv导入到新建的CHOICE.T_LOAN_ANNUAL_CUSTNO表中。

新建表：

create table CHOICE.T_LOAN_ANNUAL_CUSTNO(

LOANNO VARCHAR2(200)  ,

FUNDCUSTNO VARCHAR2(200)  ,

DATEDATE DATE  ,

DATETYPE VARCHAR2(2)  ,

IS_DEL  VARCHAR2(1) DEFAULT '0' not null,

OITIME DATE DEFAULT sysdate not null,

OUTIME DATE DEFAULT sysdate not null);

处理文本，将标题行去掉

sed -i '1d' xfsqlldr.csv

more xfsqlldr.csv |wc -l

37291

编辑sqlloader控制文件

vi /home/oracle/xfsqlldr.ctl

load data

infile '/home/oracle/xfsqlldr.csv'

insert into table choice.T_LOAN_ANNUAL_CUSTNO

fields terminated by ','

OPTIONALLY ENCLOSED BY '"'

(

LOANNO,

FUNDCUSTNO,

DATEDATE,

DATETYPE

)

sqlloader导入：

sqlldr "'/as sysdba'" ROWS=10000 control=/home/oracle/xfsqlldr.ctl LOG=/home/oracle/xfsqlldr.log

报错：

value used for ROWS parameter changed from 10000 to 248

Record 1: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE.

ORA-01830: date format picture ends before converting entire input string

Record 2: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE.

ORA-01830: date format picture ends before converting entire input string

Record 3: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE.

ORA-01830: date format picture ends before converting entire input string

Record 4: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE.

ORA-01830: date format picture ends before converting entire input string

Record 5: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE.

ORA-01830: date format picture ends before converting entire input string

导入的时间列是如下格式：

2017/9/1 16:31:25.202000

秒后面的202000不能识别。

修改导入控制文件：

load data

infile '/home/oracle/xfsqlldr.csv'

insert into table CHOICE.T_LOAN_ANNUAL_CUSTNO

fields terminated by ','

OPTIONALLY ENCLOSED BY '"'

(

LOANNO,

FUNDCUSTNO,

DATEDATE "to_date(substr(:DATEDATE,'0',instr(:DATEDATE,'.')-1),'yyyy-mm-dd hh34:mi:ss')",

DATETYPE

)

select to_date(substr('2017/9/1 16:31:25.202000','0',instr('2017/9/1 16:31:25.202000','.')-1),'yyyy-mm-dd hh34:mi:ss') from dual;

sqlloader再次导入：

sqlldr "'/as sysdba'" ROWS=10000 control=/home/oracle/xfsqlldr.ctl LOG=/home/oracle/xfsqlldr.log

省略大部分输出：

Commit point reached - logical record count 37044

Commit point reached - logical record count 37292

Commit point reached - logical record count 37540

Commit point reached - logical record count 37788

Commit point reached - logical record count 37921

检查日志：

Table CHOICE.T_LOAN_ANNUAL_CUSTNO:

37921 Rows successfully loaded.

0 Rows 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:                 255936 bytes(248 rows)

Read   buffer bytes: 1048576

Total logical records skipped:          0

Total logical records read:         37921

Total logical records rejected:         0

Total logical records discarded:        0

检查数据：

SQL> select count(*) from CHOICE.T_LOAN_ANNUAL_CUSTNO;

COUNT(*)

----------

37921

总行数对。

select count(LOANNO) from CHOICE.T_LOAN_ANNUAL_CUSTNO    ;

COUNT(LOANNO)

-------------

33466

sed -n '33465,33467p' xfsqlldr.csv

4106ed46b9de9370c001448a308881f1,4fdcf2ce524942b0a003757a615db4e9,2017/12/12 12:27:25.367000,2

a83c402de3c3233f7416884be2e2533c,290112dd17ca492184e13475f9b93817,2017/12/8 23:19:16.387000,2

,1b78c7f7e04b46a19b0973fad39143f6,2018/5/15 21:13:38.867000,3

select count(FUNDCUSTNO) from CHOICE.T_LOAN_ANNUAL_CUSTNO    ;

COUNT(FUNDCUSTNO)

-----------------

37921

该列没有空值，和总行数一致，正确。

select count(DATEDATE) from CHOICE.T_LOAN_ANNUAL_CUSTNO    ;

COUNT(DATEDATE)

---------------

36453

sed -n '36452,36454p' xfsqlldr.csv

,8ea4d98b4825490c9c48a82307269175,2018/6/5 18:03:34.680643,3

,2e4cc6b253f6434e9a0a010513256022,2018/4/9 11:51:54.507954,3

,86f77fb6962943a78c00fd028bcdcaef,,4

正确。

SQL> select count(DATETYPE) from CHOICE.T_LOAN_ANNUAL_CUSTNO      ;

COUNT(DATETYPE)

---------------

37921

该列没有空值，和总行数一致，正确。

赋权：

SELECT ROLE FROM DBA_ROLES WHERE ROLE LIKE '%CHOICE%';

grant select on CHOICE.T_LOAN_ANNUAL_CUSTNO to R_CHOICE_READER;

grant select on CHOICE.T_LOAN_ANNUAL_HISHOLD to R_CHOICE_READER;

grant R_CHOICE_READER to p_chenzy_r,p_xufang_r;

grant insert,update,delete on CHOICE.T_LOAN_ANNUAL_CUSTNO  to p_xufang_r;

grant insert,update,delete on CHOICE.T_LOAN_ANNUAL_HISHOLD to p_xufang_r;