SQL*Loader加载外部数据

2021-08-26 14:45:23

Oracle SQL*Loader (SQLLDR) 用于从外部文件加载数据到Oracle数据库，比如txt文件，使得平面数据文件地加载十分方便高效。其强劲地数据解析引擎能够处理各种格式地数据，既可以本地加载也可以跨网络加载。 SQL*Loader的日志文件记录数据加载过程，提示加载遇到的报错信息。可以通过检查日志确定加载是否完成。

下面我们创建一个文件data1.txt

7782|| "Name1"|| "Job1"|| 7839|| 09-June-1981|||| 10
7566|| "Name2"|| "Job2"|| 7839|| 02-April-1981|||| 20
7499|| "Name3"|| "Job3"|| 7698|| 20-February-1981|| 300.00|| 30
7654|| "Name4"|| "Job4"|| 7698|| 28-September-1981|| 1400.00|| 30
7658|| "Name5"|| "Job5"|| 7566|| 03-May-1982|||| 20

控制文件如下,如果是空表可以使用insert into，append是附加的意思，已存在的数据不变，没有的加载进去，如果是replace则替换之前所有数据，先删除再插入。
LOAD DATA
INFILE '/home/oracle/data1.txt'
APPEND
INTO TABLE emp1
FIELDS TERMINATED BY "||" OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr, hiredate DATE(20) "DD-Month-YYYY", comm, deptno)

测试表，我们删除了3行数据，加载之前有14行记录，后续删除要加载的三条记录，再加载数据，我们再控制文件中使用append.
SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 rows selected.

SQL> delete from emp1 where empno in (7782,7566,7499);

3 rows deleted.

SQL> commit;

使用 SQL*Loader加载
[oracle@rac1 ~]$ sqlldr userid=mv/oracle control=sqlldrctl.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Wed Aug 25 11:13:42 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 5

Table EMP1:
5 Rows successfully loaded.

Check the log file:
sqlldrctl.log
for more information about the load.

日志信息如下
[oracle@rac1 ~]$ cat sqlldrctl.log

SQL*Loader: Release 19.0.0.0.0 - Production on Wed Aug 25 11:13:42 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Control File: ././sqlldrctl.ctl
Data File: /home/oracle/data1.txt
Bad File: ././data1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 bytes
Continuation: none specified
Path used: Conventional

Table EMP1, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * O(") CHARACTER
Terminator string : '||'
ENAME NEXT * O(") CHARACTER
Terminator string : '||'
JOB NEXT * O(") CHARACTER
Terminator string : '||'
MGR NEXT * O(") CHARACTER
Terminator string : '||'
HIREDATE NEXT 20 O(") DATE DD-Month-YYYY
Terminator string : '||'
COMM NEXT * O(") CHARACTER
Terminator string : '||'
DEPTNO NEXT * O(") CHARACTER
Terminator string : '||'

Table EMP1:
5 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: 392500 bytes(250 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Wed Aug 25 11:13:42 2021
Run ended on Wed Aug 25 11:13:46 2021

Elapsed time was: 00:00:03.93
CPU time was: 00:00:00.06

对于控制文件，参数参考如下设置
The following table lists other common keywords that can be used in the control file:

-------------+----------------------------------------------+-------------------
KEYWORD | DESCRIPTION | REFERENCES
-------------+----------------------------------------------+-------------------
WHEN | Specifies one or more field conditions. | Note 191533.1
| SQL*Loader decides whether or not to load |
| the data, based on these field conditions. |
-------------+----------------------------------------------+-------------------
INSERT | SQL*Loader's default method. Requires the | (*) SQL*Loader
| table to be empty before loading. SQL*Loader | Control File
| terminates with an error if the table | Reference
| contains rows. |
-------------+----------------------------------------------+-------------------
INTO TABLE | The INTO TABLE clause of the LOAD DATA | (*) SQL*Loader
| statement allows you to identify tables, | Control File
| fields, and data*. It defines the | Reference
| relationship between records in the datafile |
| and tables. |
-------------+----------------------------------------------+-------------------
REPLACE | All rows in the table are deleted and the | Note 171306.1
| new data is loaded. Note that this option | (*) SQL*Loader
| does not replace rows based on the primary | Control File
| key automatically or selectively. | Reference
-------------+----------------------------------------------+-------------------
SEQUENCE | Ensures a unique value for a particular | Note 1058895.6
| column. SEQUENCE increments for each record |
| that is loaded or rejected. It does not |
| increment for records that are discarded or |
| skipped. |
-------------+----------------------------------------------+-------------------
APPEND | If data already exists in the table, | (*) SQL*Loader
| SQL*Loader appends the new rows to it. If | Control File
| data does not already exist, the new rows | Reference
| are simply loaded. |
-------------+----------------------------------------------+-------------------
TRUNCATE | This is the same with the SQL TRUNCATE | (*) SQL*Loader
| statement. Deletes all rows from a table or | Control File
| cluster quickly and efficiently | Reference
-------------+----------------------------------------------+-------------------
FIELDS | Defines the series of character(s) that | Note 213446.1
TERMINATED | delimit fields in data file. |
BY | |
-------------+----------------------------------------------+-------------------
(OPTIONALLY)| The character(s) that are enclosing | Note 213446.1
ENCLOSED BY | (optionally) the fields. |
-------------+----------------------------------------------+-------------------
NULLIF | Enables to load NULL data into database | Note 163657.1
| where null is specified by a unique test in |
| the data file |
-------------+----------------------------------------------+-------------------
DEFAULTIF | Automatically sets non-numeric fields to | (*) SQL*Loader
| NULL and numeric fields to 0 (zero)based on | Control File
| a condition. | Reference
-------------+----------------------------------------------+-------------------