Loading Files to oracle database

Question

Scenario

• I have a root directory on my oracle database server /rootFolder/myDir/
• I currently have 20 sub folders under /myDir/ which could expand to a couple more
• Within those 20 sub folders there are an ever changing amount of Documents of Type PDF,DOC,xlsx etc.
• I need to be able to Dynamically load all the documents from /myDir/ to the last folder as blob objects into a table.

What I've been able to do thus far.

I have created a Java stored procedure that will traverse the directory structure given to it and write a FileName, FilePath, len, Date to table WEB_DIRLIST

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 < 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) };

 }        

 }

/

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

Here is the code I have for that.

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;
/

I have hardcoded the BFILENAME input parameters but I want that to be dynamic.

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?

I would prefer to do this from one statement if possible. Can this be done from the Java Stored Procedure? If so How?

EDIT

based on the example provided here by @justin-cave http://www.idevelopment.info/data/Programming/java/jdbc/LOBS/BLOBFileExample.java

How does his program know the path of where the file is located on the server to upload? He passes the name around but

/**
     * 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;
        }

    }

Answer 1

If you wanted to load the files via PL/SQL, you'd need to run a CREATE DIRECTORY statement for every directory and subdirectory that you want to read data from. That's undoubtedly possible but it's rather clunky.

Since you already have a Java stored procedure that is walking your directory structure, it seems much more logical to load the data into your BLOB column from Java rather than from PL/SQL. There are any number of examples of writing data from a file to a BLOB on the net. I'd start with the writeBLOBPut method in this example but you could also start from the Using Large Objects page in the Java tutorials.