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

Some details:

    Database Version: 11g
    Database Names: DB1, DB2
    Database link name: dblink (created in DB1)

You may need to retrieve clob data via database link using the following command:

SQL> select *
from test@dblink

Raises ORA-22992 error.

To resolve this, follow the steps bellow.

Let’s start from the beginning:

1. In database DB2, create test table with clob column and insert data.

SQL> create table test(
text clob
);

SQL> insert into test(text)
values(‘Some test data was inserted.’);

SQL> commit;

2. In database DB1:

SQL> create database link dblink connect to dbusername identified by dbusernamepasswd using ‘DB2’;

3.  If you run the following select, you will get ORA-22992 error.

SQL> select *
from test@dblink;

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

…… Solution

In DB1 create global temporary table with the same structure as test table, let’s name it as testglobal.

SQL> create global temporary table testglobal
on commit delete rows
as select * from test@dblink;

For now , our global temporary table doesn’t contain any rows. Because data was automatically committed during creation.

SQL> select * from testglobal;
no rows selected

Let’s insert data into it.

SQL> insert into testglobal
select y from test@dblink;

Now, We have the CLOB data locally, in  table until commit.

SQL> select * from testglobal ;

col1
——————————–
Some test data was inserted.