Oracle BLOB (with base 64 encoding) to String

Question

I'm reading an Oracle BLOB from DB and want to convert it to a readable String. The BLOB is text encoded to binary and I'm pretty sure that it was encoded with Base64.

My code of reading the BLOB returns a String with unrecognized characters:

 public String getStringFromBLOB(String sql) {
    ...
    resultSet.next();
    BLOB blob = null;          
    blob = ((OracleResultSet) resultSet).getBLOB(1);

    byte[] bdata = blob.getBytes(1, (int) blob.length());       
    String tmpStr =new String(bdata);
    str = new String(tmpStr.getBytes("UTF8"), "EUC_KR");
    return str;
}

Any help will be much appreciated.

Why are you converting the resulting byte[] to a String? Are you sure it's a String? And also UTF8? If so, why store it as BLOB? — m0skit0
– m0skit0, Commented Dec 18, 2013 at 13:21
If you're storing it as a blob, why would you bother base-64 encoding it? It seems to me you need to investigate the real situation more carefully. — Jon Skeet
– Jon Skeet, Commented Dec 18, 2013 at 13:22
It is common to use BASE64 encoded text to prevent it from getting mangled by various string conversions. — Brian
– Brian, Commented Dec 18, 2013 at 15:50

Joop Eggen · Accepted Answer · 2013-12-18 16:21:20Z

1

This should do.

byte[] bdata = blob.getBytes(0, (int) blob.length()); // From 0
String data = new String(bdata, "US-ASCII"); // As it claimed to be Base64
byte[] bytes = DatatypeConverter.parseBase64Binary(data);
return new String(bytes, "EUC_KR"); // The original encoding before Base64

answered Dec 18, 2013 at 16:21

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ido E. Over a year ago

Thank you for the response but two things: first of all I think that the blob.getBytes should start from index 1 and not 0. Second, when trying to run it the third row is throwing an exception: "java.lang.ArrayIndexOutOfBoundsException: 65533". Could't find the reason. Thanks again

Joop Eggen Over a year ago

You are right about starting from 1, at least w.r.t. the interface Blob I know. The exception might indicate sloppy programming of parseBase64Binary and data not being Base64.

Brian · Accepted Answer · 2013-12-18 16:17:22Z

If you want it as readable text you can convert it using functions provided in the utl_encode package.

utl_encode.base64_decode()

Some code sourced elsewhere that uses it:

FUNCTION get_blob_from_base64_string (p_clob CLOB)
RETURN BLOB
IS
l_chunk BLOB; --Chunks of decoded blob that'll be appended
l_result BLOB; --Final blob result to be returned
l_rawout RAW (32767); --Decoded raw data from first pass decode
l_rawin RAW (32767); --Encoded raw data chunk
l_amt NUMBER DEFAULT 7700; --Default length of data to decode
l_offset NUMBER DEFAULT 1; --Default Offset of data to decode
l_tempvarchar VARCHAR2 (32767);
BEGIN
BEGIN
DBMS_LOB.createtemporary (l_result, FALSE, DBMS_LOB.CALL);
DBMS_LOB.createtemporary (l_chunk, FALSE, DBMS_LOB.CALL);
LOOP
DBMS_LOB.READ (p_clob, l_amt, l_offset, l_tempvarchar);
l_offset := l_amt + l_offset;
l_rawin := UTL_RAW.cast_to_raw (l_tempvarchar);
l_rawout := UTL_ENCODE.base64_decode (l_rawin);
l_chunk := to_blob (l_rawout);
DBMS_LOB.append (l_result, l_chunk);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;

Source is an Oracle Forums post on base64 decoding a clob (>32K)

Collectives™ on Stack Overflow

Oracle BLOB (with base 64 encoding) to String

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related