blob17

CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB) RETURN VARCHAR2 IS
l_blob_len INTEGER;
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;

l_blob_len := DBMS_LOB.GETLENGTH(p_blob);

-- Return simple size info that definitely fits in VARCHAR2
CASE
WHEN l_blob_len <= 32767 THEN RETURN 'LEN:' || l_blob_len;
ELSE RETURN 'LEN:' || l_blob_len || ' (TRUNC)';
END CASE;

END blob_to_text;
/