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
-
Leave a Reply
-
#2 written by Isaac chocron 6 months ago
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 -
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
-
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
- Comment Feed for this Post
@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.