title: Loading Files to oracle database
body: <p>Scenario </p>
  
  <ul>
  <li>I have a root directory on my oracle database server <em>/rootFolder/myDir/</em></li>
  <li>I currently have 20 sub folders under <em>/myDir/</em> which could expand to a couple more</li>
  <li>Within those 20 sub folders there are an ever changing amount of Documents of Type PDF,DOC,xlsx etc.</li>
  <li>I need to be able to <strong>Dynamically load all the documents from <em>/myDir/</em> to the last folder as blob objects into a table.</strong></li>
  </ul>
  
  <p>What I've been able to do thus far.</p>
  
  <p>I have created a Java stored procedure that will traverse the directory structure given to it and write a <code>FileName</code>, <code>FilePath</code>, <code>len</code>, <code>Date</code> to table <code>WEB_DIRLIST</code>  </p>
  
  <pre><code>DROP JAVA SOURCE BALT_CHECK."WebDocs";
  
  CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED BALT_CHECK."WebDocs" as import java.io.*;
  import java.sql.*;
  import java.util.Date;
  import java.text.SimpleDateFormat;
  import java.lang.String;
  
  public class WebDocs
  {
  public static long fileID;
  public static void GetDocs(String rootdirectory) throws SQLException
  {
      walkin(rootdirectory);
  }
  
  public static void walkin(String rootdirectory) throws SQLException
  {
      File path = new File( rootdirectory );
      String[] DirList = path.list();       
  
       for( int x = 0; x &lt; DirList.length; x++)
      {
          String newPath = rootdirectory  + DirList[x]; 
           if (newPath != null) {
              File f = new File(newPath);
              if (f.isDirectory()) {
  
              String dirName = DirList[x];
               GetDocs(newPath +"/");
              }
              if (f.isFile()){
               WriteFile(f);    
              }else{
              }
          }
      }    
   }
  
  public static void WriteFile(File file) throws SQLException
   {
      String fileName;
      String filePath; 
      String elementID;
      long len;
      Date date;
      String sqlDate;
      SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss");
  
      fileID = fileID + 1;
  
      elementID = String.valueOf(fileID);
      fileName = file.getName();
      filePath = file.getPath();
      len = file.length();
      date = new Date(file.lastModified());
      sqlDate = df.format(date);
  
  
  #sql { INSERT INTO WEB_DIRLIST (ID, FILE_NAME, FILE_SIZE,  CREATE_DATE, FILE_PATH)
           VALUES (:elementID, :fileName, :len, to_date(:sqlDate, 'YYYY-MM-DD HH24:MI:SS'), :filePath) };
  
   }        
  
   }
  
  /
  </code></pre>
  
  <p>This works perfectly and my intention was to have a pl/sql stored procedure use the values from WEB_DIRLIST as input to a procedure that took the path and file name and uploaded to a table WEB_STATICDOCS</p>
  
  <p>Here is the code I have for that.</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE BALT_CHECK.insert_doc as 
  f_lob BFILE;
  b_lob BLOB;
  BEGIN
  INSERT INTO WEB_DOCS VALUES('2',EMPTY_BLOB(),'PDF','Acctg001-travelexpensereport.pdf')
  RETURN blob_col INTO b_lob;
  
  f_lob := BFILENAME('ACCTG','Acctg001-travelexpensereport.pdf'); 
  dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
  dbms_lob.loadfromfile( b_lob, f_lob, dbms_lob.getlength(f_lob));
  dbms_lob.fileclose(f_lob);
  COMMIT;
  END;
  /
  </code></pre>
  
  <p>I have hardcoded the BFILENAME input parameters but I want that to be dynamic.</p>
  
  <p>This code works with no problems when I hard code the Name and create a directory entry within oracle for the folder i'm uploading from. (in this case the directory name is called 'ACCTG'.  From what i've been able to find, I have to create a directory entry within oracle for every directory I want to read from.... Is that the only way? What if I don't know the directory? What if there are sub dir within sub dirs? </p>
  
  <p>I would prefer to do this from one statement if possible.  Can this be done from the Java Stored Procedure? If so How?   </p>
  
  <p><strong>EDIT</strong></p>
  
  <p>based on the example provided here by @justin-cave
  <a href="http://www.idevelopment.info/data/Programming/java/jdbc/LOBS/BLOBFileExample.java" rel="nofollow">http://www.idevelopment.info/data/Programming/java/jdbc/LOBS/BLOBFileExample.java</a></p>
  
  <p>How does his program know the path of where the file is located on the server to upload?  He passes the name around but </p>
  
  <pre><code>/**
       * Method used to write binary data contained in a file to an Oracle BLOB
       * column. The method used to write the data to the BLOB uses the putBytes()
       * method. This is one of two types of methods used to write binary data to
       * a BLOB column. The other method uses Streams.
       * 
       * @throws java.io.IOException
       * @throws java.sql.SQLException
       */
      public void writeBLOBPut() 
              throws IOException, SQLException {
  
          FileInputStream     inputFileInputStream    = null;
          String              sqlText                 = null;
          Statement           stmt                    = null;
          ResultSet           rset                    = null;
          BLOB                image                   = null;
          int                 chunkSize;
          byte[]              binaryBuffer;
          long                position;
          int                 bytesRead               = 0;
          int                 bytesWritten            = 0;
          int                 totbytesRead            = 0;
          int                 totbytesWritten         = 0;
  
          try {
  
              stmt = conn.createStatement();
  
              inputBinaryFile      = new File(inputBinaryFileName);
              inputFileInputStream = new FileInputStream(inputBinaryFile);
  
              sqlText =
                  "INSERT INTO test_blob (id, image_name, image, timestamp) " +
                  "   VALUES(1, '" + inputBinaryFile.getName() + "', EMPTY_BLOB(), SYSDATE)";
              stmt.executeUpdate(sqlText);
  
              sqlText = 
                  "SELECT image " +
                  "FROM   test_blob " +
                  "WHERE  id = 1 " +
                  "FOR UPDATE";
              rset = stmt.executeQuery(sqlText);
              rset.next();
              image = ((OracleResultSet) rset).getBLOB("image");
  
              chunkSize = image.getChunkSize();
              binaryBuffer = new byte[chunkSize];
  
              position = 1;
              while ((bytesRead = inputFileInputStream.read(binaryBuffer)) != -1) {
                  bytesWritten = image.putBytes(position, binaryBuffer, bytesRead);
                  position        += bytesRead;
                  totbytesRead    += bytesRead;
                  totbytesWritten += bytesWritten;
              }
  
              inputFileInputStream.close();
  
              conn.commit();
              rset.close();
              stmt.close();
  
              System.out.println(
                  "==========================================================\n" +
                  "  PUT METHOD\n" +
                  "==========================================================\n" +
                  "Wrote file " + inputBinaryFile.getName() + " to BLOB column.\n" +
                  totbytesRead + " bytes read.\n" +
                  totbytesWritten + " bytes written.\n"
              );
  
          } catch (IOException e) {
              System.out.println("Caught I/O Exception: (Write BLOB value - Put Method).");
              e.printStackTrace();
              throw e;
          } catch (SQLException e) {
              System.out.println("Caught SQL Exception: (Write BLOB value - Put Method).");
              System.out.println("SQL:\n" + sqlText);
              e.printStackTrace();
              throw e;
          }
  
      }
  </code></pre>