blob8

CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB) RETURN VARCHAR2 IS
l_raw RAW(32767);
l_text VARCHAR2(32767);
l_blob_length NUMBER;
BEGIN
-- Check for NULL input
IF p_blob IS NULL THEN
RETURN '';
END IF;

-- Get blob length
l_blob_length := DBMS_LOB.GETLENGTH(p_blob);

-- Handle empty blob
IF l_blob_length IS NULL OR l_blob_length = 0 THEN
RETURN '';
END IF;

-- Convert BLOB to RAW
l_raw := DBMS_LOB.SUBSTR(p_blob, l_blob_length, 1);

-- Convert RAW to VARCHAR2 using UTF-8 encoding
-- Since your BLOB is ≤ 375 bytes, 32767 characters is more than sufficient
l_text := UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(l_raw, 'AL32UTF8', 'AL32UTF8'));

RETURN l_text;

EXCEPTION
WHEN OTHERS THEN
-- Log error details if needed, then return empty string or re-raise
RETURN 'CONVERSION_ERROR: ' || SQLERRM;
END blob_to_text;
/