Clob to Blob Using APEX Web Service

February 29, 2020Posted inAPEX

CLOBBASE642BLOB Function

This function is used to convert Clob to base64Blob.

Clob to Blob using APEX_WEB_SERVICE

Syntax

APEX_WEB_SERVICE.CLOBBASE642BLOB (
     p_clob IN CLOB)
RETURN BLOB;

Example
declare
    l_clob CLOB;
    l_blob BLOB;
    l_blob_2 BLOB;
begin
    SELECT BLOB_CONTENT
      INTO l_BLOB
      FROM APEX_APPLICATION_FILES
      WHERE filename = 'APEX_ENHANCED_LOV_ITEM.plb';
 -- Blob to clob(base64)
    l_CLOB := blob_to_clob(l_BLOB);
 -- CLOB to BLOB
    l_blob_2 := apex_web_service.clobbase642blob(l_CLOB);
end;

For the above reference of blob_to_clob (Full Post )

Copy Code
CREATE OR REPLACE FUNCTION blob_to_clob (i_blob IN BLOB)
    RETURN CLOB
AS
    l_clob     CLOB;
    l_temp     VARCHAR2 (32767);
    l_start    PLS_INTEGER := 1;
    l_buffer   PLS_INTEGER := 32767;
BEGIN
    DBMS_LOB.createtemporary (l_clob, TRUE);

    FOR i IN 1 .. CEIL (DBMS_LOB.getlength (i_blob) / l_buffer)
    LOOP
        --convert to blob
        l_temp :=
            UTL_RAW.cast_to_varchar2 (
                DBMS_LOB.SUBSTR (i_blob, l_buffer, l_start));
        -- append to clob variable
        DBMS_LOB.writeappend (l_clob, LENGTH (l_temp), l_temp);

        --Change start position
        l_start := l_start + l_buffer;
    END LOOP;

    RETURN l_clob;
END blob_to_clob;
Tags: