-- run from SCOTT
create table DIR_LIST (filename varchar2(255),lastmodified date, filesize number);

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList" AS
import java.io.*;
import java.sql.*;

    public class DirList
    {
    public static void getList(String directory) throws SQLException
    {
       File path = new File(directory);
       File[] FileList = path.listFiles();
       String TheFile;
       Timestamp ModiDate;
	   Long Siz;
       //#sql { DELETE FROM DIR_LIST};

       for(int i = 0; i < FileList.length; i++)
       {
           TheFile = FileList[i].getAbsolutePath();
           ModiDate = new Timestamp( FileList[i].lastModified() );
           Siz = new Long(FileList[i].length() );

           #sql { INSERT INTO DIR_LIST (FILENAME,LASTMODIFIED,FILESIZE)
                  VALUES (:TheFile, :ModiDate, :Siz) };
       }
   }
  }
/


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

Select * from DBA_JAVA_POLICY where type_name like 'java.io.%'
and grantee = 'SCOTT';
 
-- run from DBA account
begin
 for i in ( select value from v$parameter where name like '%dump_dest') loop
 --dbms_java.revoke_permission( 'SCOTT', 'SYS:java.io.FilePermission', i.value, 'read' );
 --dbms_java.revoke_permission( 'SCOTT', 'SYS:java.io.FilePermission', i.value||'/*', 'read' );
 -- the directory
 dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', i.value, 'read' );
 -- directory contents
 dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', i.value||'/*', 'read' );
 end loop;
 commit;
end;
/


--===============================================
-- Example
--===============================================
delete DIR_LIST;

begin dbms_java.set_output(100000); end;
/

begin
 for i in ( select value from v$parameter where name like '%dump_dest') loop
 pop_dir_list( i.value );
 end loop;
end;
/

select * from dir_list;