LOB (BLOB, CLOB) von entfernten Datenbanken lesen ...

English title: Retrieve Lobs from remote databases

Database Links haben, wie die meisten von euch wissen, so ihre Einschränkungen. So lassen sich BLOBS und CLOBs nicht so einfach über einen Database Link abfragen. Probiert man es, so kommt man nicht besonders weit ...

Database Links have, as most of us know, their limitations. One is that you cannot transport a LOB object. An attempt leads to an error message.

SQL> select content from dateien_tab@bloblink where file_name='04_XMLDB.pdf';
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables

Eine Lösung kann allerdings (wieder einmal) Java in der Datenbank sein. Denn in dieser könnte man eine normale JDBC-Verbindung zur entfernten Datenbank öffnen, den Blob selektieren, die Bytes per Java-Streaming lesen und dann lokal (wieder als BLOB) zurückgeben.

Java in the database clould be a solution (again). This is because in a java stored procedure one can open a plain JDBC connection to the remote database, execute a SQL query selecting the LOB column and retrieve the LOB bytes with java streaming. Those bytes could then returned to the SQL layer as a LOB object.

create or replace and compile java source named "JAVA_RemoteBlob" as
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.io.*;

  public class RemoteBlob {
    private static Statement  stmt = null;
    private static ResultSet  rs   = null;
    private static Connection remoteConn = null;
    private static Connection localConn =  null;

    static { 
      try {
         localConn = DriverManager.getConnection("jdbc:default:connection");
      } catch (Exception e) {}
    }

    public static void prepareCursor (String sUser, String sPass, String sConn, String sSql) throws Exception {
      String strUrl = "jdbc:oracle:thin:@" + sConn;
      remoteConn = DriverManager.getConnection( strUrl, sUser, sPass ); 
      stmt = remoteConn.createStatement();
      rs = stmt.executeQuery(sSql);
    }

    public static void closeCursor () throws Exception {
      rs.close();
      stmt.close();
      remoteConn.close();
    }
   
    public static Datum getBlob() throws Exception {
      Datum        localLOB = null;
      Datum        remoteLOB = null;
      InputStream  remoteBytes = null;    
      OutputStream localBytes = null;    
      Reader       remoteChars = null;
      Writer       localChars = null;

      byte[] bBuffer = null;
      char[] cBuffer = null;

      int iBytesRead = 0;

      if (rs.next()) {
  
        remoteLOB = ((OracleResultSet)rs).getOracleObject(1);  

        if (remoteLOB instanceof oracle.sql.BLOB) {
          localLOB = BLOB.createTemporary(localConn, true, BLOB.DURATION_CALL);
          remoteBytes = ((BLOB)remoteLOB).getBinaryStream(0L);
          localBytes = ((BLOB)localLOB).getBinaryOutputStream(0L);
          bBuffer = new byte[((BLOB)remoteLOB).getChunkSize()];
          while ((iBytesRead = remoteBytes.read(bBuffer, 0, bBuffer.length)) != -1) {
            localBytes.write(bBuffer, 0, iBytesRead);
          }
          localBytes.flush();
          localBytes.close();
          remoteBytes.close();
        } else {
          localLOB = CLOB.createTemporary(localConn, true, CLOB.DURATION_CALL);
          remoteChars = ((CLOB)remoteLOB).getCharacterStream(0L);
          localChars = ((CLOB)localLOB).getCharacterOutputStream(0L);
          cBuffer = new char[((CLOB)remoteLOB).getChunkSize()];
          while ((iBytesRead = remoteChars.read(cBuffer, 0, cBuffer.length)) != -1) {
            localChars.write(cBuffer, 0, iBytesRead);
          }
          localChars.flush();
          localChars.close();
          remoteChars.close();
        }
      } else { 
        throw new Exception ("ResultSet Exhausted");
      }
      return localLOB;
    }
  } 
/
sho err

create or replace package remote_lob is
  procedure prepare_cursor(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  );
  procedure close_cursor;
  function get_blob return blob;
  function get_clob return clob;

  function get_blob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return blob;

  function get_clob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return clob;
end remote_lob;
/
sho err

