oracle sqlloader導(dǎo)入時報ORA-01830解決過程

目標(biāo)：
將/home/oracle/xfsqlldr.csv導(dǎo)入到新建的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); 處理文本，將標(biāo)題行去掉
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導(dǎo)入：
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

導(dǎo)入的時間列是如下格式：
2017/9/1 16:31:25.202000
秒后面的202000不能識別。

修改導(dǎo)入控制文件：
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再次導(dǎo)入：
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

檢查數(shù)據(jù)：
SQL> select count(*) from CHOICE.T_LOAN_ANNUAL_CUSTNO;

  COUNT(*)
----------
     37921
總行數(shù)對。
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
該列沒有空值，和總行數(shù)一致，正確。
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
該列沒有空值，和總行數(shù)一致，正確。

賦權(quán)：
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;