Introduction of oracle sqlloader into Times ORA-01830 solution process
Goal:
Import / home/oracle/xfsqlldr.csv into the newly created CHOICE.T_LOAN_ANNUAL_ customer no table.
New table:
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); process the text to remove the title line
Sed-I '1d' xfsqlldr.csv
More xfsqlldr.csv | wc-l
37291
Edit sqlloader control file
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 Import:
Sqlldr "'/ as sysdba'" ROWS=10000 control=/home/oracle/xfsqlldr.ctl LOG=/home/oracle/xfsqlldr.log
Error report:
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
The imported time column is in the following format:
2017-9-1 16:31:25.202000
202000 after seconds is not recognized.
Modify the import control file:
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 yyyy-mm-dd hh34:mi:ss' 31yyyy-mm-dd hh34:mi:ss' 25.202000) from dual; sqlloader re-imported:
Sqlldr "'/ as sysdba'" ROWS=10000 control=/home/oracle/xfsqlldr.ctl LOG=/home/oracle/xfsqlldr.log
Omit most of the output:
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
Check the log:
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 (248rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 37921
Total logical records rejected: 0
Total logical records discarded: 0
Check the data:
SQL > select count (*) from CHOICE.T_LOAN_ANNUAL_CUSTNO
COUNT (*)
-
37921
The total number of lines is right.
Select count (LOANNO) from CHOICE.T_LOAN_ANNUAL_CUSTNO
COUNT (LOANNO)
-
33466
Sed-n '33465 women 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
The column has no null value, which is the same as the total number of rows, and is correct.
Select count (DATEDATE) from CHOICE.T_LOAN_ANNUAL_CUSTNO
COUNT (DATEDATE)
-
36453
Sed-n '36452 sed 36454p' xfsqlldr.csv
, 8ea4d98b4825490c9c48a82307269175,2018/6/5 18:03:34.680643,3
, 2e4cc6b253f6434e9a0a010513256022,2018/4/9 11:51:54.507954,3
, 86f77fb6962943a78c00fd028bcdcaef,4
That's right.
SQL > select count (DATETYPE) from CHOICE.T_LOAN_ANNUAL_CUSTNO
COUNT (DATETYPE)
-
37921
The column has no null value, which is the same as the total number of rows, and is correct.
Empowerment:
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
