Hello pals, i'm trying to create a materialized view on a remote database, both on 10G R2.2, the table i'm trying to replicate has a BLOB column and i receive the error ORA-22992. The replication i need to do is in FAST mode, but i can't even query the table in the remote database: select * from table@dblink.
Can you help me please with this matter?
Here's what I get from the errors manual (10.2):
ORA-22992: cannot use LOB locators selected from remote tables
Cause: A remote LOB column cannot be referenced.
Action: Remove references to LOBs in remote tables.
It looks like what you're trying simply can't be done. You'll need a workaround. Tom Kyte found that a view "masking" this LOB from the DB link would do the trick (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5322964030684). Let us know if that doesn't work.
The problem was solved this way:
Effectively, i can't directly select a table from a remote database if it has a blob field, e.g.: (select * from <table>@dblink ).
However, i sure can create a materialized view from a remote table like this and i have not any problem. e.g.
(create materialized view/snapshot ...
as select * from <table>@dblink)
Then, if a select the new table created with a blob field, the data will be returned normally.