icon-arrow icon-check icon-mail icon-phone icon-facebook icon-linkedin icon-youtube icon-twitter icon-cheveron icon-download icon-instagram play close close icon-arrow-uturn icon-calendar icon-clock icon-search icon-chevron-process icon-skills icon-knowledge icon-kite icon-education icon-languages icon-tools icon-experience icon-coffee-cup
Werken bij Integration & Application Talents
Blog 27/06/2012

ORA-22992 after migration from 9.2 to 11.2

Migration

We just migrated an application schema from Oracle 9.2.0.8 to 11.2.0.3 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

Geen reacties

Geef jouw mening

Reactie plaatsen

Reactie toevoegen

Jouw e-mailadres wordt niet openbaar gemaakt.

Geen HTML

  • Geen HTML toegestaan.
  • Regels en alinea's worden automatisch gesplitst.
  • Web- en e-mailadressen worden automatisch naar links omgezet.

Wil je deel uitmaken van een groep gedreven en ambitieuze experts? Stuur ons jouw cv!