This content originally appeared on DEV Community and was authored by Query Filter
CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB) RETURN VARCHAR2 IS
l_result VARCHAR2(32767) := '';
l_single_byte RAW(1);
l_single_char VARCHAR2(4);
l_blob_len INTEGER;
i INTEGER;
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;
l_blob_len := DBMS_LOB.GETLENGTH(p_blob);
FOR i IN 1..LEAST(l_blob_len, 8000) LOOP
-- STRICT length check before concatenation
IF LENGTH(l_result) >= 32700 THEN
EXIT;
END IF;
l_single_byte := DBMS_LOB.SUBSTR(p_blob, 1, i);
BEGIN
l_single_char := UTL_RAW.CAST_TO_VARCHAR2(
UTL_RAW.CONVERT(l_single_byte, 'AL32UTF8', 'AL32UTF8')
);
-- Check if adding this character would exceed limit
IF LENGTH(l_result) + LENGTH(l_single_char) > 32767 THEN
EXIT;
END IF;
l_result := l_result || l_single_char;
EXCEPTION
WHEN OTHERS THEN
-- Only add replacement if we have space
IF LENGTH(l_result) < 32767 THEN
l_result := l_result || '?';
ELSE
EXIT;
END IF;
END;
END LOOP;
RETURN l_result; -- No truncation message to avoid overflow
EXCEPTION
WHEN OTHERS THEN
-- Return whatever we have without concatenation
RETURN SUBSTR(l_result, 1, 32767);
END blob_to_text;
/
This content originally appeared on DEV Community and was authored by Query Filter
Query Filter | Sciencx (2025-11-12T19:44:34+00:00) blob16. Retrieved from https://www.scien.cx/2025/11/12/blob16/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.