This discussion is archived
2 Replies Latest reply: Feb 8, 2007 7:28 AM by user500315 Branched to a new discussion. RSS

ORA-22992: cannot use LOB locators selected from remote tables

user500315 Newbie
Currently Being Moderated
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?
  • 1. Re: ORA-22992: cannot use LOB locators selected from remote tables
    18622 Newbie
    Currently Being Moderated
    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.

    Daniel
  • 2. Re: ORA-22992: cannot use LOB locators selected from remote tables
    user500315 Newbie
    Currently Being Moderated
    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 ...
    tablespace ...
    build immediate
    refresh fast
    on demand
    as select * from <table>@dblink)
    Then, if a select the new table created with a blob field, the data will be returned normally.