Große BLOBs über einen Datenbank Link selektieren

Heute habe ich versucht ein BLOB über einen Datenbank Link zu selektieren.

Ich weiß, es gibt in solchen Fällen einige Einschränkungen, aber in diesem Fall gab mir der Business Case keine andere Wahl.

Mein Ziel war daher die BLOBs über einen Datenbank Link transparent zu selektieren. Einen passenden Workaround konnte ich nicht finden. Von support.oracle.com kam folgendes Statement:

• 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.”

Zusammengefasst bedeutet diese Aussage, dass ein BLOB nativ über einen Datenbank Link nicht selektiert werden kann, falls dieser großer als 32KB-1 ist. Interessante Tatsache in diesem Zusammenhang ist, dass man DBMS_LOB Operators auf der lokalen und remoten Seite verwenden kann.

Mein persönlicher Favorit ist die DBMS_LOB.SUBSTR Funktion. Der Name der Funktion ist etwas ungenau, denn man kann damit BLOBs und auch CLOBs ansprechen.

Daraus hat sich mein folgender Ansatz ergeben, um BLOBs über einen Datenbank Link selektieren zu können:

Lösung VERSION 1 (Chunk Methode):

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;
/

Die Erklärung der Funktion ist sehr einfach:

1. Ein TEMP LOB auf der lokalen Seite erstellen
2. Die Limitation von DBMS_LOB.SUBSTR als RAW(2000) als mximale chunk size definieren
3. Die einzelnen Chunks (max 2000 bytes) über den Datenbank Link kopieren und mit den Chunks auf der lokalen Seiten temporär ein BLOB zusammenfügen
4. Das BLOB lokal auf dem Aufrufer übergeben

In Anschluss daran eine VIEW mit den neuen Definitionen erstellen:

CREATE OR REPLACE FORCE VIEW TESTVW1 (ID, MYLOB) AS 
SELECT id
       ,getblobviadblink('ARCHIV','MYLOBTABLE','MYLOB',rowid) MYLOB
FROM  MYLOB@archiv;

Fertig.
Nun ist es möglich über einen Datenbank Link auch größere BLOBs als 32KB-1 zu selektieren!

Es gibt aber auch noch andere Lösungswege:

Lösung VERSION 2 (temporary table Methode)

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;
/

Beide Methoden sind möglich, aber die Version 2 ist wesentlich schneller.

Darüber hinaus gibt es sicherlich auch noch andere Lösungsmöglichkeiten.

Ich würde mich freuen, wenn Sie im Kommentar Ihre Erfahrungen posten.