I have a strange problem. Our db is in 10g, we have a dblink to an 8i db.
Whenever i do an nvl2 func over this dblink like say "select nvl2(1,2m3) from dual@dblink " then i get the foll. error msg. :
ORA-22992 Cannot use LOB Locators used from remote tables.
This happened when i was trying to query a table which did not have any LOB field. When the NVL2 is removed, there is no problem.
Earlier when both the db where on 8i, there was no problem with NVL2.
We have created synonyms for the objects in remote database using DB Link. Now neither Informatica nor Cognos can see these synonyms. They can see synonyms that points to the local database. Any one have seen this and what is the solution? BTW, I tried granting DBA role to Synonym owner to make sure this is not permission issue.
I had the same problem in Oracle 9i.
select * from T@link
T has a column 'Notes' which is a CLOB. Avoiding select of that column solved my problem.
The query that works is:
select f1, f2, f3 from T@LINK -- selecting every column but 'NOTES'
I don't know if this is related, but we were also having a problem that was causing the ORA-22992 error, and the solution turned out to be surprisingly simple. A full day of searching the web didn't turn up this answer, but then one of our DBAs accidentally stumbled over something buried in some Oracle documentation that provided the answer.
We have a database table that contains a couple primary key fields (a varchar and an integer), plus a BLOB that holds Word documents. One of our programs needs to be able to connect to a remote Oracle instance and copy Word documents based on certain primary keys.
Our code first attempted to do that like this:
insert into [local Word doc table] ([key column1], [key column 2], [blob column])
values ('[key 1 literal]', [key 2 literal],
(select [blob column] from [Word doc table]@[remote instance]
where [keys = remote keys])
Attempting to execute that was giving us the "cannot use LOB locators selected from remote tables" error.
The documentation that our DBA turned up included a bunch of SQL examples of using remote BLOBs which he thought would be helpful. But what provided the solution was the sentence following the SQL examples: "In statements structured like the preceding examples, only standalone LOB columns are allowed in the select list".
I took that to mean that if you're going to access a BLOB on a remote database, then that BLOB column has to be the ONLY column you're referencing. So I broke our program's SQL up into this:
insert into [local Word doc table] ([key 1 col], [key 2 col], [blob col]) values
('[key 1]', [key 2], NULL)
update [local Word doc table] set [blob col] =
(select [blob col] from [Word doc table]@[remote instance]
where [keys = remote keys])
where [keys = local keys]
I was amazed to find that the above works like a charm. We've got a 100 meg Word document going from one Oracle instance to the other with no problem.
Since doing a Google search on "cannot use LOB locators selected from remote tables" turns this page up near the top of its list of links, I'm hoping that by posting this I can save another programmer somewhere the two or three days of banging your head against the screen that I just went though.