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

 }

 }

/

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

/**
     * 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 METHODn" +
                "==========================================================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;
        }

    }