Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

File Handling From PL/SQL

Using a Java stored procedure it is possible to manipulate operating system files from PL/SQL.

Create the Java Stored Procedure

First we need to create the Java class to perform all file manipulation using the Java File Class.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileHandler" AS
import java.lang.*;
import java.util.*;
import java.io.*;
import java.sql.Timestamp;

public class FileHandler
{
  private static int SUCCESS = 1;
  private static  int FAILURE = 0;
  
  public static int canRead (String path) {
    File myFile = new File (path);
    if (myFile.canRead()) return SUCCESS; else return FAILURE;
  }

  public static int canWrite (String path) {
    File myFile = new File (path);
    if (myFile.canWrite()) return SUCCESS; else return FAILURE;
  }

  public static int createNewFile (String path) throws IOException {
    File myFile = new File (path);
    if (myFile.createNewFile()) return SUCCESS; else return FAILURE;
  }

  public static int delete (String path) {
    File myFile = new File (path);
    if (myFile.delete()) return SUCCESS; else return FAILURE;
  }

  public static int exists (String path) {
    File myFile = new File (path);
    if (myFile.exists()) return SUCCESS; else return FAILURE;
  }

  public static int isDirectory (String path) {
    File myFile = new File (path);
    if (myFile.isDirectory()) return SUCCESS; else return FAILURE;
  }

  public static int isFile (String path) {
    File myFile = new File (path);
    if (myFile.isFile()) return SUCCESS; else return FAILURE;
  }

  public static int isHidden (String path) {
    File myFile = new File (path);
    if (myFile.isHidden()) return SUCCESS; else return FAILURE;
  }

  public static Timestamp lastModified (String path) {
    File myFile = new File (path);
    return new Timestamp(myFile.lastModified());
  }

  public static long length (String path) {
    File myFile = new File (path);
    return myFile.length();
  }
  
  public static String list (String path) {
    String list = "";
    File myFile = new File (path);
    String[] arrayList = myFile.list();
    
    Arrays.sort(arrayList, String.CASE_INSENSITIVE_ORDER);
    
    for (int i=0; i < arrayList.length; i++) {
      // Prevent directory listing expanding if we will blow VARCHAR2 limit.
      if ((list.length() + arrayList[i].length() + 1) > 32767)
        break;
        
      if (!list.equals(""))
        list += "," + arrayList[i];
      else
        list += arrayList[i];
    }
    return list;
  }

  public static int mkdir (String path) {
    File myFile = new File (path);
    if (myFile.mkdir()) return SUCCESS; else return FAILURE;
  }

  public static int mkdirs (String path) {
    File myFile = new File (path);
    if (myFile.mkdirs()) return SUCCESS; else return FAILURE;
  }

  public static int renameTo (String fromPath, String toPath) {
    File myFromFile = new File (fromPath);
    File myToFile   = new File (toPath);
    if (myFromFile.renameTo(myToFile)) return SUCCESS; else return FAILURE;
  } 

  public static int setReadOnly (String path) {
    File myFile = new File (path);
    if (myFile.setReadOnly()) return SUCCESS; else return FAILURE;
  }

  public static int copy (String fromPath, String toPath) {
    try {
      File myFromFile = new File (fromPath);
      File myToFile   = new File (toPath);
  
      InputStream  in  = new FileInputStream(myFromFile);
      OutputStream out = new FileOutputStream(myToFile);
      
      byte[] buf = new byte[1024];
      int len;
      while ((len = in.read(buf)) > 0) {
        out.write(buf, 0, len);
      }
      in.close();
      out.close();
      return SUCCESS;
    }
    catch (Exception ex) {
      return FAILURE;
    }
  }
};
/
show errors java source "FileHandler"

Publish the Java call specification

Next we publish the call specification using a PL/SQL "wrapper" package. Note that no package body is required since it only containts references to Java stored procedures.

CREATE OR REPLACE PACKAGE file_api AS

