Managing files and folders using Java in Oracle PL/SQL

Collection of Java utilities for work with files anf folders in Oracle PL/SQL.
For testing I used Java 6 and Oracle 11.2.0.1.

1. Create a number of important facilities for testing and the work of our utilities.

CREATE TABLE BLOB_TAB
( F_ID    INTEGER,
  F_NAME  VARCHAR2(128 BYTE),
  F_BLOB  BLOB,
  F_LEN   VARCHAR2(20 BYTE) );

CREATE SEQUENCE BLOB_TAB_SEQ
  START WITH 1
  MAXVALUE 9999999
  MINVALUE 1
  NOCYCLE
  CACHE 5
  ORDER;

CREATE GLOBAL TEMPORARY TABLE ATTR_FILES
( DIRNAME   VARCHAR2(255 BYTE),
  FILENAME  VARCHAR2(255 BYTE),
  LENGTH    NUMBER,
  MOD_DATE  DATE,
  TYPE      VARCHAR2(1 BYTE) )
ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE TYPE LIST_FILES AS TABLE OF VARCHAR2(255);

2. Create Java source named «Lobster».

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Lobster" as
import java.io.*;
import java.sql.*;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import oracle.jdbc.OracleTypes;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.text.SimpleDateFormat;
import java.util.Date;
import oracle.sql.driver.*;
import oracle.sql.ArrayDescriptor;
import oracle.sql.ARRAY;

// What's: Collection java utilities for work with files, folders, CLOB and BLOB.
// Author: souluran adn Denis Popov (http://www.sql.ru/faq/faq_topic.aspx?fid=469)

public class Lobster {

// Procedure set encoding for Java = cp1251 (for situations where database character set is AL32UTF8), otherwise there not will be read Russian characters
  public static void encod()
  {
    System.setProperty("file.encoding","cp1251");
  }

// Procedure get list of file names in directory
  public static ARRAY list_files(String dirname)
  throws SQLException {
    encod();
    Connection con = null;
    ARRAY listed = null;
    con = getConnection();
    File dir = new File(dirname);
    try{
        if (dir.exists()) {
            ArrayDescriptor arrayDescriptor = new ArrayDescriptor("LIST_FILES",con);
            listed = new ARRAY(arrayDescriptor,con,((Object[])dir.list()));
        }
    } finally {
    if (con != null) {con.close();}
      }
  return listed;
  }

// Procedure get list of attributes files in directory
  public static void list_files_attr(String dirname)
  throws SQLException {
     encod();
     File path = new File(dirname);

     if (path.exists()) {

        String[] list = path.list();
        String element;

        File fileObject;
        long length;
        String dateStr, type;
        Date now = new Date();
        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy H:m:s");

        for(int i = 0; i < list.length; i++)
        {
            element = list[i];
            fileObject = new File(dirname + File.separatorChar + list[i]);
            length = fileObject.length();
            if (fileObject.isDirectory()) {
              type = "d";
            } else if (fileObject.isFile()) {
              type = "f";
            } else {
              type = "?";
            }

            java.util.Date d = new java.util.Date(fileObject.lastModified());
            dateStr = format.format(d);

            #sql { INSERT INTO ATTR_FILES (dirname, filename, length, mod_date, type)
                   VALUES (:dirname, :element, :length, to_date(:dateStr, 'dd.mm.yyyy hh24:mi:ss'), :type) };
        }
     }
  }

// Function get modified time file
  public static String getfiletime(String dirname, String fileName)
  throws SQLException {
     encod();
     File path = new File(dirname + "\\" + fileName);
     String dateStr = null;
     if (path.exists()) {
        Date now = new Date();
        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy H:m:s");
        java.util.Date d = new java.util.Date(path.lastModified());
        dateStr = format.format(d);
     }
     return dateStr;
  }

// Function check exixsting file in directory
  public static oracle.sql.NUMBER existsfile(String dirname, String fileName) throws Exception {
    //System.setProperty("file.encoding","cp1251");
    encod();
    int res = 0;
    File theDir = new File(dirname + "\\" +fileName);
    if (!theDir.exists()) {
        res = 0;
     }
    else {
        res = 1;
     }
   return new oracle.sql.NUMBER(res);
  }

