签到成功

知道了

CNDBA社区CNDBA社区

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

2016-11-25 16:01 4666 0 原创 sql开发
作者: Dave

 

 

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

 

Oracle DBLink

http://blog.csdn.net/tianlesoftware/article/details/4698642

 

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

http://blog.csdn.net/tianlesoftware/article/details/6160196

https://www.cndba.cn/dave/article/1483
https://www.cndba.cn/dave/article/1483

 

ORACLE LOB 大对象处理

http://blog.csdn.net/tianlesoftware/article/details/5070981

 

Oracle LOB 详解

http://blog.csdn.net/tianlesoftware/article/details/6905406

 

一.模拟问题

 

1.1  在实例1上操作:

 https://www.cndba.cn/dave/article/1483

创建含有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                         9MSQL> 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 daveUSING '(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 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE    10.2.0.1.0      ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - ProductionSQL> 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 tablesno rows selected[oracle@localhost ~]$ oerr ora 2299222992, 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.

 https://www.cndba.cn/dave/article/1483

 (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 存入charCBob或者rawBLob本地变量

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可以被本地的charraw 类型变量接收。

 

(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; https://www.cndba.cn/dave/article/1483

/

 7 

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; 

 https://www.cndba.cn/dave/article/1483

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 https://www.cndba.cn/dave/article/1483

 https://www.cndba.cn/dave/article/1483

"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字段长度

 

--在远程端创建过程:

https://www.cndba.cn/dave/article/1483

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

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

        5

        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---------- ---------------------------------------------------------------------         5         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--提交之后数据就被删除了,这个是临时表的属性。

 

 

 

 

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

版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!

Skype: tianlesoftware

QQ:              tianlesoftware@gmail.comhttps://www.cndba.cn/dave/article/1483

Email:   tianlesoftware@gmail.com

Blog:     http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

 

 

-------加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请----

DBA1 群:62697716(满);   DBA2 群:62697977(满)  DBA3 群:62697850(满)  

DBA 超级群:63306533(满);  DBA4 群:83829929   DBA5群: 142216823

DBA6 群:158654907    DBA7 群:172855474   DBA总群:104207940

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1902
    原创
  • 2
    翻译
  • 456
    转载
  • 131
    评论
  • 访问:3641814次
  • 积分:3137
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群