my database version is 11.2.0.2.0 and NLS_CHARACTERSET is set to WE8ISO8859P15 and NLS_NCHAR_CHARACTERSET is set to AL16UTF16.
I get an UTF-8 stream which is base64 encoded via a CLOB variable in PL/SQL. When i now decode (see my function decodeClobBase642Clob) this stream into CLOB it works fine except the special characters because i think the characterset in the database is set to WE8ISO8859P15.
-- -----------------------------------------------------------------------------
-- Decode Base64 CLOB --> CLOB.
-- -----------------------------------------------------------------------------
PROCEDURE decodeClobBase642Clob(pBase64 IN CLOB, pClob OUT CLOB,
pReturn OUT NUMBER, pMsg OUT VARCHAR2)
IS
mName CONSTANT VARCHAR2(64) := 'decodeClobBase642Clob';
vAmount PLS_INTEGER := 192;
vBase64Len PLS_INTEGER;
vBufferChar VARCHAR2(200);
vBufferRaw RAW(200);
vClob CLOB;
vClobLen PLS_INTEGER;
vOffset PLS_INTEGER := 1;
BEGIN
pMsg := NULL;
pReturn := 0;
dbms_output.put_line('Start base64 decoding ...');
vBase64Len := dbms_lob.getlength(lob_loc => pBase64);
IF vBase64Len IS NULL THEN
pMsg := 'Base64 Stream ist leer.';
pReturn := 10;
END IF;
IF pReturn = 0 THEN
dbms_output.put_line('<Base64Length>' || vBase64Len);
dbms_lob.createtemporary(lob_loc => vClob, cache => TRUE);
LOOP
IF vOffset >= vBase64Len THEN
EXIT;
END IF;
dbms_lob.read(lob_loc => pBase64, amount => vAmount, offset => vOffset,
buffer => vBufferChar);
vBufferRaw := utl_raw.cast_to_raw(vBufferChar);
BEGIN
dbms_lob.append(
dest_lob => vClob,
src_lob => utl_raw.cast_to_varchar2(utl_encode.base64_decode(vBufferRaw)));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('<vAmount>' || vAmount || '<vOffset>' || vOffset ||
'<vBufferChar>' || vBufferChar);
dbms_output.put_line('Error in dbms_lob.append <SQLERRM>' || SQLERRM);
RAISE;
END;
vOffset := vOffset + vAmount;
END LOOP;
vClobLen := dbms_lob.getlength(lob_loc => vClob);
dbms_output.put_line('<ClobLength>' || vClobLen);
dbms_output.put_line('... finshed base64 decoding.' || CHR(10));
pClob := vClob;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END decodeClobBase642Clob;
Many thanks in advance.