1、dcba的ociuldr工具

ociuldr这个工具初始是由dcba开发的,用的是v7版的oci函数库

目的是完成导出Oracle中的数据到文本文件这样的功能

这个工具流传的非常广

详细介绍可以参见:

http://www.anysql.net/ociuldr.html

2、hrb_qiuyb改版的原因及目的

这里把dcba的ociuldr定义为v1版

oracle不断在添加新的功能，相应的oci的函数库变化也很大

本质的变化发生在v8以后的版本,为oci的开发扩展了很大的空间

本次改版的目的有以下几个：

a.如同当年以c语言改写unix代码一样,目的是为使ociuldr的后续开发有更广阔的空间

b.加入对BLOB和CLOB的支持

c.克服一个人开发所带来的思维上的局限性

d.为后续的优化做好准备

3、ociuldrv2.1相对v1的新特征

主要有两个：

a.支持BLOB,CLOB数据类型

b.以新版的oci函数库重写的v1版的程序

c.性能与v1版相当

4、BLOB与CLOB的支持示例

[php]

(1)数据库内有一张表uu1

hpn2$sqlplus hr/hr@orcl

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 23 13:23:59 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

SQL> desc uu1

Name                      Null?    Type

------------------------- -------- --------------

COL1                               NUMBER

COL2                               VARCHAR2(2)

COL3                               CHAR(3)

COL4                               BLOB

COL5                               CLOB

COL6                               NUMBER

SQL> SELECT col1,col2,col3,length(col4),length(col5),col6 FROM uu1

2  /

COL1 CO COL LENGTH(COL4) LENGTH(COL5)       COL6

---------- -- --- ------------ ------------ ----------

1 4  743       140077       142237          8

2 4  744        82772         2163          7

3 4  745        94811        64521          4

4 4  746        29437         1420          2

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

(2) ociuldr v2.1可执行文件

hpn2$ls -l

total 656

-rwxr-xr-x   1 oracle     dba         127640 Jun 23 13:25 ociuldr_v2.1

hpn2$ociuldr_v2.1 -help

ociuldr: Release 2.1 by hrb_qiuyb

Usage: ociuldr_v2.1 user=... query=... field=... record=... file=...

Notes:

user  = username/password@tnsname

sql   = SQL file name

query = select statement

field = seperator string between fields

record= seperator string between records

file  = output file name(default: mydata.txt)

read  = set DB_FILE_MULTIBLOCK_READ_COUNT at session level

sort  = set SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE at session level (UNIT:MB)

hash  = set HASH_AREA_SIZE at session level (UNIT:MB)

serial= set _serial_direct_read to TRUE at session level

trace = set event 10046 to given level at session level

table = table name in the sqlldr control file

mode  = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE

log   = log file name, prefix with + to append mode

long  = maximum long field size

array = array fetch size

buffer= sqlldr READSIZE and BINDSIZE, default 16 (MB)

for field and record, you can use '0x' to specify hex character code,

=0x0d

=0x0a |=0x7c ,=0x2c         =0x09

for more hex character code,you can use unix command:man ascii

hpn2$

(3)执行生成unload操作,其中table选项是ociuldr生成的sql*loader导入的control文件

hpn2$ociuldr_v2.1 user=hr/hr@orcl query="select * from uu1" table=uu1

Initializing the environment..

Logging on as hr..

Connect to orcl sucessful!

Logged on

0 rows exported at 2008-06-23 13:26:21

4 rows exported at 2008-06-23 13:26:21

output file mydata.txt closed at 4 rows.

Freeing statement handle..

Logging off...

Freeing handles..

(4)查看下结果,每一个lob自动生成一个文件

hpn2$ls -l

total 1840

-rw-r--r--   1 oracle     dba         140077 Jun 23 13:26 LF_4_1.blob

-rw-r--r--   1 oracle     dba          82772 Jun 23 13:26 LF_4_2.blob

-rw-r--r--   1 oracle     dba          94811 Jun 23 13:26 LF_4_3.blob

-rw-r--r--   1 oracle     dba          29437 Jun 23 13:26 LF_4_4.blob

-rw-r--r--   1 oracle     dba         142237 Jun 23 13:26 LF_5_1.clob

-rw-r--r--   1 oracle     dba           2163 Jun 23 13:26 LF_5_2.clob

-rw-r--r--   1 oracle     dba          64521 Jun 23 13:26 LF_5_3.clob

-rw-r--r--   1 oracle     dba           1420 Jun 23 13:26 LF_5_4.clob

-rwxr-xr-x   1 oracle     dba         127640 Jun 23 13:25 ociuldr_v2.1

-rw-r--r--   1 oracle     dba            288 Jun 23 12:57 uu1.txt

-rw-r--r--   1 oracle     dba            445 Jun 23 13:26 uu1_sqlldr.ctl

(5) ociuldr生成的文本数据文件

hpn2$

hpn2$cat uu1.txt

1,4,743,LF_4_1.blob,LF_5_1.clob,8

2,4,744,LF_4_2.blob,LF_5_2.clob,7

3,4,745,LF_4_3.blob,LF_5_3.clob,4

4,4,746,LF_4_4.blob,LF_5_4.clob,2

(6)table选项自动生成的sql*loader control文件

hpn2$cat uu1_sqlldr.ctl

OPTIONS(BINDSIZE=16777216,READSIZE=16777216,ERRORS=-1,ROWS=50000)

LOAD DATA

INFILE 'mydata.txt' "STR X'0a'"

INSERT INTO TABLE uu1

FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS

(

COL1 CHAR,

COL2 CHAR,

COL3 CHAR,

lobfile_col4  FILLER CHAR,

COL4 LOBFILE(lobfile_col4) TERMINATED BY EOF NULLIF lobfile_col4 = 'NONE' ,

lobfile_col5  FILLER CHAR,

COL5 LOBFILE(lobfile_col5) TERMINATED BY EOF NULLIF lobfile_col5 = 'NONE' ,

COL6 CHAR

)

(7)尝试一下sql*loader导入操作

hpn2$sqlplus hr/hr@orcl

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 23 13:26:54 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

--清表

SQL> truncate table uu1;

Table truncated.

SQL> SELECT col1,col2,col3,length(col4),length(col5),col6 FROM uu1 ;

no rows selected

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

--执行导入

hpn2$

hpn2$sqlldr hr/hr@orcl control=uu1_sqlldr.ctl

SQL*Loader: Release 10.2.0.3.0 - Production on Mon Jun 23 13:27:49 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 4

(8)确认一下

hpn2$

hpn2$sqlplus hr/hr@orcl

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 23 13:28:13 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

SQL>  SELECT col1,col2,col3,length(col4),length(col5),col6 FROM uu1 ;

COL1 CO COL LENGTH(COL4) LENGTH(COL5)       COL6

---------- -- --- ------------ ------------ ----------

1 4  743       140077       142237          8

2 4  744        82772         2163          7

3 4  745        94811        64521          4

4 4  746        29437         1420          2

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

;

[/php]

[本帖最后由 hrb_qiuyb 于 2008-6-23 13:40 编辑]