30 November 2009

Base64 encoding inside Oracle Database

I've put the following code inside the oracle database, so that I can transform a Oracle BLOB into a base64 encoded CLOB and a base64 encoded CLOB back to a BLOB. I've done this transformation so I can cut my encoded CLOB into pieces of 32k which can be handled through a varchar2. Then I can sent those pieces to a PJC (pluggable java component) inside Oracle Forms. This way my binary file (blob) won't get corrupted when pasting the file together in the PJC. I know there is a package called utl_encode, but I have chosen to use java inside the Oracle database to solve this problem.


create or replace and resolve java source names base64 as
package base64;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.IOException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.sql.BLOB;
import oracle.sql.CLOB;

import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;


public class base64 {

public static CLOB blobToBase64 ( BLOB blobCol){
try {
BASE64Encoder b64ec = new BASE64Encoder();

Connection con = DriverManager.getConnection("jdbc:default:connection:");
CLOB newClob = oracle.sql.CLOB.createTemporary(con, true, oracle.sql.CLOB.DURATION_SESSION);



int bufSize = 1024*1024*1;

final BufferedInputStream in = new BufferedInputStream(blobCol.getBinaryStream(), bufSize * 2);
final BufferedOutputStream out = new BufferedOutputStream(newClob.setAsciiStream(0L), bufSize);

b64ec.encode(in, out);
out.flush();
out.close();
return newClob;
} catch (SQLException e) {
// TODO
} catch (IOException e) {
// TODO
}
return null;
}

public static BLOB base64ToBlob ( CLOB clobCol){
try {
BASE64Decoder b64dc = new BASE64Decoder();

Connection con = DriverManager.getConnection("jdbc:default:connection:");
BLOB newBlob = oracle.sql.BLOB.createTemporary(con, true, oracle.sql.BLOB.DURATION_SESSION);

b64dc.decodeBuffer(clobCol.getAsciiStream(), newBlob.getBinaryOutputStream());
int bufSize = 1024*1024*1;

final BufferedInputStream in = new BufferedInputStream(clobCol.getAsciiStream(), bufSize * 2);
final BufferedOutputStream out = new BufferedOutputStream(newBlob.setBinaryStream(0L), bufSize);

b64dc.decodeBuffer(in, out);
out.flush();
out.close();

return newBlob;

} catch (SQLException e) {
// TODO
} catch (IOException e) {
// TODO
}
return null;
}
}
/

create or replace function base64decode(clobCol in CLOB) return BLOB as language java name 'base64.base64.base64ToBlob(oracle.sql.CLOB) return oracle.sql.BLOB'
/
create or replace function base64encode(blobCol in BLOB) return CLOB as language java name 'base64.base64.blobToBase64(oracle.sql.BLOB) return oracle.sql.CLOB'
/

6 reacties:

  1. I'd suggest you use Apache Commons' Base64 class. Using the sun.misc package makes your code less portable. Nonetheless, you make me proud ;-)

    ReplyDelete
  2. Dick,

    I've seen more suggestions pointing to the Apache Commons Base64 class. But the sun.misc is available from within the Oracle Database so I don't have to include any additional classes or jars.

    Regards,

    Mark

    ReplyDelete
  3. Great Mark ;-)
    Unfortunately, there is no Java in the XE version :(
    But you can achieve the same task with PL/SQL, using the UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(raw)) funtion

    ReplyDelete
  4. The only problem with the utl_encode and the utl_raw is that you are limited again to the maximum size of the varchar2 again.

    Regards,

    Mark

    ReplyDelete
  5. Mark, I'm new to Java Stored Procedures, just need to get my head round this;
    DriverManager.getConnection("jdbc:default:connection:");
    If I'm calling base64encode from a PL/SQL block, is that the Oracle way of saying just use the same session ?

    ReplyDelete
  6. Yes, since you are already inside the oracle database you don't need to describe the connection inside java.

    ReplyDelete