Oracle: Text data imported by SQL Loader

Environment: Oracle 10g 10.2

There is a text data in the following format:

HLR_CODE HLR_TYPE REGION_CODE AREA_CODE PROV_CODE BUREAU_CODE VALID_DATE EXPIRE_DATE MODIFY_DATE
1 1302309 1 565 565 551 Z00 2000-1-1 2020-12-31 23:59:59 20:22:56
2 1302310 1 210 21 210 Z00 2000-1-1 2020-12-31 23:59:59 20:22:56
3 1302311 1 210 21 210 Z00 2000-1-1 2020-12-31 23:59:59 20:22:56
4 1302312 1 210 21 210 Z00 2000-1-1 2020-12-31 23:59:59 20:22:56

You need to import the data to a table:

Create table BS_HLR_INFO
(
HLR_CODE VARCHAR2 (8) not null,
HLR_TYPE NUMBER (3) not null,
REGION_CODE VARCHAR2 (5) not null,
AREA_CODE VARCHAR2 (5) not null,
PROV_CODE VARCHAR2 (7) not null,
BUREAU_CODE VARCHAR2 (5 ),
VALID_DATE DATE not null,
EXPIRE_DATE DATE not null,
MODIFY_DATE DATE default sysdate
);

The method is to use SQL loader:

1. Write the configuration file

OPTIONS (skip = 1, rows = 128)
LOAD DATA
INFILE "hlr_info"
Truncate
Into table BS_HLR_INFO -- the TABLE to insert records
Fields terminated ""
Trailing nullcols -- the table field can be empty if it does not have a corresponding value
(
Virtual_column FILLER, -- this is a virtual field used to skip the first column serial number generated by PL/SQL Developer.
HLR_CODE,
HLR_TYPE,
REGION_CODE,
AREA_CODE,
PROV_CODE,
BUREAU_CODE,
VALID_DATE DATE "YYYY-MM-DD HH24: MI: SS ",
EXPIRE_DATE "YYYY-MM-DD HH24: MI: SS ",
MODIFY_DATE DATE "YYYY-MM-DD HH24: MI: SS"
)

2. Execute the import command

F: \ sqlloaderdata> dir hlr *
The volume in drive F is work
The serial number of the volume is 647B-14FC.

F: \ sqlloaderdata directory

2010-08-27 638 hlr_info.ctl
2010-08-24 18,470,605 hlr_info.dat
2010-08-27 2,056 hlr_info.log
3 files in 18,473,299 bytes
0 directories, 28,318,511,104 available bytes

F: \ sqlloaderdata> sqlldr cs/cs @ hatest control = hlr_info.ctl

Console output after execution:

...... Reached the submission point-logical record count 223296
Submission point reached-223406 logic record count
Submission point reached-223516 logic record count
Submission point reached-223626 logic record count
Submission point reached-223632 logic record count
Submission point reached-223633 logic record count

F: \ sqlloaderdata>

3. view log files

SQL * Loader: Release 10.2.0.1.0-Production on Friday August 27 16:26:37 2010

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Control File: hlr_info.ctl
Data File: hlr_info.dat
Error file: hlr_info.bad
Obsolete file: Not specified

(All records can be discarded)

Number of objects to be loaded: ALL
Number of to be skipped: 1
Allowed error: 50
Bound array: 128 rows, up to 256000 bytes
Continue: Unspecified
Path used: General

Table BS_HLR_INFO, loaded from each logical record
The insert option takes effect for the table TRUNCATE.
The trailing nullcols option takes effect.

Column name location length abort packaging Data Type
--------------------------------------------------------------------------
VIRTUAL_COLUMN FIRST * WHT CHARACTER
(Filler field)
HLR_CODE NEXT * WHT CHARACTER
HLR_TYPE NEXT * WHT CHARACTER
REGION_CODE NEXT * WHT CHARACTER
AREA_CODE NEXT * WHT CHARACTER
PROV_CODE NEXT * WHT CHARACTER
BUREAU_CODE NEXT * WHT CHARACTER
VALID_DATE NEXT * wht date YYYY-MM-DD HH24: MI: SS
EXPIRE_DATE NEXT * wht date YYYY-MM-DD HH24: MI: SS
MODIFY_DATE NEXT * wht date YYYY-MM-DD HH24: MI: SS

The value used by the ROWS parameter has been changed from 128 to 110.

Table BS_HLR_INFO:
223633 rows are loaded successfully.
Zero rows are not loaded due to data errors.
Because all the WHEN clauses fail, the 0 rows are not loaded.
Because all fields are empty, 0 rows are not loaded.


Space allocated to the bound array: 255420 bytes (110 rows)
Number of bytes read from the buffer: 1048576

Total number of skipped logical records: 1
Total number of logical records read: 223633
Total number of rejected logical records: 0
Total number of discarded logical records: 0

Started running from Friday August 27 16:26:37 2010
The operation ended at 16:27:05, January 1, 2010 at on Friday.

Elapsed time: 00: 00: 28.05
CPU time: 00: 00: 02.61

Import successful!