Solution with utl_encode.base64_encode
and utl_encode.base64_decode
have one limitation, they work only with strings up to 32,767 characters/bytes.
In case you have to convert bigger strings you will face several obstacles.
BASE64_ENCODE
the function has to read 3 Bytes and transform them. In case of Multi-Byte characters (e.g. öäüè€
stored at UTF-8, aka AL32UTF8
) 3 Character are not necessarily also 3 Bytes. In order to read always 3 Bytes you have to convert your CLOB
into BLOB
first.BASE64_DECODE
. The function has to read 4 Bytes and transform them into 3 Bytes. Those 3 Bytes are not necessarily also 3 CharactersCR
and/or LF
) character each 64 characters. Such new-line characters have to be ignored while decoding.Taking all this into consideration the full featured solution could be this one:
CREATE OR REPLACE FUNCTION DecodeBASE64(InBase64Char IN OUT NOCOPY CLOB) RETURN CLOB IS
blob_loc BLOB;
clob_trim CLOB;
res CLOB;
lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
read_offset INTEGER := 1;
warning INTEGER;
ClobLen INTEGER := DBMS_LOB.GETLENGTH(InBase64Char);
amount INTEGER := 1440; -- must be a whole multiple of 4
buffer RAW(1440);
stringBuffer VARCHAR2(1440);
-- BASE64 characters are always simple ASCII. Thus you get never any Mulit-Byte character and having the same size as 'amount' is sufficient
BEGIN
IF InBase64Char IS NULL OR NVL(ClobLen, 0) = 0 THEN
RETURN NULL;
ELSIF ClobLen<= 32000 THEN
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(InBase64Char)));
END IF;
-- UTL_ENCODE.BASE64_DECODE is limited to 32k, process in chunks if bigger
-- Remove all NEW_LINE from base64 string
ClobLen := DBMS_LOB.GETLENGTH(InBase64Char);
DBMS_LOB.CREATETEMPORARY(clob_trim, TRUE);
LOOP
EXIT WHEN read_offset > ClobLen;
stringBuffer := REPLACE(REPLACE(DBMS_LOB.SUBSTR(InBase64Char, amount, read_offset), CHR(13), NULL), CHR(10), NULL);
DBMS_LOB.WRITEAPPEND(clob_trim, LENGTH(stringBuffer), stringBuffer);
read_offset := read_offset + amount;
END LOOP;
read_offset := 1;
ClobLen := DBMS_LOB.GETLENGTH(clob_trim);
DBMS_LOB.CREATETEMPORARY(blob_loc, TRUE);
LOOP
EXIT WHEN read_offset > ClobLen;
buffer := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(clob_trim, amount, read_offset)));
DBMS_LOB.WRITEAPPEND(blob_loc, DBMS_LOB.GETLENGTH(buffer), buffer);
read_offset := read_offset + amount;
END LOOP;
DBMS_LOB.CREATETEMPORARY(res, TRUE);
DBMS_LOB.CONVERTTOCLOB(res, blob_loc, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, DBMS_LOB.DEFAULT_CSID, lang_context, warning);
DBMS_LOB.FREETEMPORARY(blob_loc);
DBMS_LOB.FREETEMPORARY(clob_trim);
RETURN res;
END DecodeBASE64;
CREATE OR REPLACE FUNCTION EncodeBASE64(InClearChar IN OUT NOCOPY CLOB) RETURN CLOB IS
dest_lob BLOB;
lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
read_offset INTEGER := 1;
warning INTEGER;
ClobLen INTEGER := DBMS_LOB.GETLENGTH(InClearChar);
amount INTEGER := 1440; -- must be a whole multiple of 3
-- size of a whole multiple of 48 is beneficial to get NEW_LINE after each 64 characters
buffer RAW(1440);
res CLOB := EMPTY_CLOB();
BEGIN
IF InClearChar IS NULL OR NVL(ClobLen, 0) = 0 THEN
RETURN NULL;
ELSIF ClobLen <= 24000 THEN
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(InClearChar)));
END IF;
-- UTL_ENCODE.BASE64_ENCODE is limited to 32k/(3/4), process in chunks if bigger
DBMS_LOB.CREATETEMPORARY(dest_lob, TRUE);
DBMS_LOB.CONVERTTOBLOB(dest_lob, InClearChar, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, DBMS_LOB.DEFAULT_CSID, lang_context, warning);
LOOP
EXIT WHEN read_offset >= dest_offset;
DBMS_LOB.READ(dest_lob, amount, read_offset, buffer);
res := res || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer));
read_offset := read_offset + amount;
END LOOP;
DBMS_LOB.FREETEMPORARY(dest_lob);
RETURN res;
END EncodeBASE64;
do url_raw.cast_to_raw()
support in oracle 6
All the previous posts are correct. There's more than one way to skin a cat. Here is another way to do the same thing: (just replace "what_ever_you_want_to_convert" with your string and run it in Oracle:
set serveroutput on;
DECLARE
v_str VARCHAR2(1000);
BEGIN
--Create encoded value
v_str := utl_encode.text_encode
('what_ever_you_want_to_convert','WE8ISO8859P1', UTL_ENCODE.BASE64);
dbms_output.put_line(v_str);
--Decode the value..
v_str := utl_encode.text_decode
(v_str,'WE8ISO8859P1', UTL_ENCODE.BASE64);
dbms_output.put_line(v_str);
END;
/
Source: Stackoverflow.com