Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.