// Procedure move file
  public static void movefile(String dirname, String fileName, String newdirname, String newfileName)
  throws IOException {
    encod();
    //String myfile = new String(fileName.getBytes("windows-1251"), "windows-1251");
    File srcFile = new File(dirname + "\\" + fileName);
    File destFile = new File(newdirname + "\\" + newfileName);
    InputStream input = null;
    OutputStream output = null;
    try {
        input = new FileInputStream(srcFile);
        output = new FileOutputStream(destFile);
        byte[] buf = new byte[1024];
        int bytesRead;
        while ((bytesRead = input.read(buf)) > 0) {
        output.write(buf, 0, bytesRead);
        }
    } finally {
        input.close();
        output.close();
        srcFile.delete();
    }
  }

// Procedure copy file
  public static void copyfile(String dirname, String fileName, String newdirname, String newfileName)
  throws Exception, IOException {
    encod();
    File srcFile = new File(dirname + "\\" + fileName);
    File destFile = new File(newdirname + "\\" + newfileName);
    InputStream input = null;
    OutputStream output = null;
    try {
        input = new FileInputStream(srcFile);
        output = new FileOutputStream(destFile);
        byte[] buf = new byte[1024];
        int bytesRead;
        while ((bytesRead = input.read(buf)) > 0) {
        output.write(buf, 0, bytesRead);
        }
    } finally {
        input.close();
        output.close();
    }

  }

// Procedure delete file
  public static void deletefile(String dirname, String fileName)
  throws IOException {
    System.setProperty("file.encoding","cp1251");
    try {
        File srcFile = new File(dirname + "\\" + fileName);
        srcFile.delete();
    } finally {
    }
  }

// Procedure create new directory in Windows
  public static void newdir(String dirname)
  throws Exception {
    encod();
    File theDir = new File(dirname);
    // if the directory does not exist, create it
    if (!theDir.exists()) {
    //System.out.println("creating directory: " + dirname);
    boolean result = false;
        try{
            theDir.mkdir();
            result = true;
            }
        catch(SecurityException se){
            //handle it
            }
    if(result) {
               //System.out.println(dirname + " created");
               }
                          }
  }

// Function check exixsting directory in Windows
  public static oracle.sql.NUMBER existsdir(String dirname) throws Exception {
    encod();
    int res = 0;
    File theDir = new File(dirname);
    if (!theDir.exists()) {
        res = 0;
     }
    else {
        res = 1;
     }
   return new oracle.sql.NUMBER(res);
  }

// Procedure recursive search files in directory
  public static void recursivecopy(File src, File dest)
  throws Exception, IOException  {
    encod();
        if(src.isDirectory()){

            //if directory not exists, create it
            if(!dest.exists()){
               dest.mkdir();
               System.out.println("Directory copied from "
                              + src + "  to " + dest);
            }

            //list all the directory contents
            String files[] = src.list();

            for (String file : files) {
               //construct the src and dest file structure
               File srcFile = new File(src, file);
               File destFile = new File(dest, file);
               //recursive copy
               recursivecopy(srcFile, destFile);
            }

        }else{
            //if file, then copy it
            //Use bytes stream to support all file types
            InputStream in = new FileInputStream(src);
                OutputStream out = new FileOutputStream(dest);

                byte[] buffer = new byte[1024];

                int length;
                //copy the file content in bytes
                while ((length = in.read(buffer)) > 0){
                   out.write(buffer, 0, length);
                }

                in.close();
                out.close();
                System.out.println("File copied from " + src + " to " + dest);
        }
  }

// Procedure copy directory in Windows
  public static void copydir(String srcDir, String destDir) throws Exception, IOException {
        encod();
        File src = new File(srcDir);
        File dest = new File(destDir);
        recursivecopy(src, dest);
  }

