Query up a list of files from OS directory

Ever wish you could get a list of file from your server simply by executing a SELECT statement? Here is a list of PL/SQL and Java programs that allow you to do just that in your Oracle database. For this example I used an Oracle 11.2.0.1.0 Standard Edition database on Red Hat Linux 6.

To get access to a directory on the server on which the database is installed, you need to utilize a Java stored procedure. Not being much of a Java programmer, I took an example from AskTom for that, and made a few modifications. This procedure uses the File class to retrieve directory information, and then writes the file details to a database table. I then used a pipelined function to call the Java code. The piplined function makes it possible to then use the function call in a select statement:

SELECT * FROM TABLE(dir_list_fn('/home/oracle/'));

In Tom’s example, the Java procedure writes the file list to a global temporary table. I found that I couldn’t do that with this method. Since the Java procedure executes DML and piplelined functions do not allow for DML inside them, I had to create a work-around: I had my pipelined function call another function that runs as an autonomous transaction. That function then calls the Java that does the DML. Since now the DML was one session removed from the pipelined function, I had to use a regular table to hold the file list.

The columns returned in the query are:

• FILENAME- name of the OS file/directory
• MOD_DATE – file modification date
• TYPE – File type: f = file or pipe, d = directory

Take a look at the code and try it.

[sourcecode language=”sql”]
— Create objects to support SELECT statemtents against server directories.
— Make sure that the user as the JAVAUSERPRIV
— Christoph Ruepprich http://ruepprich.wordpress.com
— cruepprich@gmail.com

create table DIRECTORY_LIST
(
filename VARCHAR2(255),
length NUMBER,
mod_date DATE,
type VARCHAR2(1)
);

CREATE OR REPLACE TYPE t_dir_list_rec AS OBJECT
(
filename VARCHAR2(255),
length NUMBER,
mod_date DATE,
TYPE VARCHAR2(1)
);
/

CREATE OR REPLACE TYPE t_dir_list_tab AS TABLE OF t_dir_list_rec;
/

create or replace and compile java source named "DirList" as
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DirList
{
public static void getList(String directory)
throws SQLException
{
//Thanks to asktom.oracle.com for initial code sample

File path = new File( directory );
String[] list = path.list();
String element;

File fileObject;
long length;
String dateStr, type;
Date now = new Date();
SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy H:m:s");

for(int i = 0; i < list.length; i++)
{
element = list[i];
fileObject = new File(directory + File.separatorChar + list[i]);
length = fileObject.length();
if (fileObject.isDirectory()) {
type = "d";
} else if (fileObject.isFile()) {
type = "f";
} else {
type = "?";
}

java.util.Date d = new java.util.Date(fileObject.lastModified());
dateStr = format.format(d);

#sql { INSERT INTO directory_list (filename,length, mod_date, type)
VALUES (:element, :length, to_date(:dateStr,’dd-mm-yyyy hh24:mi:ss’), :type) };
}
}

}
/

create or replace procedure get_dir_list( p_directory in varchar2 )
as language java
name ‘DirList.getList( java.lang.String )’;
/

CREATE OR REPLACE PROCEDURE call_get_dir_list(p_directory IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE ‘truncate table directory_list’;
get_dir_list(p_directory);
COMMIT;
END;
/

CREATE OR REPLACE FUNCTION dir_list_fn(p_directory IN VARCHAR2)
RETURN t_dir_list_tab
PIPELINED IS
TYPE l_cur_type IS REF CURSOR;
l_cur l_cur_type;
l_rec directory_list%ROWTYPE;
l_sql VARCHAR2(1000);
x NUMBER;
BEGIN
l_sql := ‘SELECT * FROM directory_list’;
call_get_dir_list(p_directory);

OPEN l_cur FOR l_sql;
LOOP
FETCH l_cur
INTO l_rec;
EXIT WHEN l_cur%NOTFOUND;
PIPE ROW(t_dir_list_rec(filename => l_rec.filename,
length => l_rec.length,
mod_date => l_rec.mod_date,
TYPE => l_rec.type));
END LOOP;

RETURN;

END dir_list_fn;
/

col filename for a30
col type for a5
set pagesize 30
set linesize 100
set termout on feedback on

SELECT * FROM TABLE(dir_list_fn(‘/’)) ORDER BY filename;

[/sourcecode]