Oracle DBLink 访问Lob 字段 ORA-22992 解决方法

最新推荐文章于 2022-01-04 15:48:00 发布

Data & safety

最新推荐文章于 2022-01-04 15:48:00 发布

阅读量1.1k

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

638 篇文章

订阅专栏

这篇测试一下通过DBLink 访问含有Blob字段表的方法。关于DBLINK 和 Lob 的说明，参考如下链接：

Oracle DBLink

http://www.cndba.cn/Dave/article/148

删除Dblink 报错ORA-02024: database link not found 的解决方法

http://www.cndba.cn/Dave/article/539

ORACLE LOB 大对象处理

http://www.cndba.cn/Dave/article/1209

Oracle LOB 详解

http://www.cndba.cn/Dave/article/1122

一．模拟问题

1.1 在实例1上操作：

创建含有blob 的测试表：

/* Formatted on 2012/6/19 10:18:05 (QP5 v5.185.11230.41888) */

CREATE TABLE lob1

(

line NUMBER primary key,

text CLOB

);

INSERT INTO lob1

SELECT distinct line, text FROM all_source where rownum<500;

SELECT segment_name,

segment_type,

tablespace_name,

SUM (bytes) / 1024 / 1024 || 'M' AS "SIZE"

FROM user_segments

WHERE segment_name = 'LOB1'

GROUP BY segment_name, segment_type, tablespace_name;

LOB 表的信息如下：

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE

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

LOB1 TABLE SYSTEM 9M

SQL> set wrap off;

SQL> select * from lob1 where rownum=1;

LINE TEXT

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

1 package STANDARD AUTHID CURRENT_USER is -- care

1.2 在实例2上操作

创建DBLINK：

CREATE PUBLIC DATABASE LINK lob_link CONNECT TO dave IDENTIFIED BY dave

USING '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.222)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dave)

)

)';

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> select count(*) from lob1@lob_link;

COUNT(*)

----------

58228

这边查询总记录数正常。但是当我们查询具体LOB字段里的内容的时候，就会报错，如下：

SQL> select * from lob1@lob_link where rownum=1;

ERROR:

ORA-22992: cannot use LOB locators selected from remote tables

no rows selected

[oracle@localhost ~]$ oerr ora 22992

22992, 00000, "cannot use LOB locators selected from remote tables"

// *Cause: A remote LOB column cannot be referenced.

// *Action: Remove references to LOBs in remote tables

二．MOS 上的相关说明

2.1 ORA-22992 When TryingTo Select Lob Columns Over A Database Link [ID 119897.1]

在官网的这篇文章中，基于Oracle 8i的进行了说明，报错的原因是因为不支持这个特性。

(A)You cannot actually select a lob column (i.e. CLOB column) from a table

using remote database link. Thisis not a supported feature.

(B)Also, these are the INVALID operations on a LOB column:

--以下操作也不被支持。

1. SELECT lobcol from table1@remote_site;

2. INSERT INTO lobtable select type1.lobattr from table1@remote_site;

3. SELECT dbms_lob.getlength(lobcol) from table1@remote_site;

2.2 ORA-22992 in SQLUsing DBLINK and NVL2 function in 10g. [ID 427239.1]

在Oracle 9i/10g版本中，存在Bug.5185187 ，因此在dblink中使用NVL2 函数时，就会出现ORA-22992的错误。

该Bug 在Oracle 11gR2中已经修复。

也可以使用如下方法，来间接的解决这个问题：

Original SQL:

select nvl2('a', 'b','c' )from dual@test;

Modified SQL:

selectto_char(nvl2('a','b','c')) from dual@test;

2.3 SELECT with a LOB andDBLink Returns an ORA-22992: Cannot Use LOB Locators Selected from Remotetables [ID 1234893.1]

在这边文章里也提到了原因：because the use ofDBLinks and LOBs via the SELECT from PL/SQL is not supported.

在这篇文章里也提供了一些解决方法：

The followingNotes discuss this issue, and give code snippets as example to work-around therestriction using the INTO clause. Keep in mind, if working with large LOBsizes, it may be best to implement the work-around of using a MaterializedView.

--如果LOB字段很大，最好使用物化视图来解决这个问题。

Note 459557.1 (ORA-1406: FetchedColumn Value was Truncated When Selecting Remote Column into Local BLOBVariable) discusses using a Materialized View in the scenarios where the BLOBwill be larger than 2Meg.

