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. 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>
Posts
