blob9

CREATE OR REPLACE FUNCTION blob_to_text3(p_blob BLOB)
RETURN VARCHAR2
IS
l_raw RAW(32767);
l_text VARCHAR2(32767);
l_blob_length PLS_INTEGER;
BEGIN
-- Handle null or empty input safely
IF p_blob IS NULL THEN
RETURN '';
END IF;

l_blob_length := DBMS_LOB.getlength(p_blob);
IF l_blob_length IS NULL OR l_blob_length = 0 THEN
RETURN '';
END IF;

-- Guard against oversize blobs (> 32767 bytes)
IF l_blob_length > 32767 THEN
RAISE_APPLICATION_ERROR(
-20001,
'BLOB too large for VARCHAR2(32767); use a CLOB-returning version instead.'
);
END IF;

-- Extract bytes safely (within RAW(32767) limit)
l_raw := DBMS_LOB.substr(p_blob, l_blob_length, 1);

-- Decode UTF-8 bytes to VARCHAR2 text
-- UTL_I18N.RAW_TO_CHAR properly respects multi-byte UTF-8 sequences
BEGIN
l_text := UTL_I18N.RAW_TO_CHAR(l_raw, 'AL32UTF8');
EXCEPTION
WHEN OTHERS THEN
-- If the data isn’t valid UTF-8, fall back to binary-safe cast
l_text := UTL_RAW.cast_to_varchar2(l_raw);
END;

-- Replace control character 0x01 (if present) for display safety
RETURN REPLACE(l_text, CHR(1), '|');
END blob_to_text3;
/