// Procedure recursive search in directory
  private static void recursiveDelete(File file) {
      System.setProperty("file.encoding","cp1251");
         if (!file.exists()) return;
         if (file.isDirectory()) {
            for (File f : file.listFiles()) recursiveDelete(f);
            file.delete();
         } else {
            file.delete();
         }
  }

// Procedure delete directory with all contetns in Windows
  public static void deletedir(String dirname) throws IOException  {
         encod();
         File directory = new File(dirname);
         recursiveDelete(directory);
  }

// Function import file into CLOB
  public static CLOB file2clob(String dirname, String fileName)
    throws SQLException, IOException {
    Connection con = null;
    CLOB clob = null;
    Writer writer = null;
    InputStream in = null;
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      clob = CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);
      // For Oracle 8i.
//      clob = createTemporaryClob(con);
      writer = clob.getCharacterOutputStream();
      in = new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName)));
      byte[] buf = new byte[clob.getChunkSize()];
      int length;
      while ((length = in.read(buf)) != -1) {
        writer.write(new String(buf, 0, length));
      }
    } finally {
      if (in != null) {in.close();}
      if (writer != null) {writer.close();}
      if (con != null) {con.close();}
    }
    return clob;
  }

// Function import file with characterSet into CLOB
  public static CLOB file2clob(String dirname, String fileName, String characterSet)
    throws SQLException, IOException {
    Connection con = null;
    CLOB clob = null;
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      clob = CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);
      // For Oracle 8i.
//      clob = createTemporaryClob(con);
      writer = clob.getCharacterOutputStream();
      reader = new InputStreamReader(
        new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName))), characterSet
      );
      char[] chars = new char[clob.getChunkSize()];
      int iChar;
      while ((iChar = reader.read(chars)) != -1) {
        writer.write(chars, 0, iChar);
      }
    } finally {
      if (reader != null) {reader.close();}
      if (writer != null) {writer.close();}
      if (con != null) {con.close();}
    }
    return clob;
  }

// Procedure export CLOB into file
  public static void clob2file(CLOB clob, String dirname, String fileName)
    throws SQLException, IOException {
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      writer = new BufferedWriter(new FileWriter(dirname + "\\" + fileName));
      reader = new BufferedReader(clob.getCharacterStream());
      int length;
      char[] buf = new char[clob.getChunkSize()];
      while ((length = reader.read(buf, 0, clob.getChunkSize())) != -1) {
        writer.write(buf, 0, length);
      }
    } finally {
      if (writer != null) {writer.close();}
      if (reader != null) {reader.close();}
    }
  }

// Procedure export CLOB into file with characterSet
  public static void clob2file(CLOB clob, String dirname, String fileName, String characterSet)
    throws SQLException, IOException {
    Writer writer = null;
    Reader reader = null;
    try {
      encod();
      writer = new OutputStreamWriter(
        new BufferedOutputStream(new FileOutputStream(new File(dirname + "\\" + fileName))), characterSet
      );
      reader = new BufferedReader(clob.getCharacterStream());
      int length;
      char[] buf = new char[clob.getChunkSize()];
      while ((length = reader.read(buf, 0, clob.getChunkSize())) != -1) {
        writer.write(buf, 0, length);
      }
    } finally {
      if (writer != null) {writer.close();}
      if (reader != null) {reader.close();}
    }
  }

// Function import file into BLOB
  public static BLOB file2blob(String dirname, String fileName)
    throws SQLException, IOException {
    Connection con = null;
    BLOB blob = null;
    OutputStream out = null;
    InputStream in = null;
    //Locale.setDefault(new Locale("ru", "RUS", "WIN"));
    try {
      encod();
      con = getConnection();
      // For Oracle 9i and above.
      blob = BLOB.createTemporary(con, true, BLOB.DURATION_SESSION);
      // For Oracle 8i.
//      blob = createTemporaryBlob(con);
      out = blob.getBinaryOutputStream();
      String myfile = null;
      in = new BufferedInputStream(new FileInputStream(new File(dirname + "\\" + fileName)));
      int length;
      int chunkSize = blob.getChunkSize();
      byte[] buf = new byte[chunkSize];
      while ((length = in.read(buf)) != -1) {
        out.write(buf, 0, length);
      }
    } finally {
      if (in != null) {in.close();}
      if (out != null) {out.close();}
      if (con != null) {con.close();}
    }
    return blob;
  }

