Select a blob across a database link, without getting ORA-22992

4

Just a quick blog about a simple trick to select a blob across a database link, especially for a collegue of mine, Harry Dragstra.

Say, you have a table with a blob on a remote database

SQL> describe test_blob@xx
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
BLB BLOB


When you use a normal select statement to get all columns you run into an error

SQL> select id, blb
2 from test_blob@xx;

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

no rows selected

But when you wrap your blob column in a scalar subquery expression it works like a charm

SQL> select id
2 , ( select blb from dual ) blb
3 , dbms_lob.getlength( ( select blb from dual ) ) len
4 , utl_raw.cast_to_varchar2( dbms_lob.substr( ( select blb from dual ), 20, 1 ) ) sub
5 from test_blob@xx;

ID BLB
---------- ------------------------------------------------------------
LEN
----------
SUB
--------------------------------------------------------------------------------
1 74657374696E6774657374696E6774657374696E6774657374696E677465
7000
testingtestingtestin

2 74657374696E6774657374696E6774657374696E6774657374696E677465
7168000
testingtestingtestin

This trick works, as far as I know, only on a 11.2 database.
P.S. According to Tom Kyte this method works with databases from version 10.2.0.5.0 and upwards, but beware, see Ask Tom

Share.

About Author

4 Comments

  1. Anton Scheffer on

    @Isaac chocron:
    I don’t believe that your query is working for small clobs, and that inserting a large clob in your remote table causes an ORA-00942 in your query.

  2. Isaac chocron on

    I tested it on our 11R2 (64bits) with a object type based table that contain CLOB.
    When the length of the remote CLOB is more of 4000 characters, I received the next oracle error:

    1 select message#, (select message from dual) txt from message_from_machine@copy_values
    2* where message#= 2557073
    SQL> /
    ERROR:
    ORA-00942: table or view does not exist

Leave a Reply