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

Anton Scheffer July 2, 2012 5
0 0
Read Time:1 Minute, 2 Second

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

About Post Author

Anton Scheffer

Oracle Consultant at AMIS
anton.scheffer@amis.nl
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Related posts:

Data Integriteit anno 2011 – Hands-on met Toon Koppelaars en RuleGen 3.0 bij AMIS op dinsdag 27 september The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper Oracle 12c STIG Password Generator in PL/SQL My First Steps with RAC One Node 11gR2 (11.2.0.2) Default ThumbnailWeird ADF 11g requirement addressed with left outer join and modern SQL join syntax

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

5 thoughts on “Select a blob across a database link, without getting ORA-22992”

  1. 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. @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. 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

Comments are closed.

Next Post

Oracle Enterprise Manager Cloud Control 12.1.0.1 – Agent Installation, Issues and Solutions

Mon Jul 2 , 2012
Currently busy for a client to install and configure Oracle Enterprise Manager 12c for database and more administration across the globe. These environments were configured and setup by a different 3rd party so not always follow our wishes and administration guidelines. You can imagine that such environments are also a […]

You May Like

%d bloggers like this: