利用全局临时表解决database link不能使用的问题.docx

早上睡不着起来做automation的模板，利用database link，从标准数据库抽取样表到当前数据库做测试时，一切顺利，如果表含有long类型的字段，使用quest_spc_bruce.substr_of_long即可，但是，对于LOB类型的字段发现不行，需另找方法。下面说下利用Oracle DBLink 访问Lob 字段 ORA-22992 解决方法。推荐使用全局临时表的方法。一模拟问题在实例1上操作，创建含有blob 的测试表：sql view plaincopy/* 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 set wrap off; SQL select * from lob1 where rownum=1; LINE TEXT - - 1 package STANDARD AUTHID CURRENT_USER is - care 在实例2上操作，创建DBLINK：sql view plaincopyCREATE 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 count(*) from lob1lob_link; COUNT(*) - 58228 这边查询总记录数正常。但是当我们查询具体LOB字段里的内容的时候，就会报错，如下：sql view plaincopySQL select * from lob1lob_link where rownum=1; ERROR: ORA-22992: cannot use LOB locators selected from remote tables no rows selected oraclelocalhost $ 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 上的相关说明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 table1remote_site; 2. INSERT INTO lobtable select type1.lobattr from table1remote_site; 3. SELECT dbms_lob.getlength(lobcol) from table1remote_site;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 dualtest;Modified SQL: selectto_char(nvl2(a,b,c) from dualtest;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.三解决方法一种是将接收的lob 存入char（CBob）或者raw（BLob）本地变量MOS文章：Ora-22992 has a workaround in 10gR2 ID 436707.1Workaround for ORA-22992 ID 796282.1Starting 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 testtorem where id=1; dbms_output.put_line(my_ad); END; / 我这里的测试环境是CBLOB，示例如下：SQL declare2 my_ad varchar(2000);3 BEGIN4 SELECT text INTO my_ad FROMlob1lob_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); BEGINSELECT obj INTO my_ad FROM test2torem where id=1; END; / 二是使用物化视图MOS 文章： ORA-1406: Fetched Column Value was Truncated When Selecting RemoteColumn into Local BLOB Variable ID 459557.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 Developers Guide先测试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_bloblengthrepb如果返回值大于32KB，我们就可以使用物化视图了。SQL create materializedview lobmv1 as select line,text from lob1lob_link;-查询物化视图：CLOB正常显示SQL set wra