create or replace PROCEDURE PROC_BLOB_TO_FILE(
    ov_retcode      OUT VARCHAR2,
    ov_errmsg       OUT VARCHAR2,
    ib_blob         IN  BLOB,
    iv_dir          IN  VARCHAR2,
    iv_file_name    IN  VARCHAR2)
AS
    ln_blob_len     NUMBER;
    lf_file         UTL_FILE.FILE_TYPE;
    ln_pos          INTEGER := 1;
    ln_amount       BINARY_INTEGER := 32767;
    lr_buffer       RAW(32767);
BEGIN
    ov_retcode := '0';
    ov_errmsg := NULL;
--
    IF iv_file_name IS NULL THEN
        RETURN;
    END IF;
--
    -- Get BLOB length and open file in write binary mode
    ln_blob_len := DBMS_LOB.getlength(ib_blob);
    lf_file := UTL_FILE.fopen(
        iv_dir,
        iv_file_name,
        'wb',
        32767
    );
--
    -- Read chunks of the BLOB and write them to the file
    -- until complete.
    WHILE (ln_pos <= ln_blob_len) LOOP
        DBMS_LOB.read(ib_blob, ln_amount, ln_pos, lr_buffer);
        UTL_FILE.put_raw(lf_file, lr_buffer, TRUE);
        ln_pos := ln_pos + ln_amount;
    END LOOP;

    -- Close the file.
    UTL_FILE.fclose(lf_file);

EXCEPTION
    WHEN OTHERS THEN
        -- Close the file if something goes wrong.
        IF (UTL_FILE.is_open(lf_file)) THEN
            UTL_FILE.fclose(lf_file);
        END IF;
--
        ov_retcode := '1';
        ov_errmsg := SQLERRM || dbms_utility.format_error_backtrace;
--
        apex_debug.enter(
            'PROC_BLOB_TO_FILE',
            'iv_dir'        , iv_dir,
            'iv_file_name'  , iv_file_name,
            'ov_errmsg'     , ov_errmsg);
END PROC_BLOB_TO_FILE;
/