8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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
- Publish the Java Call Specification
- Grant Privileges to Give JServer Access to the Filesystem
- Test It
- List Files in a Directory
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...