8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
List Files in a Directory From PL/SQL and SQL : Java
This article shows how to list files in a directory on the database server using a Java in the database. This is a cut-down version of the general file API described in this article.
- Setup
- Create the Java Class, Call Specification and Pipelined Table Function
- Test It
- Using Views
- Pros and Cons
Related articles.
Setup
We connect to a privileged user, and create a new test user.
CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA --DROP USER testuser1 CASCADE; CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE PROCEDURE, CREATE TYPE, CREATE VIEW TO testuser1;
We grant the relevant Java privileges to the test user to allow it to access the file system. Notice we are only granting read access here. For the general file API we need to grant additional privieleges.
EXEC DBMS_JAVA.grant_permission('TESTUSER1', 'java.io.FilePermission', '<>', 'read'); EXEC DBMS_JAVA.grant_permission('TESTUSER1', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', ''); GRANT JAVAUSERPRIV TO TESTUSER1;
Create the Java Class, Call Specification and Pipelined Table Function
We connect to the new test user.
CONN testuser1/testuser1@//localhost:1521/pdb1
We create the FileListHandler
Java class.
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileListHandler" AS import java.lang.*; import java.util.*; import java.io.*; public class FileListHandler { 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; } }; / show errors java source "FileListHandler"
We create a call specification for the Java class. This could be a standalone function, but I prefer to use a package.
CREATE OR REPLACE PACKAGE file_list_api AS FUNCTION list (p_path IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'FileListHandler.list (java.lang.String) return java.lang.String'; END file_list_api; / SHOW ERRORS
We create a pipelined table function to allow convenient access to the FILE_LIST_API
package from SQL. We split the returned comma-separated list into an array. There are a number of ways to do that, but this method uses the APEX_STRING
package.
CREATE OR REPLACE TYPE t_varchar2_arr AS TABLE OF VARCHAR2(500); / CREATE OR REPLACE FUNCTION get_files (p_dir IN VARCHAR2) RETURN t_varchar2_arr PIPELINED AS l_array APEX_APPLICATION_GLOBAL.vc_arr2; l_string VARCHAR2(32767); BEGIN l_array:= APEX_STRING.string_to_table(FILE_LIST_API.list(p_dir), ','); FOR i in 1..l_array.count LOOP PIPE ROW(l_array(i)); END LOOP; RETURN; END; /
Test It
We can use the LIST
function in the FILE_LIST_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_LIST_API.list ('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')); END; / Output : alert_cdb1.log,cdb1_cjq0_18139.trc,cdb1_cjq0_18139.trm,cdb1_dbrm_15137.trc,cdb1_dbrm_15137.trm ... ... removed for brevity ... cdb1_vktm_17896.trc,cdb1_vktm_17896.trm 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(2000); BEGIN l_array:= APEX_STRING.string_to_table(FILE_LIST_API.list ('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'), ','); FOR i in 1..l_array.count LOOP DBMS_OUTPUT.put_line('Array(' || i || ') : ' || l_array(i)); END LOOP; END; / Array(1) : alert_cdb1.log Array(2) : cdb1_cjq0_18139.trc Array(3) : cdb1_cjq0_18139.trm Array(4) : cdb1_dbrm_15137.trc ... removed for brevity ... Array(531) : cdb1_vktm_17026.trm Array(532) : cdb1_vktm_17896.trc Array(533) : cdb1_vktm_17896.trm PL/SQL procedure successfully completed. SQL>
We can query the files using the GET_FILES
pipelined table function.
COLUMN file_name FORMAT A30 SELECT column_value AS file_name FROM TABLE(get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')); FILE_NAME ------------------------------ alert_cdb1.log cdb1_cjq0_18139.trc cdb1_cjq0_18139.trm ... removed for brevity ... cdb1_vktm_17896.trc cdb1_vktm_17896.trm 539 rows selected. SQL>
We can limit the output using a WHERE
clause in the normal way.
SELECT column_value AS file_name FROM TABLE(get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')) WHERE column_value LIKE 'alert%'; FILE_NAME ------------------------------ alert_cdb1.log SQL>
These queries can be used in PL/SQL.
Using Views
You can hide the internal workings by creating views over the pipelined table function. The following views display all the files in the trace directory, and just the alert log respectively.
CREATE OR REPLACE VIEW trace_files_v AS SELECT column_value AS file_name FROM TABLE(get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')); CREATE OR REPLACE VIEW alert_log_v AS SELECT column_value AS file_name FROM TABLE(get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')) WHERE column_value LIKE 'alert%';
Pros and Cons
Pros of this method are.
- Doesn't require too much initial setup.
- Doesn't give additional access to the files. It just lists them. This assumes we've set the Java permissions correctly.
- Has a control mechanism for what directories can be accessed. This assumes we grant the Java permissions on a per-directory basis.
- Can list files from any directory on the DB server that is accessible to the "oracle" OS user.
- Doesn't require initial setup on the file system.
Cons of this method are.
- We don't get any additional details about the files. Just the file name. The Java API allows us to gather more information, but that would be extra code.
- We are using Java in the database. This is not available from old XE releases. There is nothing wrong with using Java in the database, but some people prefer to avoid it.
For more information see:
Hope this helps. Regards Tim...