Procedurs to Upload and Download files into Oracle Database

1. Create Table into database

CREATE TABLE MY_BLOBS
(
  ID        VARCHAR2(255 BYTE),
  BLOB_COL  BLOB
)
;

2. Upload Procudure

CREATE OR REPLACE PROCEDURE upload_file (file_name IN VARCHAR2)
AS
   src_loc    BFILE := BFILENAME ('EXT_DIR', file_name);
   dest_loc   BLOB;
  
   lv_id varchar2(50);
  
BEGIN

    select substr(file_name,1,instr(file_name,'.')-1)
        into lv_id
    from dual;


   INSERT INTO my_blobs
        VALUES (lv_id, EMPTY_BLOB ())
     RETURNING blob_col
          INTO dest_loc;

   DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
   DBMS_LOB.OPEN (dest_loc, DBMS_LOB.lob_readwrite);
   DBMS_LOB.loadfromfile (dest_lob      => dest_loc,
                          src_lob       => src_loc,
                          amount        => DBMS_LOB.getlength (src_loc)
                         );
   DBMS_LOB.CLOSE (dest_loc);
   DBMS_LOB.CLOSE (src_loc);
   COMMIT;
END;
/


3. Download Procedure

CREATE OR REPLACE PROCEDURE download_file (file_name IN VARCHAR2)
AS

  l_file       UTL_FILE.file_type;
   l_buffer     RAW (32767);
   l_amount     BINARY_INTEGER     := 32767;
   l_pos        INTEGER            := 1;
   l_blob       BLOB;
   l_blob_len   INTEGER;
BEGIN
-- Get LOB locator
   SELECT BLOB_COL
     INTO l_blob
     FROM my_blobs
    WHERE ID = file_name;

   l_blob_len := DBMS_LOB.getlength (l_blob);
-- Open the destination file.
   l_file := UTL_FILE.fopen ('EXT_DIR', file_name, '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 (l_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;
/