Get and Read file names in a directory using SQL - PL/SQL

I got this interesting method to list / access all files in a user specified directory using SQL and PL/SQL. Here is the reference and method

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584

First you need to grant JAVAUSERPRIV to schema user as sys

GRANT JAVAUSERPRIV to app_owner;


Connect as schema user and execute these scripts


SQL> CREATE GLOBAL TEMPORARY TABLE dir_list
  2       ( filename VARCHAR2(255) )
  3      ON COMMIT DELETE ROWS
  4  /

Table created.

SQL> create or replace
  2          and compile java source named "DirList"
  3      as
  4      import java.io.*;
  5      import java.sql.*;
  6
  7      public class DirList
  8      {
  9      public static void getList(String directory)
 10                        throws SQLException
 11     {
 12         File path = new File( directory );
 13         String[] list = path.list();
 14        String element;
 15
 16        for(int i = 0; i < list.length; i++)
 17         {
 18             element = list[i];
 19             #sql { INSERT INTO DIR_LIST (FILENAME)
 20                    VALUES (:element) };
 21         }
 22     }
 23
 24     }
 25     /

Java created.

SQL> create or replace
  2       procedure get_dir_list( p_directory in varchar2 )
  3    as language java
  4      name 'DirList.getList( java.lang.String )';
  5     /

Procedure created.

SQL> exec get_dir_list('/applns/oracle/dbs');

PL/SQL procedure successfully completed.

SQL> select * from dir_list;

FILENAME
--------------------------------------------------------------------------------

initdw.ora
init.ora
orapwCTSD1
lkCTSD1
hc_CTSD1.dat
spfileCTSD1.ora
orapwMVS01
orapwC9W01
lkC9W01
hc_C9W01.dat
spfileC9W01.ora

FILENAME
--------------------------------------------------------------------------------

lkMVS01
hc_MVS01.dat
spfileMVS01.ora
orapwCWY01
lkCWY01
hc_CWY01.dat
spfileCWY01.ora
orapwCVS01
orapwM9W01
lkM9W01
hc_M9W01.dat

FILENAME
--------------------------------------------------------------------------------

spfileM9W01.ora
lkCVS01
hc_CVS01.dat
spfileCVS01.ora
orapwCCX01
lkCCX01
hc_CCX01.dat
spfileCCX01.ora
lkinstMCSEK
hc_MNH01.dat
initCVS01.ora

FILENAME
--------------------------------------------------------------------------------

hc_MEK01.dat
hc_MCSEK.dat
initCTSD1.ora
test
cts.dmp
lkinstMEK01
hc_MVS02.dat
hc_CCM1.dat
hc_MCM1.dat
initCWY01.ora

43 rows selected.

SQL>