I was asked recently whether there was any way to convert a BLOB into Base64 in PL/SQL easily – it’s possible in Java, but the resulting string may well be more than 32k which seems to be a limit in what can be returned.
Digging out the trusty Oracle Built Ins guide, there is a UTL_ENCODE package which can do this, but only up to 32k it seems, because it only handles a RAW datatype. So, the way round the limitation would be to strip out the first <n> characters in the BLOB, convert them to a raw, then encode them, then put them into a new CLOB, which can handle the greater than 32k limit.
Putting together a few calls to UTL_ENCODE, DBMS_LOB and UTL_RAW (since we need to convert the RAW back into a VARCHAR2 to be able to append it to a CLOB), I came up with the following:
CREATE OR REPLACE PROCEDURE encode_to_base64 ( p_blob IN BLOB , p_clob OUT CLOB ) AS l_raw RAW(32767); l_length PLS_INTEGER; l_loops PLS_INTEGER; l_pos PLS_INTEGER; l_encoded_raw RAW(32767); l_encoded_vc2 VARCHAR2(32767); l_size PLS_INTEGER; BEGIN l_size := 12288; l_length := DBMS_LOB.getLength(p_blob); IF l_length > l_size THEN l_loops := ROUND((l_length/l_size)+0.5); ELSE l_loops := 1; END IF; l_pos := 1; FOR i IN 1..l_loops LOOP DBMS_LOB.READ(p_blob, l_size, l_pos, l_raw); l_encoded_raw := UTL_ENCODE.base64_encode(l_raw); l_encoded_vc2 := UTL_RAW.cast_to_varchar2(l_encoded_raw); DBMS_LOB.writeAppend ( p_clob, LENGTH(l_encoded_vc2), l_encoded_vc2); l_pos := l_pos + l_size; END LOOP; DBMS_LOB.writeAppend(p_clob,1,CHR(10)); END encode_to_base64; /
That should do it – hope it’s useful!