sql*loader试验小记1
1.首先我们来建个表，用来接数据
SQL> create table test(
2 id number,
3 name char);
Table created.
SQL> select * from test;
no rows selected
SQL> !
2.编辑ctl文件，ctl很重要~出了问题可导不进去~
[oracle@oracle9i u01]$ vi test.ctl
load data
infile 'test.dat'
into table test
fields terminated by ','
(id,name)
3.简洁版ctl完成，很简单吧，下面我们写个数据源~
[oracle@oracle9i u01]$ vi test.dat
001,jack,
002,tom,003,may,004,gok
"test.dat" [New] 3L, 35C written
4.大功告成，现在我们来执行一下~
[oracle@oracle9i u01]$ sqlldr loader/loader control=test.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Wed Feb 25 14:49:36 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
5.执行成功，select一下test表看看~
[oracle@oracle9i u01]$ sqlplus loader/loader
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Feb 25 14:49:42 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select * from test;
no rows selected
6.悲剧发生了！是谁偷走了我的数据？？！！
表确实存在，我们再去看看数据字典
SQL> select table_name from user_tables;
TABLE_NAME
--------------
A
PERF_PI_DEFINE_HO
TEST
SQL> select * from test;
no rows selected
7.问题在这里，char(1)显然和我们数据源的类型不符，短了！
SQL> desc test
Name Null Type
------ -------- --------
ID NUMBER
NAME CHAR(1)
8.重新建表~
SQL> drop table test;
Table dropped.
SQL> create table test(
2 id number(3),
3 name char(4)
4 );
Table created.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@oracle9i ~]$ cd /u01
[oracle@oracle9i u01]$ sqlldr loader/loader control=test.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Wed Feb 25 14:51:23 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
9.我们再数据源里写的并没有都进来，我们用个参数来解决！~
SQL> select * from test;
ID NAME
---------- ----
1 jack
2 tom
10.我们把ctl文件修改成如下：
[oracle@oracle9i u01]$ vi test.ctl
load data
infile 'test.unl' "fix 10"
into table test
fields terminated by ','
(id,name)
11.再来修改unl文件，注意空格~fix 10的含义是[001，jack，\n]，10个字符一读
001,jack,
002, tom,
003,mary, 004,lala,
12.好了，下面重复一下前面的导入操作，再察察test表
[oracle@oracle9i u01]$ sqlldr loader/loader control=test.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Wed Feb 25 15:26:25 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
SQL> select * from test;
ID NAME
---------- ----
1 jack
2 tom
3 mary
4 lala
==========================================================================================
^^ 成功！~ 收获在于导入时应注意数据源的数据字典，要和库里的空表匹配，否则就会像我一样，一场悲剧在所难免。
另：sg中花了6章的篇幅介绍sql*loader，可见其重要性，我只是简单的试验了一下，其中还有很多复杂的参数，待俺仔细研究后再来总结~~也请高手们积极拍砖~~谢谢各位看官~喝口水休息一下吧~~
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/11517198/viewspace-557797/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
