0

I m accessing remote database through DBLINK, but when trying to fire select query to fetch image stored in BLOB datatype it gives me following error: 


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

Thanks in Advance.

Ollie
  • 17,058
  • 7
  • 48
  • 59
Sham
  • 691
  • 2
  • 13
  • 26
  • possible duplicate of [Best way to handle LOBs in Oracle dblink'ed tables](http://stackoverflow.com/questions/38074/best-way-to-handle-lobs-in-oracle-dblinked-tables) – Ben Jan 27 '13 at 13:38

2 Answers2

3

You didn't provide the statement you are trying to execute, but I guess you are trying to do something which simply isn't possible in Oracle 10g.

The docs at http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref161 state:

Oracle Database has limited support for remote LOBs. Remote LOBs are supported in three ways.

  1. Create table as select or insert as select.

  2. Functions on remote LOBs returning scalars. SQL and PL/SQL functions having a LOB parameter and returning a scalar datatype are supported. Other SQL functions and DBMS_LOB APIs are not supported for use with remote LOB columns.

  3. Data Interface for remote LOBs. You can insert a character or binary buffer into a remote CLOB or BLOB, and select a remote CLOB or BLOB into a character or binary buffer.

These are the only supported syntax involving LOBs in remote tables. No other usage is supported.

See the link for extended examples.

GWu
  • 2,767
  • 18
  • 28
  • There seems to be a [partial workaround](http://technology.amis.nl/2012/07/02/select-a-blob-across-a-database-link-without-getting-ora-22992/) for this; but as [Tom](http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:696224943687#5143032900346117375) notes it may be something that stops working in the future, so shouldn't be relied upon. – Alex Poole Aug 03 '12 at 17:03
1

To access data of type BLOB use the PIC datatype. Build your query like this:

*select(
  **select PIC from  AGENT_SIGNATURES_TB@DBLINK**
)  as PIC
from dual*

At least that worked for me on an Oracle database.

Jonas Schubert Erlandsson
  • 3,910
  • 1
  • 24
  • 37
user2015502
  • 197
  • 1
  • 2