SQL & PL/SQL

Hi all.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for HPUX: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

I have a problem while decoding base 64 data when my data exceeds 32767, I have this example:

table creation

CREATE TABLE prueba_clob (id NUMBER,columna CLOB);

Insert sample data 'aaaaa':

DECLARE
	v_clob CLOB;
	v_varchar VARCHAR2(32767);
BEGIN
	dbms_lob.createtemporary(v_clob, true);
	v_varchar := RPAD('a',32767,'a');
	FOR i IN 1..2 LOOP
		DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar),v_varchar);
	END LOOP;
	INSERT INTO prueba_clob VALUES (1,v_clob);
END;
/

Function for encoding and decoding:

CREATE OR REPLACE FUNCTION fn_codificar_64
(
	p_entrada		IN CLOB,
	p_codificar		IN NUMBER
)
RETURN CLOB IS
	v_entrada CLOB;
	v_salida CLOB;
	v_tamanio NUMBER;
	v_posicion NUMBER := 1;
	v_buffer_cod VARCHAR2(32767);
	v_buffer_dec VARCHAR2(32767);
	v_cantidad_cod NUMBER := 57;
	v_cantidad_dec NUMBER := 32767;
BEGIN
	v_entrada := p_entrada;
	v_tamanio := LENGTH(v_entrada);
	dbms_lob.createtemporary(v_salida, true);
	
	--When Encoding
	IF p_codificar = 1 THEN
		WHILE v_posicion <= v_tamanio LOOP
			DBMS_LOB.READ(v_entrada,v_cantidad_cod,v_posicion,v_buffer_dec);
			v_buffer_cod := UTL_ENCODE.TEXT_ENCODE(v_buffer_dec, ENCODING => UTL_ENCODE.BASE64);
			v_posicion := v_posicion + v_cantidad_cod;
			v_salida := v_salida||v_buffer_cod;
			v_buffer_dec := null;
			v_buffer_cod := null;
		END LOOP;
	--When Decoding
	ELSIF p_codificar = 0 THEN
		WHILE v_posicion <= v_tamanio LOOP
			DBMS_LOB.READ(v_entrada,v_cantidad_dec,v_posicion,v_buffer_cod);
			v_buffer_dec := UTL_ENCODE.TEXT_DECODE(v_buffer_cod, ENCODING => UTL_ENCODE.BASE64);
			v_posicion := v_posicion + v_cantidad_dec;
			DBMS_LOB.WRITEAPPEND(v_salida, LENGTH(v_buffer_dec),v_buffer_dec);
			v_buffer_dec := null;
			v_buffer_cod := null;
		END LOOP;
	END IF;
	RETURN v_salida;
END fn_codificar_64;
/

Insertion of encoded data:

DECLARE
	v_clob CLOB;
BEGIN
	SELECT fn_codificar_64(columna,1)
	INTO v_clob
	FROM prueba_clob;
	
	INSERT INTO prueba_clob VALUES(2,v_clob);
END;
/

Final results:

SELECT fn_codificar_64(columna,0)
FROM prueba_clob
WHERE id = 2;

aaaaaa�����

As you can see only the first part is correctly decoded, after that we only have �����

The data is correctly encoded you can use this link to check: http://redir.dasumo.com/hex/

When encoding, I use chunks of 57 bytes because I read that when using that amount you can place as many chunks as you need and it works, but when decoding I don't know the correct amount.

Thanks.

Regards.