// Procedure export BLOB into file
  public static void blob2file(BLOB blob, String dirname, String fileName)
    throws SQLException, IOException {
    InputStream in = null;
    OutputStream out = null;
    try {
      encod();
      out = new BufferedOutputStream(new FileOutputStream(new File(dirname + "\\" + fileName)));
      in = blob.getBinaryStream();
      int length;
      byte[] buf = new byte[blob.getChunkSize()];
      while ((length = in.read(buf)) != -1) {
        out.write(buf, 0, length);
      }
    } finally {
      if (in != null) {in.close();}
      if (out != null) {out.close();}
    }
  }

// Function connection
  public static Connection getConnection()
    throws SQLException {
    return DriverManager.getConnection("jdbc:default:connection:");
  }

// Function create temporary CLOB
  public static CLOB createTemporaryClob(Connection con)
    throws SQLException {
    CallableStatement cst = null;
    try {
      cst = con.prepareCall("{call dbms_lob.createTemporary(?, false, dbms_lob.SESSION)}");
      cst.registerOutParameter(1, OracleTypes.CLOB);
      cst.execute();
      return (CLOB)cst.getClob(1);
    } finally {
      if (cst != null) { cst.close(); }
    }
  }

// Function create temporary BLOB
  public static BLOB createTemporaryBlob(Connection con)
    throws SQLException {
    CallableStatement cst = null;
    try {
      cst = con.prepareCall("{call dbms_lob.createTemporary(?, false, dbms_lob.SESSION)}");
      cst.registerOutParameter(1, OracleTypes.BLOB);
      cst.execute();
      return (BLOB)cst.getBlob(1);
    } finally {
      if (cst != null) { cst.close();
        }
    }
  }

}
/

3. Then create a package of «Lobster» for publication the Java call specification, so we can access it via PL/SQL.

CREATE OR REPLACE PACKAGE Lobster as

/* Package of utilities for work with files, folders, CLOB and BLOB */

/* get list of files from directory */
function list_files(p_dir_name VARCHAR2) RETURN list_files AS
language java name 'Lobster.list_files(java.lang.String) return oracle.sql.ARRAY';

/* get list of attributes files from directory */
procedure list_files_attr(p_dir_name varchar2) as
language java name 'Lobster.list_files_attr(java.lang.String)';

/* file exists or not */
function existsfile(p_dir_name varchar2, p_file_name varchar2) return number as
language java name 'Lobster.existsfile(java.lang.String, java.lang.String) return oracle.sql.NUMBER';

/* move file to another directory */
procedure movefile(p_dir_name varchar2, p_file_name varchar2, p_newdir_name varchar2, p_newfile_name varchar2) as
language java name 'Lobster.movefile(java.lang.String, java.lang.String, java.lang.String, java.lang.String)';

/* copy file to another directory */
procedure copyfile(p_dir_name varchar2, p_file_name varchar2, p_newdir_name varchar2, p_newfile_name varchar2) as
language java name 'Lobster.copyfile(java.lang.String, java.lang.String, java.lang.String, java.lang.String)';

