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

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

    user500315
      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
          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
            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.