SQL*LOADER加载实例测试

SQL*Loader加载数据有两种使用方法：一种是只使用一个控制文件，控制文件中包含数据；另外一种是使用一个控制文件和一个数据文件。一般为了利于模板和数据的分离，以及程序的不同分工会使用第二种方式。数据文件可以是CSV文件或者以其他分割符分隔的TXT文件，如数据源是EXCEL表格，则可以先将文件另存为CSV格式数据文件。数据文件可以用PL/SQL Developer或者Toad导出，也可以用SQL*Plus的SPOOL格式化产出，或是UTL_FILE包生成，另外，用Toad还能直接生成包含数据的控制文件。

1．数据包含在控制文件中的情况

例1：加载控制文件中的数据到LOADTEST表中，数据加载方式为APPEND，数据以“|”分隔，对应列空缺数据赋予空值，指定废弃文件。

1) 建立控制文件test.ctl

LOAD DATA 
INFILE *
DISCARDFILE 'test.dsc'
APPEND
INTO TABLE LOADTEST
WHEN LOADID<>'5'
FIELDS TERMINATED BY"|"
TRAILING NULLCOLS
(LOADID, OPERNAME, OPERTYPE)
BEGINDATA
1|nn|append
2|kk|truncate
3|jjdf|insert
4|ddf|
J|dts|insert
5|asas|replace

2) 启动SQL*Loader加载数据

[oracle@nn ~]$ sqlldr nn/123 control=/home/oracle/test.ctl

注意：在进行加载前，确保目标表已经创建，否则将出现如上的SQL*Loader-941、ORA-04043，提示目标表不存在。

下面我们在NN用户下创建目标表LOADTEST。

SQL>CREATE TABLE LOADTEST
(  loadid   NUMBER,
  opername     VARCHAR2(100),
  opertype VARCHAR2(50))

下面重新进行上述的加载：

3) 查询加载成功的数据

根据控制文件的加载条件，LOADID等于5的数据记录不加载，所以控制文件中第6条数据没有加载进去，应该保存在废弃文件test.dsc中，另外，第4条记录的OPERTYPE没有数据，根据加载条件对应列没有数据时赋予空值，所以该列值为空，还有第5条数据的LOADID数据与目标表定义的LOADID列数据类型不符，所以不会被加载，而是被保存到错误文件test.bad中。

SQL>  SELECT * FROM loadtest;

2．数据文件独立存在的情况

1) 数据文件

本案例数据文件为test01.dat，数据分隔符为“,”。

7,rfg,append
8,gdgdg,truncate
9,uytt,insert
11,dgdg,
g,litu,insert
10,jygjh,replace

2) 控制文件

创建控制文件test01.ctl，指明要加载的数据文件名，以及废弃文件，加载数据条件为LOADID不为10，数据之间以“,”分隔，对应列空缺数据赋予空值。

LOAD DATA 
INFILE test01.dat
DISCARDFILE 'test01.dsc'
APPEND
INTO TABLE LOADTEST
WHEN LOADID<>'10'
FIELDS TERMINATED BY","
TRAILING NULLCOLS
(LOADID, OPERNAME, OPERTYPE)

3) 启动SQL*Loader加载过程

执行命令：[oracle@nn ~]$ sqlldr nn/123 control=/home/oracle/test01.ctl

执行结果由图红色部分可见，SQL*Loader对数据文件中的6行物理数据进行了加载，加载结果通过“SQL> SELECT * FROM loadtest;”查询可知，只有4行数据时加载成功的，数据文件中有2行数据不符合加载条件没有被加载进去。分别是第5行数据，LOADID与目标表定义的列数据类型不一致，所以被拒绝，第6行数据因为与WHEN条件LOADID<>'10'冲突，所以被废弃。另外，第4行数据的OPERTYPE没有数据，所以加载时赋予了空值。

4) 查看加载日志文件

除了通过查询目标表的数据来查看加载的情况，还有一种更直接的方法就是查看加载的日志文件中Total logical records skipped、Total logical records read、Total logical records rejected、Total logical records discarded，看看被拒绝与被废弃的数据有多少，原因是什么，另外也可以直接查看对应的错误文件和废弃文件看看哪些数据是没有加载成功的。