blob12

CREATE OR REPLACE FUNCTION blob_to_text_chunked(p_blob BLOB) RETURN CLOB IS
l_clob CLOB;
l_buffer RAW(1000);
l_text_chunk VARCHAR2(4000);
l_pos INTEGER := 1;
l_blob_len INTEGER;
BEGIN
IF p_blob IS NULL THEN
RETURN EMPTY_CLOB();
END IF;

DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);
l_blob_len := DBMS_LOB.GETLENGTH(p_blob);

WHILE l_pos <= l_blob_len LOOP
    -- Same logic as your original CONVERT call
    l_buffer := DBMS_LOB.SUBSTR(p_blob, 1000, l_pos);

    l_text_chunk := REPLACE(
        UTL_RAW.CAST_TO_VARCHAR2(
            UTL_RAW.CONVERT(l_buffer, 'AL32UTF8', 'AL32UTF8')
        ), 
        CHR(1), 
        '|'
    );

    DBMS_LOB.WRITEAPPEND(l_clob, LENGTH(l_text_chunk), l_text_chunk);
    l_pos := l_pos + 1000;
END LOOP;

RETURN l_clob;

EXCEPTION
WHEN OTHERS THEN
IF DBMS_LOB.ISTEMPORARY(l_clob) = 1 THEN
DBMS_LOB.FREETEMPORARY(l_clob);
END IF;
RETURN 'ERROR: ' || SQLERRM;
END blob_to_text_chunked;
/