This discussion is archived
1 2 Previous Next 28 Replies Latest reply: May 1, 2012 1:01 PM by gpoz RSS

DBLink problem ORA-22992

398072 Newbie
Currently Being Moderated
Hi all !!

I'm using an active dblink between db1 and db2 named orcl. Database db2 in schema usr has a couple of tables A and B looking like that:

Table A
column id number, primary key
column id_ref, number, not null,
column eventdate date not null
column longfield CLOB

Table B
column id_ref, number, primary key
column description varchar2(256) not null

In database db1 I ran the following query:

SELECT ID, ID_REF, EVENTDATE
FROM A@ORCL

and It works fine... But, If I try to run the following query:

SELECT A.ID, A.ID_REF, A.EVENDATE, B.DESCRIPTION
FROM A@ORCL A
INNER JOIN B@ORCL B
ON (A.ID_REF = B.ID_REF);

I get the following message:

ERROR in line 1:
ORA-22992: cannot use LOB locators selected from remote tables

db1 is an Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production running over solaris

db2 is an Oracle Database 10g Release 10.1.0.4.0 - 64bit Production With the Real Application Clusters option, running over solaris

How can I solve this problem ? Any help will be appreciated

Thanks in Advance
  • 1. Re: DBLink problem ORA-22992
    Justin Cave Oracle ACE
    Currently Being Moderated
    Can you create a view on the remote database that joins A & B? If so, try referring to that remote view instead.

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 2. Re: DBLink problem ORA-22992
    398072 Newbie
    Currently Being Moderated
    Thanks for your answer...

    But, look at this curious thing: I re-write the query as follows:

    SELECT A.ID, A.ID_REF, A.EVENDATE, B.DESCRIPTION
    FROM A@ORCL A, B@ORCL B
    WHERE A.ID_REF = B.ID_REF;

    and it works fine... It seems like Oracle don't like the ANSI SQL92...
  • 3. Re: DBLink problem ORA-22992
    422864 Newbie
    Currently Being Moderated
    Very strange for me! Any ideas from Oracle mans?
  • 4. Re: DBLink problem ORA-22992
    476442 Newbie
    Currently Being Moderated
    Hi,

    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.

    Any input is appreciated.
  • 5. Re: DBLink problem ORA-22992
    560031 Newbie
    Currently Being Moderated
    Hi,
    try to create a synonym for remote table.

    Eg:

    create synonym remote_dual for dual@dblink;

    select nvl2(1,2,3) from remote_dual;

    This resolved for me.
  • 6. Re: DBLink problem ORA-22992
    61841 Newbie
    Currently Being Moderated
    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.

    Thanks,

    Jatin
  • 7. Re: DBLink problem ORA-22992
    bjeffrie Journeyer
    Currently Being Moderated
    I ran in to an ORA-22992 yesterday and thankfully these posts were here to give me clue.
    Synonyms would be a problem for my application, but using decode worked fine. Totally crazy.

    I.e., NVL2(1,2,3) --> DECODE(1,null,2,3)

    Hope that helps someone else ..., -Brian.
  • 8. Re: DBLink problem ORA-22992
    69529 Newbie
    Currently Being Moderated
    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 doubt how to select a LOB using DBLINK.

    Ramagopal
  • 9. Re: DBLink problem ORA-22992
    400137 Journeyer
    Currently Being Moderated
    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.

    Generally, you can't select LOBs from a remote table using database link.

    However in some case it is possible (for example creating table using CTAS).

    Are you getting this error during the creation of the Materialized View or during executing a SELECT statement?
  • 10. Re: DBLink problem ORA-22992
    622946 Newbie
    Currently Being Moderated
    I too ran into the error when using NVL2.

    After rebuilding my statement to a CASE WHEN .. IS NOT NULL THEN .. ELSE .. my query ran like a charm again.
  • 11. Re: DBLink problem ORA-22992
    631984 Newbie
    Currently Being Moderated
    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.
  • 12. Re: DBLink problem ORA-22992
    190639 Newbie
    Currently Being Moderated
    well you certainly saved me a lot of effort. Thanks,
  • 13. Re: DBLink problem ORA-22992
    32784 Newbie
    Currently Being Moderated
    You saved me lot of trouble too! Yesterday I met the same problem. Now it's solved, thanks to this entry.
    Thanx, Paul.
  • 14. Re: DBLink problem ORA-22992
    669703 Newbie
    Currently Being Moderated
    This is a good way of doing it.

    It only works if you are inserting it into a table though, you still can't SELECT it directly.

    I even tried using a function to return it but the Limit is ONLY 4000 for a VARCHAR return.

    FUNCTION f_EmailBodyClobToVar2(p_nEHI_PK IN TAR_EMAIL_HISTORY.ehi_pk%TYPE)
    RETURN VARCHAR2
    IS
    v_email_body_v2 VARCHAR2(32767);

    BEGIN

    SELECT SUBSTR(email_body,1,32767)
    INTO v_email_body_v2
    FROM tar_email_history
    WHERE ehi_pk = p_nEHI_PK;

    RETURN v_email_body_v2;

    END f_EmailBodyClobToVar2;
1 2 Previous Next