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

一、 问题背景

开发反馈用dblink查询视图遇到以下报错，报错比较明显，不能通过dblink访问远程表（或视图）的lob字段，想知道解决方法。

二、 解决方法

1. 升级源和目标库版本

根据Oracle 文档 ID 796282.1

• 10.2之前版本不支持通过dblink访问lob字段，就连通过plsql变量也不支持
• 10.2~12.1，还是不支持通过dblink访问lob字段，但是支持了通过plsql变量中转（见方法2）
• 12.2及以上版本，该问题不再存在（源和目标端都在12.2以上）

This limitation does not longer exists in Oracle 12.2 and above.  In that case, both source and target must be running Oracle 12.2.0.1 or above

2. 使用函数或存储过程（10.2之前）

原理是将lob对象在本地就转成varchar类型，然后通过dblink访问转换后的类

--On remote (B) :
create or replace function lobconverter(lobin in clob) return varchar2 is
  Result varchar2(5000);
begin
  result:=lobin;
  return(Result);
end lobconverter;

-- Now we need to ensure that this function is being called on the remote site to achieve that we can simply create a view like the following :
create view testv as select col,lobconverter(col2) col2 from tab1;

-- on the local database A :
-- Instead of selecting from the table, simply select from the view :
select * from tab1@link_to_b;

 COL1
----------
COL2
--------------------
         101
This is test data

         102
aims to test the LOB

3. 使用plsql（10.2~12.1）

它的原理是plsql中varchar类型支持的最大长度能到30000以上，将lob转为varchar后不会报错长度不够。

• Selecting a CLOB object through the dblink

--1.1 From the remote database:
create table <table>(id number, obj clob);
insert into <table> values(1,'sdfsdfsfd');
insert into <table> values(2, 'sdfsdfsfdvfgdfvgdfvdf');
commit;

--1.2 From the local database:
create database link <dblink> using 'identification of the remote database in the tnsnames.ora';

set serveroutput on
declare
my_ad varchar(6000);
BEGIN
SELECT obj INTO my_ad FROM <table>@<dblink> where id=1;
dbms_output.put_line(my_ad);
END;
/

• Selecting a BLOB object through the dblink

--1.1 From the remote database:
create table <table>(id number, obj blob);
insert into <table> values(1,empty_blob());
insert into <table> values(2,empty_blob());
commit;

--1.2 From the local database:
create database link <dblink> using 'identification of the remote database in the tnsnames.ora';

set serveroutput on
declare
my_ad raw(50);
BEGIN
SELECT obj INTO my_ad FROM <table>@<dblink> where id=1;
END;
/

4. 目标库使用临时表

“临时表”指的可以是ctas建的普通表，也可以是Oracle的全局临时表。

• ctas普通表

create table tmp_test as select * from table_xxx@space where rownum <= 10;
select * from tmp_test;

• 全局临时表（感觉最终还是类似ctas，有点多余）

-- 现在目标端建立目标表结构：
create /*source only*/ table mingshuo.tmp_ms_19031403 as select * from  backupwt.tmp_ms_19031403@dblk_e1 where 1=0;

--目标端建立全局临时表：
create /*source only*/ global temporary table mingshuo.gb_temp_tab
(
  id             NUMBER(20) not null,
...
) on commit delete rows;

insert into mingshuo.gb_temp_tab select * from backupwt.tmp_ms_19031403@dblk_e1;
--注意将数据插入到临时表中后不要提交，否则数据没有了
--将临时表中的数据插入到目标表中：
insert into mingshuo.tmp_ms_19031403 select * from mingshuo.gb_temp_tab;
commit;

5. 使用物化视图

就是目标端不建普通视图而建物化视图，不过物化视图做不到实时，而且频繁刷新压力可能很大，需要慎重。

参考

Ora-22992 workaround (文档 ID 436707.1)

Workaround for ORA-22992 (文档 ID 796282.1)

Oracle学习笔记：ORA-22992 cannot use LOB locators selected from remote tables - Hider1214 - 博客园

oracle ORA-22992问题_conghe6716的博客-CSDN博客