8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 8i » Here

File Handling From PL/SQL

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

This should only be used as a last resort if the functionality you require is not available using the UTL_FILE package. As mentioned later, you need to be very careful what file system privileges you grant and/or who you give access to this functionality. If used unwisely, this could allow someone to damage files on the server, or cause a major security breach.

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. Notice no package body is required since it only contains 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 to Give JServer Access to the Filesystem

In this example we are granting access to all directories on the server. That is really dangerous. You need to be more specific about these grants and/or be very careful about who you grant access to this functionality.

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.

Test It

Finally we call the FILE_API 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;
/

List Files in a Directory

We can use the LIST function in the FILE_API package to list files and sub-directories in a directory. Notice the files are presented as a comma-separated list.

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('Output : ' ||  File_API.list ('/u01/app/oracle'));
END;
/
Output : admin,audit,cfgtoollogs,checkpoints,diag,product


PL/SQL procedure successfully completed.

SQL>

We can split the list into an array. There are a number of ways to do that, but this method uses the APEX_STRING package.

DECLARE
  l_array APEX_APPLICATION_GLOBAL.vc_arr2;
  l_string varchar2(32767);
BEGIN
  l_array:= APEX_STRING.string_to_table(File_API.list ('/u01/app/oracle'), ',');

  FOR i in 1..l_array.count LOOP
    DBMS_OUTPUT.put_line('Array(' || i || ') : ' || l_array(i));
  END LOOP;
END;
/
Array(1) : admin
Array(2) : audit
Array(3) : cfgtoollogs
Array(4) : checkpoints
Array(5) : diag
Array(6) : product


PL/SQL procedure successfully completed.

SQL>

Hope this helps. Regards Tim...

Back to the Top.