--https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584
--https://asktom.oracle.com/pls/apex/asktom.search?tag=reading-files-in-a-directory-how-to-get-a-list-of-available-files#1565062600346635117

-- dbms_java.grant_permission( 'SCHEMA_X', 'SYS:java.io.FilePermission', '/cifs/-', 'read,write,delete' );

DROP TYPE dir_array
/
CREATE OR REPLACE TYPE dir_entry AS OBJECT (
    file_type   VARCHAR2(1)
   ,readable    VARCHAR2(1)
   ,writeable   VARCHAR2(1)
   ,hidden      VARCHAR2(1)
   ,file_size   NUMBER
   ,modified    DATE
   ,name        VARCHAR2(4000)
);
/

CREATE TYPE dir_array AS TABLE OF dir_entry;
/

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DirUtil" as

import java.io.File;
import java.io.FilenameFilter;
import java.io.IOException;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Timestamp;

import oracle.sql.ARRAY;
import oracle.sql.STRUCT;
import oracle.sql.ArrayDescriptor;
import oracle.sql.StructDescriptor;

import oracle.jdbc.driver.OracleDriver;

public class DirUtil {

    private static Connection conn;

    static {
        try {
            conn = (new OracleDriver()).defaultConnection();
        } catch (SQLException e) {
            System.out.println(e);
        }
    }


    /**
     * List the files in the directory represented by the given Oracle DIRECTORY
     * object.
     *
     * @param dirname The name of the DIRECTORY object for which we want to list
     * the files (case sensitive).
     * @throws IOException
     * @throws SQLException
     */
    public static ARRAY listFiles(String dirname)
        throws IOException, SQLException {

        String dirpath = getDirectoryPath(dirname);
        File directory = getDirectory(dirpath);

        STRUCT[] ret = fileList(directory);

        // Create an array descriptor and return it.
        ArrayDescriptor desc = ArrayDescriptor.createDescriptor (
            "DIR_ARRAY", conn);

        return new ARRAY(desc, conn, ret);
    }

    /**
     * Create a File object with the abstract pathname given by the parameter.
     *
     * @param dirpath The filesystem path of the directory
     * @throws IOException If the directory represented by this pathname does
     * not exist, or if it is a file.
     */
    private static File getDirectory(String dirpath) throws IOException {

        File directory = new File(dirpath);

        if(!directory.exists()) {
            throw new IOException("Directory: "+dirpath+" does not exist.");
        }
        if(!directory.isDirectory()) {
            throw new IOException("Path: "+dirpath+" is not a directory.");
        }

        return directory;
    }

    /**
     * Get the filesystem path for the Oracle DIRECTORY object given by the
     * input parameter.
     *
     * @param dir The name of the DIRECTORY object for which we want the path.
     * @throws IOException If there is no DIRECTORY object with the given name.
     */
    private static String getDirectoryPath(String dir)
        throws SQLException, IOException {
        String sql = "select directory_path from all_directories where " +
            "directory_name = ?";

        PreparedStatement s = conn.prepareStatement(sql);
        s.setString(1, dir);
        ResultSet rs = s.executeQuery();

        // There should be one row and one only.
        if(rs.next()) {
            return rs.getString(1);
        } else {
            throw new IOException("Directory object "+dir+" does not exist.");
        }

    }

    /**
     * Create an array of STRUCT objects representing the files in the given
     * directory.
     *
     * @param directory The File object representing the directory.
     * @throws SQLException
     */
    private static STRUCT[] fileList(File directory) throws SQLException {

        // Create the array of files to add.
        File[] files = directory.listFiles (
            new FilenameFilter() {
                // Accept all files
                public boolean accept(File dirpath, String name) {
                    return true;
                }
            }
        );

        // No files in directory
        if(files == null) {
            return null;
        }

        STRUCT[] ret = new STRUCT[files.length];

        // Create the struct entry for each file.
        for(int i=0; i<files.length; i++) {
            File f = files[i];
            StructDescriptor sd = StructDescriptor.createDescriptor (
                "DIR_ENTRY", conn);
            Object[] attributes = {
                f.isDirectory() ? "D" : (f.isFile() ? "F" : "U"),
                f.canRead() ? "Y" : "N",
                f.canWrite() ? "Y" : "N",
                f.isHidden() ? "Y" : "N",
                new Long(f.length()),
                new Timestamp(f.lastModified()),
                f.getName()
            };
            STRUCT s = new STRUCT(sd, conn, attributes);
            ret[i] = s;
        }

        return ret;
    }

    public static void moveFile(String directoryOld, String nameOld, String directoryNew, String nameNew)
        throws SQLException, IOException {

           String dirpathOld = getDirectoryPath(directoryOld);
           String dirpathNew = getDirectoryPath(directoryNew);

           File afile = new File(dirpathOld + "/" + nameOld);

           if (afile.renameTo(new File(dirpathNew + "/" + nameNew))){
//            System.out.println("File is moved successful!");
           } else {
               throw new IOException("Datei konnte nicht verschoben werden.");
           }

    }

    public static void deleteFile(String directory, String name)
        throws SQLException, IOException {

           String dirpath = getDirectoryPath(directory);

           File afile = new File(dirpath + "/" + name);

           afile.delete();

    }

}
/

CREATE OR REPLACE PACKAGE mam_file_java_util
AS

FUNCTION ls
    (
     directory          IN VARCHAR2
    )
    RETURN dir_array;

/**
* Move a file
* @param    directorySource  current directory
* @param    filenameOld      current filename
* @param    directoryTarget  new directory
* @param    filenameNew      new filename
*/
PROCEDURE move
    (
     directorySource     IN all_directories.directory_name%TYPE
    ,filenameOld         IN VARCHAR2
    ,directoryTarget     IN all_directories.directory_name%TYPE
    ,filenameNew         IN VARCHAR2
    );

/**
* Delete a file
* @param    directory   current directory
* @param    filename    current filename
*/
PROCEDURE delete
    (
     directory          IN all_directories.directory_name%TYPE
    ,filename           IN VARCHAR2
    );

END mam_file_java_util;
/

CREATE OR REPLACE PACKAGE BODY mam_file_java_util
AS

FUNCTION ls
    (
     directory          IN VARCHAR2
    )
    RETURN dir_array
IS
   LANGUAGE JAVA
   NAME 'DirUtil.listFiles(java.lang.String) return oracle.sql.ARRAY';

PROCEDURE move
    (
     directorySource    IN all_directories.directory_name%TYPE
    ,filenameOld        IN VARCHAR2
    ,directoryTarget    IN all_directories.directory_name%TYPE
    ,filenameNew        IN VARCHAR2
    )
IS
    LANGUAGE JAVA
    NAME 'DirUtil.moveFile(java.lang.String,java.lang.String,java.lang.String,java.lang.String)';

PROCEDURE delete
    (
     directory          IN all_directories.directory_name%TYPE
    ,filename           IN VARCHAR2
    )
IS
    LANGUAGE JAVA
    NAME 'DirUtil.deleteFile(java.lang.String,java.lang.String)';

END mam_file_java_util;
/

/*
SELECT  *
FROM    TABLE(mam_file_java_util.ls('MY_DIRECTORY'))
WHERE   name LIKE 'T%'
AND     modified >= SYSDATE - 3
AND     file_size > 10;
*/
