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

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