zt

关于US7库的数据在GB2312库中访问的实现方法

―― 广州瑞信  温和

基本思想：

在US7库中利用UTL_RAW函数创建视图，在GB2312库中创建链接至US7库去的database link，即可使用database link来访问US7中的中文字符数据(客户端为GB2312，无需做出改动)。

具体实现：

一、        环境介绍

数据库版本ORACLE 8.1.7

使用US7字符集的数据库SID为US7

使用GB2312字符集的数据库SID为ORA8

确保两个数据库中都装有PLSQL的UTL_RAW包(在sqlplus中使用desc utl_raw命令查看)

在GB2312库的主机上要有指向US7库的connection string (假设该connection string名为US7)。

二、        实施步骤

1、在US7库中建立对含有中文字段的表的视图，例post表，使用语句如下(仅举字段name为例，其他字段亦相同)：

create or replace view v_post as

select UTL_RAW.CAST_TO_RAW(name) name1

from post;

注：post表中name字段类型为varchar2，包含中文信息。函数UTL_RAW.CAST_TO_RAW将name字段从varchar2型转换为raw型。建立的视图v_post只含有name1字段。

2、在GB2312库中建立database link，指向US7库，该link的用户为T43，使用语句为：

create database link us7 connect to t43 identified by t43 using 'us7';

注：database link名为us7，使用connection string “us7” 来指向数据库US7。

3、在GB2312库中使用database link us7来建立视图，语句如下：

create or replace view v_post as

select UTL_RAW.CAST_TO_varchar2(name1) name

from v_post@us7;

注：函数UTL_RAW.CAST_TO_varchar2将US7库中的视图v_post的name1字段从raw型转为varchar2型。Create语句生成本地视图v_post，name字段为varchar2类型的中文信息。

4、检验：

使用语句 select * from v_post; 能正常显示数据

(或使用语句select UTL_RAW.CAST_TO_varchar2(name1) from v_post@us7;)

三、        附录

1、        create database link语句语法

Create a Database Link.

Syntax:

CREATE [SHARED][PUBLIC] DATABASE LINK link_name

[CONNECT TO CURRENT_USER]

[USING 'connect_string']

CREATE [SHARED][PUBLIC] DATABASE LINK link_name

[CONNECT TO user IDENTIFIED BY password]

[AUTHENTICATED BY user IDENTIFIED BY password]

[USING 'connect_string']

2、// CAST_TO_RAW

FUNCTION cast_to_raw(c IN VARCHAR2 CHARACTER SET ANY_CS) RETURN RAW;

pragma RESTRICT_REFERENCES(cast_to_raw, WNDS, RNDS, WNPS, RNPS);

Cast a varchar2 to a raw

This function converts a varchar2 represented using N data bytesinto a raw with N data bytes.

The data is not modified in any way, only its datatype is recastto a RAW datatype.

Input parameters:

c - varchar2 or nvarchar2 to be changed to a raw

Defaults and optional parameters: None

Return value:

raw - containing the same data as the input varchar2 and equal byte length as the input varchar2 and without a leading length field.

null - if c input parameter was null

Errors:

None

3、// CAST_TO_VARCHAR2

FUNCTION cast_to_varchar2(r IN RAW) RETURN VARCHAR2;

pragma RESTRICT_REFERENCES(cast_to_varchar2, WNDS, RNDS, WNPS, RNPS);

Cast a raw to a varchar2

This function converts a raw represented using N data bytes

NOTE:         Once the value is cast to a varchar2, string operations or server->client communication will assume that the string is represented using the database's character set.  "Garbage" results are likely if these operations are applied to a string that is actually represented using some other character set.

Input parameters:

r - raw (without leading length field) to be changed to a

varchar2)

Defaults and optional parameters: None

Return value:

varchar2                 - containing having the same data as the input raw

null             - if r input parameter was null

Errors:

None