OraMatt: YABAOracle

This is one of those topics that EVERYONE has a different take on and here’s mine!

Using the LS_TABLE external table that you can find here you’ll be able to loop through all the files in a directory and load them into a table.

Here we go!

/* Create a table in the database to load the files into */
create table oracle_files (filename varchar2(100), filesize number, filecontents blob) 
  lob (filecontents) store as lob_filecontents (enable storage in row);


/* Load the files */
create or replace procedure load_files
  as
  cursor c1 IS SELECT "file_name" from ls_table where "file_size" > 0;
    
  strfilename varchar2(100);
  fhdatafile bfile;
  tempblob blob;
  vfilesize number;
  ncommitcounter number;

begin
  
  FOR i IN c1
  LOOP
    strfilename := i."file_name";
    dbms_lob.createtemporary(tempblob, true);  
    dbms_lob.open(tempblob, dbms_lob.lob_readwrite);
    fhdatafile := bfilename ('USER_DOCS_DIR', strfilename);
    vfilesize := dbms_lob.getlength(fhdatafile);
    dbms_lob.open(fhdatafile, dbms_lob.lob_readonly);
    dbms_lob.loadfromfile(tempblob, fhdatafile, dbms_lob.lobmaxsize);
  
    insert into oracle_files (filename, filesize, filecontents)
    values (strfilename, vfilesize, tempblob);
    
    dbms_output.put_line(strfilename||' '||vfilesize);
      
    dbms_lob.close(tempblob);
    dbms_lob.close(fhdatafile);
  END LOOP;

  COMMIT;
END;
/