当前位置： 首页 > DUL > 正文

dul处理long、clob、blob字段中有中文内容

huangtingzhong 发布于 2014-09-15 23:03DUL抢沙发

下面只是用于测试，请误使用到生产环境

1，软件版本

[oracle@www.htz.pw ~]$lsb_release -a

LSB Version:    :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch

Distributor ID: RedHatEnterpriseAS

Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)

Release:        4

Codename:       NahantUpdate8

www.htz.pw > select * from v$version;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

[oracle@www.htz.pw ~]$./dul1

 

Data UnLoader 10.2.4.37 – Oracle Internal Only – on Mon Sep 15 22:40:52 2014

with 64-bit io functions

 

Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.

 

 Strictly Oracle Internal use Only

2，测试long字段有中文字符

www.htz.pw > desc htz.test;

 Name                                      Null?    Type

 —————————————– ——– —————————-

 ID                                                 NUMBER

 NAME                                               LONG

www.htz.pw > set lines  200

www.htz.pw > select * from htz.test;

 

        ID NAME

———- ——————————————————————————–

         1 123534

         1 东方龙马成都技术工程师:黄廷忠，个人BLOG:www.htz.pw

       123 123

DUL> unload table htz.test;

. unloading table                      TEST

DUL: Warning: Recreating file "HTZ_TEST.ctl"

       3 rows unloaded

      

[oracle@www.htz.pw ~]$cat HTZ_TEST.ctl

load data

CHARACTERSET ZHS16GBK

infile ‘HTZ_TEST.dat’

insert

into table "HTZ"."TEST1"这里将原来的TEST更改为TEST1

fields terminated by whitespace

(

  "ID"                               CHAR(3) enclosed by X’7C’      

 ,"NAME"                             CHAR(30) enclosed by X’7C’      

)

 

[oracle@www.htz.pw ~]$od -x HTZ_TEST.dat

0000000 317c 207c 317c 3332 3335 7c34 7c0a 7c31

0000020 7c20 abb6 bdb7 fac1 edc2 c9b3 bcb6 bcbc

0000040 f5ca a4b9 ccb3 a6ca bb3a cdc6 d6a2 a3d2

0000060 b8ac c8f6 42cb 4f4c 3a47 7777 2e77 7468

0000100 2e7a 7770 0a7c 317c 3332 207c 317c 3332

0000120 0a7c

0000122

创建一个test1表，用于导入数据

www.htz.pw > create table htz.test1(id number,name long);

 

Table created.

[oracle@www.htz.pw ~]$sqlldr htz/oracle control=HTZ_TEST.ctl

 

SQL*Loader: Release 11.2.0.3.0 – Production on Fri Sep 12 08:23:51 2014

 

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

 

Commit point reached – logical record count 3

 

www.htz.pw > set lines 200

www.htz.pw > col name for a100

www.htz.pw > select * from htz.test1;

 

        ID NAME

———- —————————————————————————————————-

         1 123534

         1 东方龙马成都技术工程师:黄廷忠，个人BLOG:www.htz.pw

       123 123

这里看到long中文字段是完美支持的。

3，测试BLOB字段有中文字符

www.htz.pw > create table htz.blob (id number,name blob);

 

Table created.

 

 

www.htz.pw > select * from htz.blob;

 

        ID

———-

NAME

——————————————————————————–

 

B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2

2CB8F6C8CB424C4F473A7777772E68747A2E7077

通过pl/sql可以看到中文字符集

clip_image001[4]

www.htz.pw > create table htz.blob1 as select * from htz.blob where 1=2;

 

Table created.

 

DUL> desc htz.blob;

Table HTZ.BLOB

obj#= 81834, dataobj#= 81834, ts#= 4, file#= 4, block#=9370

      tab#= 0, segcols= 2, clucols= 0

Column information:

icol# 01 segcol# 01           ID len   22 type  2 NUMBER(0,-127)

icol# 02 segcol# 02         NAME len 4000 type 113 BLOB

  LOB Segment: dataobj#= 81835, ts#= 4, file#= 4, block#=9378 chunk=1

  LOB Index: dataobj#= 81836, ts#= 4, file#= 4, block#=9386

 

DUL> unload table htz.blob;

. unloading (index organized) table     LOB010024aa       0 rows unloaded

Preparing lob metadata from lob index

Reading LOB010024aa.dat 0 entries loaded and sorted 0 entries

. unloading table                      BLOB       1 row  unloaded

-rw-r–r–   1 oracle oinstall        0 Sep 12 09:33 LOB010024aa.dat

-rw-r–r–   1 oracle oinstall      335 Sep 12 09:33 LOB010024aa.ctl

-rw-r–r–   1 oracle oinstall       60 Sep 12 09:33 LF0001.lob

-rw-r–r–   1 oracle oinstall       16 Sep 12 09:33 HTZ_BLOB.dat

-rw-r–r–   1 oracle oinstall      330 Sep 12 09:33 HTZ_BLOB.ctl

 

 

 

[oracle@www.htz.pw ~]$od -x LF0001.lob

0000000 abb6 bdb7 fac1 edc2 c9b3 bcb6 d6b7 abb9

0000020 becb 524f 4341 454c bcbc f5ca a4b9 ccb3

0000040 a6ca c6bb a2cd d2d6 b82c c8f6 42cb 4f4c

0000060 3a47 7777 2e77 7468 2e7a 7770

0000074

 

 

[oracle@www.htz.pw ~]$sqlldr htz/oracle control=HTZ_BLOB.ctl

 

SQL*Loader: Release 11.2.0.3.0 – Production on Fri Sep 12 09:47:25 2014

 

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

 

Commit point reached – logical record count 1

 

 

www.htz.pw > select * from htz.blob1;

 

        ID

———-

NAME

——————————————————————————–

 

B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2

2CB8F6C8CB424C4F473A7777772E68747A2E7077

 

 

www.htz.pw > select * from htz.blob;

 

        ID

———-

NAME

——————————————————————————–

 

B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2

2CB8F6C8CB424C4F473A7777772E68747A2E7077

结果集完全一样。

通过pl/sql查看中文

clip_image002[4]

BLOB中文也完美支持

4 dul测试clob中文字符

www.htz.pw > create table htz.clob (id number,name clob,address clob);

 

Table created.

 

 

www.htz.pw > select * from htz.clob;

 

        ID NAME                                                         ADDRESS

———- ———————————————————— —————————————-

       123 东方龙马技术工程师：黄廷忠，个人BLOG：www.htz.pw             www.htz.pw

       123 东方龙马成都分公司ORACLE技术工程师黄廷忠                     个人博客www.htz.pw

       124 东方龙马成都分公司ORACLE技术工程师黄廷忠

 

 

DUL> desc htz.clob;

Table HTZ.CLOB

obj#= 81807, dataobj#= 81807, ts#= 4, file#= 4, block#=10186

      tab#= 0, segcols= 3, clucols= 0

Column information:

icol# 01 segcol# 01           ID len   22 type  2 NUMBER(0,-127)

icol# 02 segcol# 02         NAME len 4000 type 112 CLOB cs 852(ZHS16GBK)

  LOB Segment: dataobj#= 81808, ts#= 4, file#= 4, block#=10194 chunk=1

  LOB Index: dataobj#= 81809, ts#= 4, file#= 4, block#=10202

icol# 03 segcol# 03      ADDRESS len 4000 type 112 CLOB cs 852(ZHS16GBK)

  LOB Segment: dataobj#= 81810, ts#= 4, file#= 4, block#=10210 chunk=1

  LOB Index: dataobj#= 81811, ts#= 4, file#= 4, block#=10218

DUL> unload table htz.clob;

. unloading (index organized) table     LOB010027da       0 rows unloaded

. unloading (index organized) table     LOB010027ea       0 rows unloaded

Preparing lob metadata from lob index

Reading LOB010027da.dat 0 entries loaded and sorted 0 entries

Preparing lob metadata from lob index

Reading LOB010027ea.dat 0 entries loaded and sorted 0 entries

. unloading table                      CLOB

DUL: Warning: Recreating file "HTZ_CLOB.ctl"

       3 rows unloaded

      

      

www.htz.pw > create table htz.clob1 as select * from htz.clob where 1=2;

 

Table created.

 

 

[oracle@www.htz.pw ~]$mv LF0005.lob    LF0005.lob.back

[oracle@www.htz.pw ~]$mv LF0004.lob    LF0004.lob.back

[oracle@www.htz.pw ~]$mv LF0003.lob    LF0003.lob.back

[oracle@www.htz.pw ~]$mv LF0002.lob    LF0002.lob.back

[oracle@www.htz.pw ~]$mv LF0001.lob    LF0001.lob.back

[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0005.lob.back  >   LF0005.lob

[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0004.lob.back  >   LF0004.lob

[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0003.lob.back  >   LF0003.lob

[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0002.lob.back  >   LF0002.lob

[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0001.lob.back  >   LF0001.lob

其实这里我们可以通过下面这条命令一步搞定

ls -l LF*.lob|grep -v grep|awk ‘{cmd="mv "$9" "$9".back";print(cmd);system(cmd);cmd1="iconv -f UCS-2BE -t gb2312 "$9".back > "$9;print(cmd1);system(cmd1);cmd3="rm "$9".back";print(cmd3);system(cmd3)}’

[oracle@www.htz.pw ~]$sqlldr htz/oracle control=HTZ_CLOB.ctl

 

SQL*Loader: Release 11.2.0.3.0 – Production on Thu Sep 11 23:54:08 2014

 

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

 

Commit point reached – logical record count 3

 

www.htz.pw > select * from htz.clob1;

 

        ID NAME                                                         ADDRESS

———- ———————————————————— ————————————————–

       123 东方龙马技术工程师：黄廷忠，个人BLOG：www.htz.pw             www.htz.pw

       123 东方龙马成都分公司ORACLE技术工程师黄廷忠                     个人博客www.htz.pw

       124 东方龙马成都分公司ORACLE技术工程师黄廷忠

赏

您可能还会对这些文章感兴趣！

dul处理long、clob、blob字段中有中文内容：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter