ITPub博客

首页 > 数据库 > Oracle > Oracle DBLink 访问Lob 字段 ORA-22992 解决方法

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

Oracle 作者:yu5782647 时间:2014-05-27 19:54:55 0 删除 编辑
http://blog.csdn.net/tianlesoftware/article/details/7676863

这篇测试一下通过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


ORACLE LOB 大对象处理

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


Oracle LOB 详解

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


一.模拟问题


1.1 在实例1上操作:


创建含有blob 的测试表:

  1. /* Formatted on 2012/6/19 10:18:05 (QP5 v5.185.11230.41888) */
  2. CREATE TABLE lob1
  3. (
  4. line NUMBER primary key,
  5. text CLOB
  6. );
  7. INSERT INTO lob1
  8. SELECT distinct line, text FROM all_source where rownum<500;
  9. SELECT segment_name,
  10. segment_type,
  11. tablespace_name,
  12. SUM (bytes) / 1024 / 1024 || 'M' AS "SIZE"
  13. FROM user_segments
  14. WHERE segment_name = 'LOB1'
  15. GROUP BY segment_name, segment_type, tablespace_name;



LOB 表的信息如下:

  1. SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE
  2. --------------- ------------------ ------------------------------ ----------
  3. LOB1 TABLE SYSTEM 9M
  4. SQL> set wrap off;
  5. SQL> select * from lob1 where rownum=1;
  6. LINE TEXT
  7. ---------- ---------------------------------------------------------------------
  8. 1 package STANDARD AUTHID CURRENT_USER is -- care


1.2 在实例2上操作


创建DBLINK:

  1. CREATE PUBLIC DATABASE LINK lob_link CONNECT TO dave IDENTIFIED BY dave
  2. USING '(DESCRIPTION =
  3. (ADDRESS_LIST =
  4. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.222)(PORT = 1521))
  5. )
  6. (CONNECT_DATA =
  7. (SERVICE_NAME = dave)
  8. )
  9. )';
  10. SQL> select * from v$version;
  11. BANNER
  12. ----------------------------------------------------------------
  13. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
  14. PL/SQL Release 10.2.0.1.0 - Production
  15. CORE 10.2.0.1.0 Production
  16. TNS for Linux: Version 10.2.0.1.0 - Production
  17. NLSRTL Version 10.2.0.1.0 - Production
  18. SQL> select count(*) from lob1@lob_link;
  19. COUNT(*)
  20. ----------
  21. 58228


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

  1. SQL> select * from lob1@lob_link where rownum=1;
  2. ERROR:
  3. ORA-22992: cannot use LOB locators selected from remote tables
  4. no rows selected
  5. [oracle@localhost ~]$ oerr ora 22992
  6. 22992, 00000, "cannot use LOB locators selected from remote tables"
  7. // *Cause: A remote LOB column cannot be referenced.
  8. // *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 存入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;

/

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;
/


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
into blob_loc
from
where name ='';
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远程端相同的全局临时表,然后在查询临时表:

  1. --创建临时表:
  2. create global temporary table lob2
  3. (
  4. line number primary key,
  5. text clob
  6. )
  7. on commit delete rows;
  8. --插入数据:
  9. SQL> insert into lob2 select line,text from lob1@lob_link;
  10. 499 rows created.
  11. SQL> select * from lob2 where rownum<5;
  12. LINE TEXT
  13. ---------- ---------------------------------------------------------------------
  14. 5
  15. 8 type NUMBER is NUMBER_BASE;
  16. 9 subtype FLOAT is NUMBER; -- NUMBER(126)
  17. 11 subtype "DOUBLE PRECISION" is FLOAT;
  18. SQL> commit;
  19. Commit complete.
  20. SQL> select * from lob2 where rownum<5;
  21. no rows selected
  22. --提交之后数据就被删除了,这个是临时表的属性。
     








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

        分类:            Oracle Developer8656人阅读评论(0)收藏举报



这篇测试一下通过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


ORACLE LOB 大对象处理

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


Oracle LOB 详解

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


一.模拟问题


1.1  在实例1上操作:


创建含有blob 的测试表:

  1. /* Formatted on 2012/6/19 10:18:05 (QP5 v5.185.11230.41888) */ 
  2. CREATE TABLE lob1 
  3.    line   NUMBER primary key
  4.    text   CLOB 
  5. ); 
  6.  
  7. INSERT INTO lob1 
  8.    SELECT distinct line, text FROM all_source where rownum<500; 
  9.  
  10. SELECT segment_name, 
  11.          segment_type, 
  12.          tablespace_name, 
  13.          SUM (bytes) / 1024 / 1024 || 'M' AS "SIZE" 
  14.     FROM user_segments 
  15.    WHERE segment_name = 'LOB1' 
  16. GROUP BY segment_name, segment_type, tablespace_name; 



LOB 表的信息如下:

  1. SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME                SIZE 
  2. --------------- ------------------ ------------------------------ ---------- 
  3. LOB1            TABLE              SYSTEM                         9M 
  4.  
  5. SQL> set wrap off
  6. SQL> select * from lob1 where rownum=1; 
  7.  
  8.    LINE TEXT 
  9. ---------- --------------------------------------------------------------------- 
  10.    1 package STANDARD AUTHID CURRENT_USER is              -- care 


1.2 在实例2上操作


