Select a blob across a database link, without getting ORA-22992 13422386 1019544571447648 7687716130941590224 o1

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

July 2, 2012 AMIS, Database, Oracle, SQL 5 Comments

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

Related Posts

Tags:

About The Author

Anton Scheffer

Oracle Consultant at AMIS

5 Comments

  1. elalgarrobosiquesabiacortarelpan June 24, 2015

    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 November 5, 2012

    @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 November 4, 2012

    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

  4. Marco Gralike July 2, 2012

    Also be aware of Tom Kyte’s follow up…(regarding “bug or feature”)

    “Select without select into … July 2, 2012 – 9am Central time zone” on http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:696224943687

  5. Marco Gralike July 2, 2012

    Follow up on Anton’s post regarding XMLTYPE (CLOB/Basicfile/Securefile) handling:

    http://www.liberidu.com/blog/2012/07/02/xmltype-datatype-and-ora-22992