ORACLE APEX | STORE BLOB FILE | Store Blob file Outside the Oracle database in Oracle APEX

-- New Table 

CREATE TABLE  "EMP_IMAGE" 

   (    "EMP_ID" varchar2(10), 

    "FILENAME" VARCHAR2(3000) ,  

    "MIMETYPE" VARCHAR2(3000), 

    "LAST_UPDATE" DATE, 

    "IMAGE" BLOB

   )

/

1)  Add privilege to use UTL_FILE to Your User
grant execute on utl_file to TEST;

select * from dba_tab_privs where table_name='UTL_FILE';

2 ) Create Directory to Save File and add privilege to write on Directory to You User
CREATE OR REPLACE DIRECTORY TEST_DIR AS 'G:\STORE_IMAGES';
GRANT read, write ON DIRECTORY TEST_DIR TO TEST;

3) Use this Procedure to write a BLOB file to your disk
Create or Replace PROCEDURE Store_Blob_FileOutside (p_blob IN OUT NOCOPY BLOB,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2)
AS
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
BEGIN
l_blob_len := DBMS_LOB.getlength(p_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen(p_dir, p_filename,'wb', 32767);
-- Read chunks of the BLOB and write them to the file until complete.
WHILE l_pos <= l_blob_len LOOP
DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END Store_Blob_FileOutside;

4) Use this process to upload file

DECLARE
l_blob BLOB;
BEGIN
-- Ge SELECT filename,
select
BLOB_CONTENT INTO l_blob
FROM apex_application_temp_files
where NAME= :P5_PRODUCT_IMAGE ;

blob_to_file(p_blob => l_blob,
p_dir => 'TEST_DIR',
p_filename => :P5_PRODUCT_IMAGE);
END;