Hi All,
I'm doing the following to get a list of all the files in a directory. It's working fine. But when I run the procedure in a scheduler; it runs without errors but the table remain empty without the list of files.
(1) Table to hold the list of files
create global temporary table DIR_LIST
( filename varchar2(255) )
on commit delete rows;
/
(2) Java Class
create or replace and compile java source named "DirList" as
import java.io.*;
import java.sql.*;
public class DirList {
public static void getList(String directory)
throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
String sql = "INSERT INTO NGM_DIR_LIST (file_name) values (?)";
File path = new File(directory);
String[] list = path.list();
String element;
for (int i = 0; i < list.length; i++) {
element = list[i];
String fpath = directory + "/" + list[i];
File f = new File(fpath);
long len;
String ftype;
if (f.isFile()) {
len = f.length();
ftype = "F";
} else {
len = 0;
ftype = "D";
}
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, element);
-- pstmt.setLong(2, len);
-- pstmt.setString(3, ftype);
pstmt.executeUpdate();
pstmt.close();
}
}
}
--------------
(3) Procedure which calls the Java Class
create or replace procedure get_dir_list(p_directoruy in varchar2)
as language java
name 'DirList.getList(java.lang.String)';
/
(3) Run
exec get_dir_list('D:\Files_to_be_Uploaded');
or
begin
exec get_dir_list('D:\Files_to_be_Uploaded');
end;
(4) Test
Select * from DIR_LIST;
It shows correct data.
ISSUE : when I call the procedure get_dir_list from the scheduler; it fails to load list of files into the table dir_list.
-- Scheduler
begin
sbms_scheduler.create_job(
job_name => 'jn_1',
job_type => 'PLSQL_BLOCK',
job_action => q'#exec get_dir_list('D:\Files_to_be_Uploaded');#',
start_date => systimestamp,
enabled => true,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'
);
END;
/
Thank You