This discussion is archived
1 2 Previous Next 28 Replies Latest reply: May 1, 2012 1:01 PM by gpoz Go to original post RSS
  • 15. Re: DBLink problem ORA-22992
    229727 Newbie
    Currently Being Moderated
    Thanks a lot for the effort you took to document this solution.
    I was able to help a friend using this post.

    All the best.
  • 16. Re: DBLink problem ORA-22992
    680235 Newbie
    Currently Being Moderated
    Hi.
    Did you find a solution for selecting a BLOB?
    Thanks
    Peter
  • 17. Re: DBLink problem ORA-22992
    681974 Newbie
    Currently Being Moderated
    Hi There,

    I have tried the same steps as you have mentioned...

    First Step is Insert as follows

    insert into test017.attachments_blob (attachments_dbid, entity_dbid, entity_fielddef_id,data)
    select attachments_dbid, entity_dbid, entity_fielddef_id,NULL from dbg01.attachments_blob@cqltc.world WHERE entity_dbid = 33682270

    And the Update

    update test017.attachments_blob set data = (select data
    from dbg01.attachments_blob@cqltc.world WHERE entity_dbid = 33682270) where entity_dbid = 33682270

    But I am getting an error...

    SQL> update test017.attachments_blob set data = (select data
    2 from dbg01.attachments_blob@cqltc.world WHERE entity_dbid = 33682270) where entity_dbid = 33682
    270;
    from dbg01.attachments_blob@cqltc.world WHERE entity_dbid = 33682270) where entity_dbid = 33682270
    *
    ERROR at line 2:
    ORA-03001: unimplemented feature

    Can you help me pls.

    Thanks
    Gaurav
  • 18. Re: DBLink problem ORA-22992
    659137 Newbie
    Currently Being Moderated
    Another way to go if you don't want to implement "INSERT" and "UPDATE" approach is to create a cursor and peroform the insert with in the cursor.

    I have similar project where I have to insert to a table VIA DB_LINK and I keep getting ..
    ORA-22992: cannot use LOB locators selected from remote tables.
  • 19. Re: DBLink problem ORA-22992
    353151 Newbie
    Currently Being Moderated
    Are you getting this error during the creation of the Materialized View or during executing a SELECT statement?
    I am getting this error when trying to create a mview on a remote database, Only thing I can do using the database link is do a count(*), any attempt to access these clob columns over the DB link fails with this error.

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

    Any thoughts on how this can be resolved in a MLOG/MVIEW scenario, Oracle docs say we can replicate tables that have LOB's (with some restrictions), same way like the regular tables and all I am doing to copying a table which has CLOB columns.

    Appreciate your help.

    Thanks
  • 20. Re: DBLink problem ORA-22992
    353151 Newbie
    Currently Being Moderated
    Metalink had all the required info to fix this issue, just remove "BUILD DEFERRED" from the create mview statement and it works like a charm.

    --There is an error in the code as per Bug 4043461
    --sol: Create the Materialized View without using the 'build deferred' clause.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 21. Re: DBLink problem ORA-22992
    581157 Newbie
    Currently Being Moderated
    hi
    my idea is this:
    i must replicate data from dblink@remote to my db

    1. DB create table test .....
    1.DB table test = 2.DB table test
    my 1.DB is remote DB, but i copied all data from 1.DB to 2.DB
    I use 2.DB

    insert into test@remote (to 2.DB)
    select * from test (from 1.DB)
    where id=......

    plus triggers....

    This idea is not good, but it work
    TP
  • 22. Re: DBLink problem ORA-22992
    746213 Newbie
    Currently Being Moderated
    Another alternative (Oracle 11.2) is to do the following:

    SQL>create table message_detail_stg as select * from message_detail@SOURCE_LOCATION1

    Table created.

    SQL>select count(*) from message_detail_stg;

    COUNT(*)
    ----------------
    329809

    desc message_detail_stg
    Name Null? Type
    -------------------------------------- ------- ---------------------------------------------
    MSG_DETAIL_ID NOT NULL NUMBER(24)
    MSG_TEXT NOT NULL CLOB

    This is a little quicker than the insert/update alternative.

    Rex
  • 23. Re: DBLink problem ORA-22992
    875314 Newbie
    Currently Being Moderated
    I had a similar problem (both databases beeing 11.1.0.7.0) and the cause was a LOB column type created by a function (wm_concat) I used on non-LOB columns.
    Casting the "LOB" to some non-LOB type resolved the problem:

    remote_db:
    create view remote_view as
    select wm_concat(dummy) as dummy from dual

    local db:
    select * from remote_view@remote_db -- caused "ORA-22992: cannot use LOB locators selected from remote tables"

    solution:
    select cast(dummy as varchar2(4000)) from remote_view@remote_db -- runs fine :-)

    Kind regards
    Andreas
  • 24. Re: DBLink problem ORA-22992
    501930 Newbie
    Currently Being Moderated
    First, let me tell you many thanks as you saved me (and many others) lots of time.

    Second...

    Just wanted to mention that if somebody has this problem with SELECT (not INSERT-SELECT) , then the following works fine:

    select [key 1], , [non blob col1], ..., [non blob col N], *(select [blob col] from [remote table] t1 where t1.rowid = t2.rowid)*
    from [remote table] t2
    where ...

    Hope it helps.

    Edited by: Tauruz on Oct 24, 2011 12:24 AM
  • 25. Re: DBLink problem ORA-22992
    900838 Newbie
    Currently Being Moderated
    hi,

    this still gives me ORA-22992: cannot use LOB locators selected from remote tables error.

    What do you mean with by key 1 in your select
    select *[key 1]*, , [non blob col1], ..., [non blob col N], (select [blob col] from [remote table] t1 where t1.rowid = t2.rowid)
    from [remote table] t2
    where ...

    thanks,
    Shobhna
  • 26. Re: DBLink problem ORA-22992
    900838 Newbie
    Currently Being Moderated
    Additional info - My remote table has composite primary key
  • 27. Re: DBLink problem ORA-22992
    ManishOraclePandey Newbie
    Currently Being Moderated
    Thank you very much...You really saved my life....It was really helpful....
  • 28. Re: DBLink problem ORA-22992
    gpoz Newbie
    Currently Being Moderated
    I ran into this problem today, and my solution didn't use decode or synonyms or anything else, except CAST. I was creating a view by selecting null (not a null value in a column) and some other non-null column values (but this made no diff to have or not) from a table accessed by a dblink, where the table had a CLOB defined in it. Nothing about the CLOB was accessed anywhere in the code. This is the exact structure of the query that showed the problem:

    CREATE OR REPLACE VIEW dxs_v AS
    (
    SELECT
    null qq,
    1 zz
    FROM
    (
    schema.table_no_clob@remote_inst yyy
    JOIN
    schema.table_with_clob@remote_inst zzz (same remote instance)
    ON zzz.col1 = yyy.col1)
    )
    /

    comment the SELECT line "null", and it worked, uncomment, and it fails with 22992. Interestingly, I also got around this without changing the "null" line if I changed the query to NOT BE ANSI JOINS.

    for my particular circumstance, I had to deliver a literal instead of null to make this move forward.

    cast(null as char) became cast('' as char)
    cast(null as raw(16)) became cast('00' as raw(16))

    thank you, David S, (PS, if any gurus can fill in any blanks and tell us the why behind all this, I'd sure be glad to learn -- maybe I found a bug!)
1 2 Previous Next