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 support.oracle.com:
- 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 is retval blob; tmpraw raw(2000); tmplen number; tmpchk number; chksize number; begin --preset vars chksize:=2000; dbms_lob.createtemporary (retval,true); execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmplen using rwid; -- precalc tmpchk:=floor(tmplen/chksize); -- applicate frist chunks for i in 0 .. tmpchk-1 loop execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid; dbms_lob.append(retval,tmpraw); 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; dbms_lob.append(retval,tmpraw); end if; return retval; end; /
The explanation of the function is simple:
- Create a temp lob at local site
- The limitation of DBMS_LOB.SUBSTR is a RAW(2000) as maximum chunk size
- 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
- return local blob to upper caller
Now create a view with the new defintion:
CREATE OR REPLACE FORCE VIEW TESTVW1 (ID, MYLOB) AS SELECT id ,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 is PRAGMA AUTONOMOUS_TRANSACTION; retval blob; begin 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; commit; return retval; end; /
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.