blob6

CREATE OR REPLACE FUNCTION blob_to_text2(p_blob BLOB)
RETURN VARCHAR2
IS
l_raw RAW(32767);
l_len PLS_INTEGER;
l_pos PLS_INTEGER := 1;
l_amt PLS_INTEGER;
l_text VARCHAR2(32767);
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;

l_len := DBMS_LOB.getlength(p_blob);

WHILE l_pos <= l_len LOOP
l_amt := LEAST(32767, l_len - l_pos + 1); -- safe chunk size for RAW
l_raw := DBMS_LOB.substr(p_blob, l_amt, l_pos); -- returns RAW up to l_amt bytes

-- decode this chunk from AL32UTF8
l_text := l_text || UTL_I18N.RAW_TO_CHAR(l_raw, 'AL32UTF8');

-- defensive check: VARCHAR2 (PL/SQL) max is 32767
IF LENGTH(l_text) > 32767 THEN
  RAISE_APPLICATION_ERROR(-20001,
    'Decoded text exceeds VARCHAR2(32767) limit. Use a CLOB-returning function for large content.');
END IF;

l_pos := l_pos + l_amt;

END LOOP;

RETURN REPLACE(l_text, CHR(1), '|');
END;
/