We just migrated an application schema from Oracle 18.104.22.168 to 22.214.171.124 in production. The dev, test and acceptance environments were already migrated and tested. All went fine in production aswell and we had no issues. Until a batchjob failed with the following error:
ORA-22992: cannot use LOB locators selected from remote tables
The batchjob is a plsqpl package that is kicked off every few minutes and worked fine.
Investigating the issue revealed a change in behaviour of using LOB’s through database links in release 11.2 (I didn’t test other releases).
create a table in a 9.2 and a 11.2 database with a clob column.
create table clob_test (clobcol clob);
In the client database (11.2) create two database links
create database link test92 connect to lob_test identified by lob_test using 'db92'; create database link test112 connect to lob_test identified by lob_test using 'db112';
insert into clob_test@test92 values ('testval'); 1 rows inserted. insert into clob_test@test92 select 'testselect' from dual; 1 rows inserted.
The insert statements work fine (as expected). Now let’s remote insert LOB’s into an 11.2 database.
insert into clob_test@test112 values ('testval'); 1 rows inserted. insert into clob_test@test112 select 'testselect' from dual; Error report: SQL Error: ORA-22992: Gebruik van LOB-locators uit externe tabellen is niet mogelijk. ORA-02063: Voorgaande line uit TEST112. 22992. 00000 - "cannot use LOB locators selected from remote tables" *Cause: A remote LOB column cannot be referenced. *Action: Remove references to LOBs in remote tables.
The second statement fails with an ORA-22992, while the first one still succeeds.
This is what the documentation has to say on using remote lobs: 11.2 Rules for using LOB’s
I did a quick search on MOS, but found no relevant information. In our case we rewrote the DML statement to exclude the LOB column, as it was updated separately later in the code anyway.Overzicht blogs