Oracle Database: Select BLOB via DB Link

Today i’vh tried to select a simple blob over da database link. I know there are some restrictions using database links but in my business case i’vh to do this. So my goal is to transparenty select blobs over a database link from a remote database.

First I havn’t found any suiteable workaround. Here my references from

  • SELECT with a LOB and DBLink Returns an ORA-22992: Cannot Use LOB Locators Selected from Remote tables (Doc ID 1234893.1)
    • “The error is expected because the use of DBLinks and LOBs via the SELECT from PL/SQL is not supported.”
  • Ora-22992 workaround (Doc ID 436707.1)
    • Getting ORA-1406 with lobs greater than 32KB – 1
  • ORA-1406: Fetched Column Value was Truncated When Selecting Remote Column into Local BLOB Variable (Doc ID 459557.1)
    • “This means that we are not able to retrieve BLOBs columns greater than 32KB – 1 in size through a database link.”

Summarized we are not able to select a blob nativ over a database link if this blob is greater than 32KB-1.

The interesting thing is, that you are able to use DBMS_LOB operators on local and remote site. My favourite is the DBMS_LOB.SUBSTR function. The name is a little bit missleading because you can retrieve BLOB and CLOB. Here is my approach to select blob via a database link transparently to application:

VERSION 1 (chunk method):

create or replace function GETBLOBVIADBLINK
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,rwid in urowid)
return blob
  retval blob;
  tmpraw raw(2000);  
  tmplen number;
  tmpchk number;
  chksize number;
  --preset vars
  dbms_lob.createtemporary (retval,true);
  execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmplen using rwid;
  -- precalc  

  -- applicate frist chunks  
  for i in 0 .. tmpchk-1
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid;
  end loop;
  -- applicate last entry
  if (tmplen-(tmpchk*chksize)) > 0 then
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||(tmplen-(tmpchk*chksize))||','||((tmpchk*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid;
  end if;
  return retval;

The explanation of the function is simple:

  1. Create a temp lob at local site
  2. The limitation of DBMS_LOB.SUBSTR is a RAW(2000) as maximum chunk size
  3. Copy chunk (2000 bytes max.) by chunk over the database link and append it to our local temporary blob. So we generate a local copy
  4. return local blob to upper caller


Now create a view with the new defintion:

       ,getblobviadblink('ARCHIV','MYLOBTABLE','MYLOB',rowid) MYLOB
FROM  MYLOB@archiv;

It’s done. I’m able to select a blob via dblink even it is greater than 32KB, now!

VERSION 2 (temporary table method):

create global temporary table tmplob (tmplob blob) ON COMMIT PRESERVE ROWS;
create or replace function getblobviadblink2
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,rwid in urowid)
return blob
  retval blob;

  execute immediate 'insert /*+ NOLOGGING */ into tmplob select '||col||' from '||tbl||'@'||dblnk||' where rowid=:rwid' using rwid;
  select tmplob into retval from tmplob;
  delete /*+ NOLOGGING */ from tmplob;
  return retval;

Both methods are suitable for selecting on  it, but the VERSION2 Method is significant faster on network.

These are just two of many other ways. I would appreciate it if you share your implementation and experience with lob selection.

5 thoughts on “Oracle Database: Select BLOB via DB Link

  1. What’s Going down i am new to this, I stumbled upon this I’ve discovered It absolutely helpful and it has
    helped me out loads. I’m hoping to contribute & help other
    users like its aided me. Great job.

  2. Hi,
    Great article!

    VERSION 2 will not work:
    – you insert lob into temporary table
    – then you query lob locator (not the value) into retval
    – then you delete from temp table – data is lost here
    – finally, you return lob locator, which points to nonexisting data.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s