DBA Hub

📋Steps in this guide1/6

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 Java in the database.

oracle miscconfigurationintermediate
by OracleDba
12 views
1

Setup

We connect to a privileged user, and create a new test user. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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;

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;
2

Create the Java Class, Call Specification and Pipelined Table Function

We connect to the new test user. We create the Java class. We create a call specification for the Java class. This could be a standalone function, but I prefer to use a package. We create a pipelined table function to allow convenient access to the 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 package.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
CONN testuser1/testuser1@//localhost:1521/pdb1

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"

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

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;
/
3

Test It

We can use the function in the package to list files and sub-directories in a directory. Notice the files are presented as a comma-separated list. We can split the list into an array. There are a number of ways to do that, but this method uses the package. We can query the files using the pipelined table function. We can limit the output using a clause in the normal way. These queries can be used in PL/SQL.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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>

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>

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>

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>
4

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
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%';
5

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

A Variation

Christian Antognini sent this variation of the routine, which passes back an array. For more information see: - File Handling From PL/SQL - List Files in a Directory From PL/SQL and SQL : Comparison of Methods Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileListHandler" AS
import java.io.File;
import java.lang.Exception;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Array;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
 
public class FileListHandler
{
  public static Array list (String path) throws Exception {
    Path directory = Paths.get(path);
    if (!Files.isDirectory(directory))
      throw new Exception("path argument does not reference a directory (" + path + ")");
 
    File[] files = directory.toFile().listFiles();
    OracleConnection connection = (OracleConnection)(new OracleDriver()).defaultConnection();
    return connection.createOracleArray("T_VARCHAR2_ARR", files);
  }
};
/
 
CREATE OR REPLACE TYPE t_varchar2_arr AS TABLE OF VARCHAR2(500);
/


CREATE OR REPLACE PACKAGE file_list_api AS

FUNCTION list (p_path  IN  VARCHAR2) RETURN t_varchar2_arr
AS LANGUAGE JAVA
NAME 'FileListHandler.list (java.lang.String) return java.sql.Array';
 
END file_list_api;
/


-- Query it like this.
SELECT * FROM table(FILE_LIST_API.list ('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'));

Comments (0)

Please to add comments

No comments yet. Be the first to comment!