Workaround for ORA-22992

(Doc ID 796282.1)

Last updated on JUNE 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 12.1.0.2 [Release 9.2 to 12.1]
Information in this document applies to any platform.



Symptoms

This article provides a workaround for error ORA-22992 which occurs when selecting LOB data over database links.  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, if not the query will fail with the following error:

ORA-65510: Distributed LOB operations are not supported on pre-12.2 databases.

In database versions prior to 12.2.0.1:

SQL> select text from Table@DBlink;
select text from Table@DBlink
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

<>The following test case should explain more details about the issue, it assumes that we have two databases A, B both are running 12.1.0.2 :


-- On the target, instance B in our example : 

conn user/pass 
create table tab1(col1 number, col2 clob); 
insert into tab1 values(101,'This is test data'); 
insert into tab1 values(102, 'aims to test the LOB'); 
commit;  


-- Now on the local instance, A : 

conn user/pass 
create database link link_to_B connect to user  
identified by pass using 'B'; -- B is the TNS alias of Database B 

select * from test@torem; 

ERROR at line 1: 
ORA-22992: cannot use LOB locators selected from remote tables 


--The same issue will occur even if we have used PLSQL block : 

set serveroutput on 

declare 
var1 varchar(6000); 
BEGIN 
SELECT col2 INTO var1 FROM tab1@link_B where col1=101; 
dbms_output.put_line(var1); 
END; 
/  
ERROR at line 4: 
ORA-22992: cannot use LOB locators selected from remote tables 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms