-- Create objects to support SELECT statements against server directories.
-- Make sure that the user is the JAVAUSERPRIV
-- Akter Hossain naosolver.blogspot.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
LENGTH MOD_DATE TYPE
SELECT * FROM TABLE(dir_list_fn('/')) ORDER BY filename;