Note 119897.1 (ORA-22992 When Trying To SelectLob Columns Over A Database Link) states this feature is not supported but thenat the end of the Note it states starting with 10.2 there is a work-around ofusing LONG and LONG RAW types.

Note 436707.1 (Ora-22992 has a workaround in10gR2) illustrates a work-around (using LONG RAW) as mentioned was possible inthe previous note.

Note 796282.1 (Workaround for ORA-22992)provides 3 work-arounds.

三．解决方法

3.1 将接收的lob 存入char（CBob）或者raw（BLob）本地变量

MOS文章：

Ora-22992 has a workaround in 10gR2 [ID 436707.1]

Workaround for ORA-22992 [ID 796282.1]

Starting from 10g the select from alob object through a database link is supportedby receiving the LOB objects into variables defined as CHAR orRAW.

--从Oracle10g开始，dblink 的select可以被本地的char或raw 类型变量接收。

（1） Selecting a CLOB objectthrough the dblink:

set serveroutput on

declare

my_ad varchar(2000);

BEGIN

SELECT obj INTO my_ad FROM test@torem where id=1;

dbms_output.put_line(my_ad);

END;

我这里的测试环境是CBLOB，示例如下：

SQL> declare

2 my_ad varchar(2000);

3 BEGIN

4 SELECT text INTO my_ad FROMlob1@lob_link where rownum=1;

5 dbms_output.put_line(my_ad);

6 END;

package STANDARD AUTHIDCURRENT_USER is -- careful onthis line;

SED edit occurs!

PL/SQL procedure successfully completed.

--这里输出了我们CLOB里的内容。

（2）Selecting a BLOB object through thedblink:

declare

my_ad raw(50);

BEGIN

SELECT obj INTO my_ad FROM test2@torem where id=1;

END;

3.2 使用物化视图

MOS 文章： ORA-1406: Fetched Column Value was Truncated When Selecting RemoteColumn into Local BLOB Variable [ID 459557.1]

在3.1 中，我们可以使用本地变量来接收dblink中LOB的值，但是这个方法是有限制的。当我们接收的LOB 字段值大于32KB值，就会报如下错误：

ORA-01406 :fetched column value was truncated

"If the LOBis greater than 32KB - 1 in size, then PL/SQL will raise a truncation error andthe contents of the buffer are undefined."

This means thatwe are not able to retrieve BLOBs columns greater than 32KB - 1 in size througha database link.

The restrictionstill holds good for 11g from Oracledocumention SecureFiles and LargeObjects Developer's Guide

3.2.1 测试LOB字段长度

--在远程端创建过程：

create or replace procedure get_bloblengthas

blob_loc blob;

blob_length number;

begin

select <lob_column>

into blob_loc

from <table_name>

where name ='<critira>';

blob_length := dbms_lob.getlength(blob_loc);

dbms_output.put_line('Length of the Column : ' || to_char(blob_length));

end;

--在本地调用过程：

exec get_bloblength@repb

如果返回值大于32KB，我们就可以使用物化视图了。

3.2.2 创建物化视图

SQL> create materializedview lobmv1 as select line,text from lob1@lob_link;

--查询物化视图：CLOB正常显示

SQL> set wrap off;

SQL> select * from lobmv where rownum<5;

LINE TEXT

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

8 type NUMBER is NUMBER_BASE;

9 subtype FLOAT is NUMBER; --NUMBER(126)

11 subtype "DOUBLEPRECISION" is FLOAT;

3.3 将含有LOB字段的表复制到本地的全局临时表

我们在本地创建一张和dblink远程端相同的全局临时表，然后在查询临时表：

--创建临时表：

create global temporary table lob2

(

line number primary key,

text clob

)

on commit delete rows;

--插入数据：

SQL> insert into lob2 select line,text from lob1@lob_link;

499 rows created.

SQL> select * from lob2 where rownum<5;

LINE TEXT

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

8 type NUMBER is NUMBER_BASE;

9 subtype FLOAT is NUMBER; -- NUMBER(126)

11 subtype "DOUBLE PRECISION" is FLOAT;

SQL> commit;

Commit complete.

SQL> select * from lob2 where rownum<5;

no rows selected

--提交之后数据就被删除了，这个是临时表的属性。