OraFAQ Forum: SQL & PL/SQL » ORA-22992: cannot USE LOB locators selected FROM remote TABLES

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22992: cannot USE LOB locators selected FROM remote TABLES (ORACLE 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-22992: cannot USE LOB locators selected FROM remote TABLES [message #542737]

Fri, 10 February 2012 00:33

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

Report message to a moderator

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

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So do not select the LOB remote column.

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68776
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 30 16:38:38 CDT 2026