FUNCTION canRead (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.canRead (java.lang.String) return java.lang.int';

FUNCTION canWrite (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.canWrite (java.lang.String) return java.lang.int';

FUNCTION createNewFile (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.createNewFile (java.lang.String) return java.lang.int';

FUNCTION delete (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.delete (java.lang.String) return java.lang.int';

FUNCTION exists (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.exists (java.lang.String) return java.lang.int';

FUNCTION isDirectory (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.isDirectory (java.lang.String) return java.lang.int';

FUNCTION isFile (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.isFile (java.lang.String) return java.lang.int';

FUNCTION isHidden (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.isHidden (java.lang.String) return java.lang.int';

FUNCTION lastModified (p_path  IN  VARCHAR2) RETURN DATE
AS LANGUAGE JAVA 
NAME 'FileHandler.lastModified (java.lang.String) return java.sql.Timestamp';

FUNCTION length (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.length (java.lang.String) return java.lang.long';

FUNCTION list (p_path  IN  VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'FileHandler.list (java.lang.String) return java.lang.String';

FUNCTION mkdir (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.mkdir (java.lang.String) return java.lang.int';

FUNCTION mkdirs (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.mkdirs (java.lang.String) return java.lang.int';

FUNCTION renameTo (p_from_path  IN  VARCHAR2,
                   p_to_path    IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.renameTo (java.lang.String, java.lang.String) return java.lang.int';

FUNCTION setReadOnly (p_path  IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.setReadOnly (java.lang.String) return java.lang.int';

FUNCTION copy (p_from_path  IN  VARCHAR2,
               p_to_path    IN  VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'FileHandler.copy (java.lang.String, java.lang.String) return java.lang.int';

END file_api;
/
SHOW ERRORS

Grant Privileges

The relevant permissions must be granted from SYS for JServer to access the file system.

EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
GRANT JAVAUSERPRIV TO SCHEMA-NAME;

The affects of the grant will not be noticed until the grantee reconnects. It is up to the individual to decide on the level of access that is required.

Depending on the documentation used, you may be told to add the directories you intend to access to your UTL_FILE_DIR parameter in the Init.ora file. I have not found this to be necessary when using the Java approach.

Test It

Finally we call the PL/SQL packaged functions from PL/SQL. An example of every syntax can be seen below.

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('canRead      : ' ||  File_API.canRead ('C:\temp\test1.txt'));
  DBMS_OUTPUT.PUT_LINE('canWrite     : ' ||  File_API.canWrite ('C:\temp\test1.txt'));
  DBMS_OUTPUT.PUT_LINE('createNewFile: ' ||  File_API.createNewFile ('C:\temp\test1.txt'));
  DBMS_OUTPUT.PUT_LINE('delete       : ' ||  File_API.delete ('C:\temp\test2.txt'));
  DBMS_OUTPUT.PUT_LINE('exists       : ' ||  File_API.exists ('C:\temp\test2.txt'));
  DBMS_OUTPUT.PUT_LINE('isDirectory  : ' ||  File_API.isDirectory ('C:\temp\test1.txt'));
  DBMS_OUTPUT.PUT_LINE('isFile       : ' ||  File_API.isFile ('C:\temp\test1.txt'));
  DBMS_OUTPUT.PUT_LINE('isHidden     : ' ||  File_API.isHidden ('C:\temp\test1.txt'));
  DBMS_OUTPUT.PUT_LINE('lastModified : ' ||  TO_CHAR(File_API.lastModified ('C:\temp\test1.txt'), 'DD-MON-YYYY HH24:MI:SS'));
  DBMS_OUTPUT.PUT_LINE('length       : ' ||  File_API.length ('C:\temp\test1.txt'));
  DBMS_OUTPUT.PUT_LINE('mkdir        : ' ||  File_API.mkdir ('C:\temp\dir1'));
  DBMS_OUTPUT.PUT_LINE('mkdirs       : ' ||  File_API.mkdirs ('C:\temp\dir2\dir3'));
  DBMS_OUTPUT.PUT_LINE('renameTo     : ' ||  File_API.renameTo ('C:\temp\test1.txt','C:\temp\test2.txt'));
  DBMS_OUTPUT.PUT_LINE('setReadOnly  : ' ||  File_API.setReadOnly ('C:\temp\test1.txt'));
  DBMS_OUTPUT.PUT_LINE('copy         : ' ||  File_API.copy ('C:\temp\test2.txt','C:\temp\test1.txt'));
END;
/

Hope this helps. Regards Tim...

Back to the Top.