/* delete file from directory */
procedure deletefile (p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.deletefile(java.lang.String, java.lang.String)';

/* create new directory or subdirectory */
procedure newdir(p_dir_name varchar2) as
language java name 'Lobster.newdir(java.lang.String)';

/* directory exists or not */
function existsdir(p_dir_name varchar2) return number as
language java name 'Lobster.existsdir(java.lang.String) return oracle.sql.NUMBER';

/* delete directory or subdirectory with all contents */
procedure deletedir(p_dir_name varchar2) as
language java name 'Lobster.deletedir(java.lang.String)';

/* copy all contents from directory to another directory */
procedure copydir(p_dir_name varchar2, p_newdir_name varchar2) as
language java name 'Lobster.copydir(java.lang.String, java.lang.String)';

/* import file into CLOB */
function file2clob(p_dir_name varchar2, p_file_name varchar2) return clob as
language java name 'Lobster.file2clob(java.lang.String, java.lang.String) return oracle.sql.CLOB';

/* import file into CLOB with encoding */
function file2clob(p_dir_name varchar2, p_file_name varchar2, p_characterset varchar2) return clob as
language java name 'Lobster.file2clob(java.lang.String, java.lang.String, java.lang.String) return oracle.sql.CLOB';

/* import CLOB into file */
procedure clob2file(p_clob clob, p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.clob2file(oracle.sql.CLOB, java.lang.String, java.lang.String)';

/* import CLOB into file with encoding */
procedure clob2file(p_clob clob, p_dir_name varchar2, p_file_name varchar2, p_characterset varchar2) as
language java name 'Lobster.clob2file(oracle.sql.CLOB, java.lang.String, java.lang.String, java.lang.String)';

/* import file into BLOB */
function file2blob(p_dir_name varchar2, p_file_name varchar2) return blob as
language java name 'Lobster.file2blob(java.lang.String, java.lang.String) return oracle.sql.BLOB';

/* import BLOB into file */
procedure blob2file(p_blob blob, p_dir_name varchar2, p_file_name varchar2) as
language java name 'Lobster.blob2file(oracle.sql.BLOB, java.lang.String, java.lang.String)';

end;
/ 

4. Grant the Oracle JVM the relevant filesystem permissions.

DECLARE
  l_schema VARCHAR2(30) := 'USERNAME';
BEGIN
  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.util.PropertyPermission', 'file.encoding', 'write' );
END;
/

5. Examples how use package Lobster:

-- import file into BLOB
DECLARE

l_id INT DEFAULT 1;
l_dir VARCHAR2(128);
l_name VARCHAR2(128);
l_blob BLOB;

BEGIN
l_dir := 'd:\mydir\test';
l_name := 'my file.xls';

select BLOB_TAB_SEQ.NEXTVAL into l_id from dual;

INSERT INTO blob_tab
VALUES(l_id, null, EMPTY_BLOB(), 0);

SELECT f_blob INTO l_blob
FROM blob_tab
WHERE f_id = l_id
FOR UPDATE;

l_blob := lobster.file2blob(l_dir, l_name);

UPDATE blob_tab
SET f_name = l_name, f_blob = l_blob, f_len = dbms_lob.getlength(l_blob)
where f_id = l_id;
COMMIT;

END;
/

-- import BLOB into file
DECLARE
l_dir VARCHAR2(128);
l_name VARCHAR2(128);
l_blob BLOB;

BEGIN
l_dir := 'd:\mydir\test';
l_name := 'my file2.xls';

SELECT f_blob INTO l_blob
FROM blob_tab
WHERE f_id = :id;

lobster.blob2file(l_blob, l_dir, l_name);

END;
/

-- list of file name
SELECT column_value FROM TABLE(lobster.list_files('d:\mydir'));

--list of file attributes
begin
execute immediate 'truncate table ATTR_FILES';
lobster.list_files_attr('d:\mydir');
end;
/
select * from ATTR_FILES;

-- manage file and folders
declare
res number;
begin
-- copy file to another folder
lobster.copyfile('d:\mydir', 'test.txt', 'd:\my', 'test2.txt');
-- move file to another folder
lobster.movefile('d:\mydir', 'test.txt', 'd:\my', 'test.txt');
-- delete file
lobster.deletefile('d:\my', 'test.txt');
-- create new folder
lobster.newdir('d:\my5');
-- delete folder
lobster.deletedir('d:\my5');
-- copy all contetnts from folder to anothe folder
lobster.copydir('d:\my', 'd:\mytest');
-- exists file
res := lobster.existsfile('d:\my', 'test.txt');
dbms_output.put_line('file is ' || res);
res := lobster.existsdir('d:\my');
dbms_output.put_line('folder is ' || res);
end;
/