Reading files from directory

First we need to create a directory in oracle that will point to a location on the disk.

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\test';

We can see the directory in the system view dba_directories.

This procedure reads the file and stores it as a blob in a table test.

create or replace procedure read_blob(p_dir varchar2, p_file_name varchar2) is
  v_blob         blob;
  v_bfile        bfile;
  v_dest_offset  pls_integer;
  v_src_offset   pls_integer;
  v_name varchar2(100) := p_file_name;
begin
      dbms_lob.createtemporary(v_blob, true, dbms_lob.session);
      v_bfile := bfilename(p_dir, v_name);
      dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
      v_dest_offset  := 1;
      v_src_offset   := 1;      
      dbms_lob.loadblobfromfile(v_blob, v_bfile, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset);
      
      insert into test
        (name, content)
      values
        (v_name, v_blob);      

      dbms_lob.fileclose(v_bfile);
      dbms_lob.freetemporary(v_blob);
end read_blob;