create or replace package body remote_lob is
  procedure prepare_cursor(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) as language java name 'RemoteBlob.prepareCursor(java.lang.String,java.lang.String,java.lang.String,java.lang.String)';

  procedure close_cursor
  as language java name 'RemoteBlob.closeCursor()';

  function get_blob return blob
  as language java name 'RemoteBlob.getBlob() return oracle.sql.BLOB';

  function get_clob return clob
  as language java name 'RemoteBlob.getBlob() return oracle.sql.CLOB';

  function get_blob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return blob is 
    v_blob blob;
  begin
    prepare_cursor(p_user, p_pass, p_conn, p_sql);
    v_blob := get_blob;
    close_cursor;
    return v_blob;
  end get_blob_simple;

  function get_clob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return clob is
    v_lob clob;
  begin
    prepare_cursor(p_user, p_pass, p_conn, p_sql);
    v_lob := get_clob;
    close_cursor;
    return v_lob;
  end get_clob_simple;

end remote_lob;
/
sho err

Nun habt Ihr das Package REMOTE_LOB, welches euch erlaubt, BLOBs oder CLOBS aus entfernten Datenbanken abzurufen. Der einzige Wermutstropfen ist, dass Username, Password und Connection-String hier nochmal übergeben werden müssen: die Informationen eines Database Link kann man nicht nutzen, da die das Passwort für die entfernte Datenbank nicht mehr aus den Dictionary Tabellen ausgelesen werden kann. Vor dem Test braucht Ihr noch Java-Netzwerkprivilegien - ohne schlägt der JDBC-Connect fehl ...

Now we have the package REMOTE_LOB which allows you to read BLOB oder CLOB objects from remote databases. The only disadvantage is that you need to pass username, password and the connect string to the java stored procedures. The information contained in an existing database link cannot be used since the remote users' password cannot be read from the dictionary tables. Before testing you need appropriate java privileges - without the JDBC connection will fail with the error message below.

ERROR in line 1:
ORA-29532: "Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.net.SocketPermission
sccloud033 resolve) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission',
'sccloud033', 'resolve' )
ORA-06512: in "SCOTT.REMOTE_LOB", line 2
ORA-06512: in line 2

Also Privilegien vergeben (als DBA) ...

So we grant the privileges (as DBA) ...

begin
  dbms_java.grant_permission( 
    GRANTEE => 'SCOTT', 
    PERMISSION_TYPE => 'SYS:java.net.SocketPermission',
    PERMISSION_NAME => '{remote-host}', 
    PERMISSION_ACTION => 'connect, resolve' 
  );
end;
/

.... und testen:

... finally we can test ...

SQL> exec remote_lob.prepare_cursor('test','test','sccloud033:1521/orcl','select content from dateien_tab');

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(remote_lob.get_blob) from dual;

DBMS_LOB.GETLENGTH(REMOTE_LOB.GET_BLOB)
---------------------------------------
                                 460800

1 row selected.

SQL> select dbms_lob.getlength(remote_lob.get_blob) from dual;

DBMS_LOB.GETLENGTH(REMOTE_LOB.GET_BLOB)
---------------------------------------
                                 751616

1 row selected.

SQL> select remote_lob.get_blob from dual;

GET_BLOB
--------------------------------------------------------------------------------
D0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF09000600000000000000
00000000050000001502000000000000001000001702000001000000FEFFFFFF0000000025020000

1 Zeile wurde ausgewählt.

SQL> exec remote_lob.close_cursor;

PL/SQL procedure successfully completed.

Mit CLOBs funktioniert das analog. Die Funktionen GET_BLOB_SIMPLE und GET_CLOB_SIMPLE dienen der "Bequemlichkeit" und machen alle drei Schritte auf einmal. Das Grundprinzip könnte sogar verwendet werden, um LOBs aus einer anderen Datenbank auszulesen - schließlich kann man auch den JDBC-Treiber einer anderen Datenbank laden. Das kommt vielleicht noch etwas später - bis jetzt erstmal viel Spaß damit.

CLOBs also work. The functions GET_BLOB_SIMPLE and GET_CLOB_SIMPLE are "convenience functions" which do the three steps (prepare, get, close) at once. The basic principle of these functions could also be used to retrieve LOB objects from non-Oracle databases - a 3rd-party JDBC driver can easily be loaded into the database JVM. More about this later ... for now have fun with this package ...