ORA-22992: cannot use LOB locators selected from remote tables Oracle 11g
January 8, 2015 Leave a comment
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.