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

5
Share this on .. Tweet about this on Twitter0Share on LinkedIn2Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

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 this on .. Tweet about this on Twitter0Share on LinkedIn2Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

About Author

Oracle Consultant at AMIS

5 Comments

  1. elalgarrobosiquesabiacortarelpan on

    I can’t understand how it works but it really works. My database release is 11.2.0.4.0 – 64bit.
    Thank you very much Anton.

  2. 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.

  3. 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