eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 从《越狱》(Prison Break)看中美差异 | Blog首页 | EMC Cx500 ATA盘损坏 »

如何在sqlldr中倒入多字符分隔符文件

今天有朋友询问:
我们...应用系统很多,它们之间要交换很多数据,目前是以文本方式交换,问题是文本的分隔符号是(|+|),为三个字符,主要是避免数据的混淆.....

这样的文件能够用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/eygle

SQL*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 - Production

SQL> 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.txt

SQL*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/eygle

SQL*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 - Production

SQL> select * from test;

T_ID T_VOL
-------------------- --------------------
20021228000000 00120000
20021228000000 00130000
20021228000000 00140000
20021||8000000 00140000
20021++8000000 00140000

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

只作了简单验证,不知道复杂情况会不会有问题。

-The End-


历史上的今天...
    >> 2012-11-22文章:
    >> 2011-11-22文章:
    >> 2009-11-22文章:
    >> 2008-11-22文章:
    >> 2005-11-22文章:

无觅

By eygle on 2006-11-22 13:26 | Comments (4) | HowTo | 977 |

4 Comments

Why not try it before question?

不会有问题的,字段和记录都可以是多字符分隔的(从8I开始支持), 我曾经试过文本字符串和图像混在一个文件中的,都可以的。

学习了

要大胆的去尝试!!


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com