« 从《越狱》（Prison Break）看中美差异 | Blog首页 | EMC Cx500 ATA盘损坏 »
如何在sqlldr中倒入多字符分隔符文件
作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】链接：http://www.eygle.com/archives/2006/11/sqlldr_multi_separator.html
今天有朋友询问：
我们...应用系统很多，它们之间要交换很多数据，目前是以文本方式交换，问题是文本的分隔符号是（|+|），为三个字符，主要是避免数据的混淆.....
这样的文件能够用sqlldr导入么?
我测试了一下，试验证明是可以的，sqlldr支持多字符分隔符文件导入。
首先看我的数据文件和控制文件:
[oracle@jumper tmp]$ cat data.ctl
load data
into table TEST
fields terminated by "|+|"
(
T_ID,
T_VOL
)
[oracle@jumper tmp]$ cat data.txt
20021228000000|+|00120000
20021228000000|+|00130000
20021228000000|+|00140000
20021||8000000|+|00140000
20021++8000000|+|00140000
创建测试表:
[oracle@jumper tmp]$ sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 21 13:21:16 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> create table test
2 (T_ID varchar2(20),
3 T_VOL varchar2(20)
4 );Table created.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
加载数据:
[oracle@jumper tmp]$ sqlldr eygle/eygle control=data.ctl data=data.txtSQL*Loader: Release 9.2.0.4.0 - Production on Tue Nov 21 13:23:53 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 5
[oracle@jumper tmp]$ sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 21 13:23:57 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> select * from test;
T_ID T_VOL
-------------------- --------------------
20021228000000 00120000
20021228000000 00130000
20021228000000 00140000
20021||8000000 00140000
20021++8000000 00140000SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
只作了简单验证，不知道复杂情况会不会有问题。
-The End-
By eygle on 2006-11-22 13:26 | Comments (4) | Posted to HowTo | Edit |Pageviews:
| 相关文章 | 随机文章 |
|
|
美丽的京剧 美丽的晋剧 回家之难 难于上青天 徒步上班的清晨 CPU Load Very High-超高负载之数据库 关于自动PGA管理的进一步探讨 |
留言 (4)
Why not try it before question?
Posted by: anysql at November 22, 2006 9:10 PM
不会有问题的，字段和记录都可以是多字符分隔的（从8I开始支持）， 我曾经试过文本字符串和图像混在一个文件中的，都可以的。
Posted by: wangke at March 13, 2007 12:43 PM
学习了
Posted by: guyuanli at April 16, 2007 10:43 AM
要大胆的去尝试！！
Posted by: Hopewell_Go at October 10, 2007 5:47 PM
