Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22992: cannot USE LOB locators selected FROM remote TABLES (ORACLE 10G)
ORA-22992: cannot USE LOB locators selected FROM remote TABLES [message #542737] Fri, 10 February 2012 00:33 Go to next message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hi,

I have facing the below problem while the executing the query,
pls help


SELECT tmp1. *
FROM
tmp1
LEFT OUTER JOIN tmp2 ON tmp1.NUM_MAIN_ID = tmp2.NUM_MAIN_ID;

ORA-22992: cannot USE LOB locators selected FROM remote TABLES


Tmp1 TABLE as CLOB field.


Tmp1 is remote TABLE, for which i have given dblink.



Regards

Re: ORA-22992: cannot USE LOB locators selected FROM remote TABLES [message #542743 is a reply to message #542737] Fri, 10 February 2012 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So do not select the LOB remote column.

Regards
Michel
Re: ORA-22992: cannot USE LOB locators selected FROM remote TABLES [message #542781 is a reply to message #542743] Fri, 10 February 2012 03:21 Go to previous messageGo to next message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hi,

fine thanks, it works


SELECT tmp1. NUM_MAIN_ID
FROM
tmp1
LEFT OUTER JOIN tmp2 ON tmp1.NUM_MAIN_ID = tmp2.NUM_MAIN_ID;


If i format the query by

SELECT tmp1. NUM_MAIN_ID, null as vch_main_name
FROM
tmp1
LEFT OUTER JOIN tmp2 ON tmp1.NUM_MAIN_ID = tmp2.NUM_MAIN_ID;

pls help in this, because i'm using this in union query

now returning the same error

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



regards

krish
Re: ORA-22992: cannot USE LOB locators selected FROM remote TABLES [message #542786 is a reply to message #542781] Fri, 10 February 2012 03:27 Go to previous message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I can't believe this happens when you just add a NULL to select column list.

Use SQL*Plus and copy and paste your session, the WHOLE session including table descriptions and so on.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals (for both databases).

Regards
Michel
Previous Topic: number format to show scientific notation
Next Topic: Merger of Sequences (merged 2)
Goto Forum:
  


Current Time: Sat Aug 16 03:44:43 CDT 2025