创建DBLINK:

  1. CREATE PUBLIC DATABASE LINK lob_link CONNECT TO dave IDENTIFIED BY dave 
  2. USING '(DESCRIPTION = 
  3.           (ADDRESS_LIST = 
  4.            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.222)(PORT = 1521)) 
  5.         ) 
  6.            (CONNECT_DATA = 
  7.          (SERVICE_NAME = dave) 
  8.      ) 
  9.   )'; 
  10.  
  11. SQL> select * from v$version; 
  12.  
  13. BANNER 
  14. ---------------------------------------------------------------- 
  15. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
  16. PL/SQL Release 10.2.0.1.0 - Production 
  17. CORE    10.2.0.1.0      Production 
  18. TNS for Linux: Version 10.2.0.1.0 - Production 
  19. NLSRTL Version 10.2.0.1.0 - Production 
  20.  
  21. SQL> select count(*) from lob1@lob_link; 
  22.   COUNT(*) 
  23. ---------- 
  24.      58228 


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

  1. SQL> select * from lob1@lob_link where rownum=1; 
  2. ERROR: 
  3. ORA-22992: cannot use LOB locators selected from remote tables 
  4.  
  5. no rows selected 
  6.  
  7. [oracle@localhost ~]$ oerr ora 22992 
  8. 22992, 00000, "cannot use LOB locators selected from remote tables" 
  9. // *Cause:  A remote LOB column cannot be referenced. 
  10. // *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 存入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;

/

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
into blob_loc
from 
where name ='';
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远程端相同的全局临时表,然后在查询临时表:

  1. --创建临时表: 
  2. create global temporary table lob2 
  3.    line   number primary key
  4.    text   clob 
  5. on commit delete rows
  6.  
  7. --插入数据: 
  8. SQL> insert into lob2 select line,text from lob1@lob_link; 
  9. 499 rows created. 
  10.  
  11. SQL> select * from lob2 where rownum<5; 
  12.       LINE TEXT 
  13. ---------- --------------------------------------------------------------------- 
  14.          5 
  15.          8   type NUMBER is NUMBER_BASE; 
  16.          9   subtype FLOAT is NUMBER; -- NUMBER(126) 
  17.         11   subtype "DOUBLE PRECISION" is FLOAT
  18.  
  19. SQL> commit
  20. Commit complete. 
  21.  
  22. SQL> select * from lob2 where rownum<5; 
  23. no rows selected 
  24. --提交之后数据就被删除了,这个是临时表的属性。 





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

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

Skype:  tianlesoftware

QQ:              tianlesoftware@gmail.com

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

更多1




顶 1 踩 0
主题推荐 oracleoracle10g测试数据object 猜你在找 Oracle LOB 详解 Oracle Optimizer CBO RBO 微信公众平台应用开发框架sophia设计不足(1) IOS 企业版发布后,用户通过sarafi浏览器安装无效的解决方案 对 Oracle 备份与恢复 的补充说明 批处理 自动修改 IP 地址 关于在MySQL 、 VC、 JSP 中使用UTF-8解决中文生僻字乱码的问题 Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1] mybatis--面向接口编程 Oracle11g 统计信息(一)-----统计信息自动收集任务















查看评论

  暂无评论

* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场






TOP



.tag_list { background: none repeat scroll 0 0 #FFFFFF; border: 1px solid #D7CBC1; color: #000000; font-size: 12px; line-height: 20px; list-style: none outside none; margin: 10px 2% 0 1%; padding: 1px; } .tag_list h5 { background: none repeat scroll 0 0 #E0DBD3; color: #47381C; font-size: 12px; height: 24px; line-height: 24px; padding: 0 5px; margin: 0; } .tag_list h5 a { color: #47381C; } .classify { margin: 10px 0; padding: 4px 12px 8px; } .classify a { margin-right: 20px; white-space: nowrap; }
    个人资料
    • 访问:5959673次
    • 积分:61706分
    • 排名:第6名
    • 原创:966篇
    • 转载:85篇
    • 译文:1篇
    • 评论:1285条
    Oracle 组织


    安徽DBA俱乐部
    • 因Dave 回老家发展,所以筹划了安徽DBA俱乐部,俱乐部整合安徽地区的IT资源,现有成员已经包含安徽大部分IT公司,俱乐部除了资讯信息分享之外,也会定期举行线下活动。欢迎安徽地区的DBA 加入.

      QQ群:363076776,备注,加群必须注明籍贯,该群只对安徽地区开放。

    • 网址:www.ahdba.com

    CNDBA 交流QQ群
    • 注意:加群必须注明表空间和数据文件关系
    • 不要重复加群
    • ----------------------
    • CNDBA_1: 104207940(满)
    • CNDBA_2: 62697716
    • CNDBA_3: 283816689
    • CNDBA_4: 63306533
    • CNDBA_5: 62697850
    • CNDBA_6: 62697977
    • CNDBA_7: 142216823
    Dave's Links
    最新评论








公司简介|招贤纳士|广告服务|银行汇款帐号|联系方式|版权声明|法律顾问|问题报告|合作伙伴|论坛反馈 网站客服 杂志客服 微博客服 400-600-2320 京 ICP 证 070598 号 北京创新乐知信息技术有限公司 版权所有 江苏乐知网络技术有限公司 提供商务支持 Copyright © 1999-2014, CSDN.NET, All Rights Reserved GongshangLogo






量子统计

















来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24577884/viewspace-1171250/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-05-15

  • 博文量
    32
  • 访问量
    222995