Ever wondered how to turn pictures or other binary data back into readable text in Oracle PL/SQL? Let’s explore the easy steps of converting BLOB (Binary Large Object) to CLOB (Character Large Object). It’s simpler than you think!
CREATE FUNCTION BLOB_TO_CLOB(
P_BLOB BLOB,
P_CHARSET_ID IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID
)
RETURN CLOB
IS
v_result CLOB;
v_blob BLOB := BLOB_TO_CLOB.P_BLOB;
v_charset_id INTEGER := BLOB_TO_CLOB.P_CHARSET_ID;
v_dest_offsset INTEGER := 1;
v_src_offsset INTEGER := 1;
v_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
v_warning INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
BEGIN
IF v_blob IS NULL THEN
RETURN NULL;
END IF;
DBMS_LOB.CREATETEMPORARY(LOB_LOC => v_result, CACHE => FALSE);
DBMS_LOB.CONVERTTOCLOB(DEST_LOB => v_result,
SRC_BLOB => v_blob,
AMOUNT => DBMS_LOB.LOBMAXSIZE,
DEST_OFFSET => v_dest_offsset,
SRC_OFFSET => v_src_offsset,
BLOB_CSID => v_charset_id,
LANG_CONTEXT => v_lang_context,
WARNING => v_warning
);
RETURN v_result;
END;
Here is a basic example: I have an Excel file stored in APEX_APPLICATION_STATIC_FILES. The data is stored in BLOB type in FILE_CONTENT column. The following example shows how to make a change.
DECLARE
v_blob_content APEX_APPLICATION_STATIC_FILES.file_content%TYPE;
v_mime_type APEX_APPLICATION_STATIC_FILES.mime_type%TYPE;
v_clob clob;
BEGIN
SELECT file_content,
mime_type
INTO v_blob_content,
v_mime_type
FROM APEX_APPLICATION_STATIC_FILES
WHERE file_name = 'DEMO.xlsx';
v_clob := BLOB_TO_CLOB(P_BLOB => v_blob_content,
P_CHARSET_ID => NLS_CHARSET_ID('UTF8'));
END;
I hope this post helps.