【练习】移动数据---解决null值

1.创建数据文件：

[oracle@host03 ~]$ vi base_data.dat 

1,zhangfei,zhangyide
2,guanyu,guanyunchang
3,liubei,liuxuande
4,zhugeliang,

2.创建控制文件：

[oracle@host03 ~]$ vi base.ctl

load data
infile 'base_data.dat'
into table s1_base
truncate
fields terminated by ','
(id,fname,lname)

3.查看创建的文件：

[oracle@host03 ~]$ ll
total 8
-rw-r--r-- 1 oracle oinstall 103 Nov 11 13:20 base.ctl
-rw-r--r-- 1 oracle oinstall  76 Nov 11 13:18 base_data.dat

4.通过sqlldr导入数据：

[oracle@host03 ~]$ sqlldr scott/tiger control=base.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Nov 11 13:22:29 2016

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

Commit point reached - logical record count 4

5.查看生成的相关文件：

[oracle@host03 ~]$ ll
total 16
-rw-r--r-- 1 oracle oinstall  103 Nov 11 13:20 base.ctl
-rw-r--r-- 1 oracle oinstall   36 Nov 11 13:22 base_data.bad
-rw-r--r-- 1 oracle oinstall   76 Nov 11 13:18 base_data.dat
-rw-r--r-- 1 oracle oinstall 1839 Nov 11 13:22 base.log

6.查看bad文件内容：

[oracle@host03 ~]$ cat base_data.bad 
4,zhugeliang,
2,guanyu,guanyunchang

7.查看导入数据：

13:12:37 SCOTT@ORA11GR2>select * from s1_base;

        ID FNAME      LNAME
---------- ---------- ----------
         1 zhangfei   zhangyide
         3 liubei     liuxuande

8.解决空值问题（控制文件中加入trailing nullcols）：

[oracle@host03 ~]$ vi base.ctl 

load data
infile 'base_data.dat'
into table s1_base
truncate
fields terminated by ','
trailing nullcols
(id,fname,lname)

9.在执行sqlldr：

[oracle@host03 ~]$ sqlldr scott/tiger control=base.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Nov 11 13:28:07 2016

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

Commit point reached - logical record count 4

10.查看bad文件：

[oracle@host03 ~]$ cat base_data.bad 
2,guanyu,guanyunchang

11.验证空值：

13:24:43 SCOTT@ORA11GR2>select * from s1_base;

        ID FNAME      LNAME
---------- ---------- ----------
         1 zhangfei   zhangyide
         3 liubei     liuxuande
         4 zhugeliang