My Oracle Support Banner

Workaround for ORA-22992 (Doc ID 796282.1)

Last updated on NOVEMBER 06, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 9.2 to 12.1]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.


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

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 :

-- 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'); 

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

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


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.