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

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

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

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

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可以看到中文字符集

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查看中文

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