Thursday, February 07, 2008

ORA-22992 Oracle hell

Welcome to my little corner of hell today. A third party vendor changed their tables structures recently, adding a CLOB field to some of their tables. Like most hard working technical folk we have had to write a lot of new code to provide functionality the third party vendor does not and will not provide. So some perfectly good ANSI standard SQL suddenly puked out a "ORA-22992: cannot use LOB locators selected from remote tables" error.

I'm not using any LOB's in the queries so why in the blue heck does Oracle want to their LOB locators? I did a search and thankfully found where someone on Oracle's forums just tried to use the WHERE clause to join instead of the JOIN statement and it worked. I tried it on and sure enough, it worked. It goes something like this:

crap1 VARCHAR2(10)
crap2 DATE

poop1 VARCHAR2(10)
poop2 DATE
poop3 CLOB

SELECT crap2,poop2
FROM table1@remote JOIN table2@remote ON crap1 = poop1;

ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

SELECT crap2,poop2
FROM table1@remote, table2@remote WHERE crap1 = poop1;

glorious data

There is nothing we geeks hate more than code that should work but doesn't. Especially when the people we work for are paying five, six, seven or more figures for this software. The problem was originally noticed over two and a half years ago!



Anonymous said...

Search metalink. There is an open bug, no resolution as of yet. Workaround is to use Oracle join syntax (i.e. where a = b instead of join on a = b).

Grouchy said...

I just got metalink access this week and you are right, there is an open bug out there and I have already done the work-around.