Get Directory listing

There are no built‐in methods anywhere in SQL or PL/SQL to read a directory listing. Well, Java can do that for us easily.

rajesh@10GR2> create global temporary table gtt_file_names (
  2  file_name varchar2(45)
  3  ) on commit delete rows;

Table created.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> create or replace and compile
  2  java source named "dir_file_list"
  3  as
  4     import java.io.*;
  5     import java.sql.*;
  6
  7     public class dir_file_list
  8     {
  9             public static void getfiles(String dir) throws SQLException
 10             {
 11                     File path1 = new File (dir);
 12                     String list[] = path1.list();
 13                     String element;
 14                             for ( int i = 0; i< list.length; i++)
 15                             {
 16                                     element = list[i];
 17                                     #sql{insert into gtt_file_names values (:element)};
 18                             }
 19             }
 20     };
 21  /

Java created.

Elapsed: 00:00:01.12
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create or replace procedure get_dir_lists(p_dir in varchar2)
  2  as language java
  3  name 'dir_file_list.getfiles(java.lang.String)';
  4  /

Procedure created.

Elapsed: 00:00:00.07
rajesh@10GR2>

One last detail here before we run this procedure. We need to give it permissions to do what it wants to do; read a directory. without that we will be ending up with error.

rajesh@10GR2> exec get_dir_lists('E:\ORACLE\PRODUCT\10.1.0\ADMIN\10GR2\UDUMP');
BEGIN get_dir_lists('E:\ORACLE\PRODUCT\10.1.0\ADMIN\10GR2\UDUMP'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
(java.io.FilePermission E:\ORACLE\PRODUCT\10.1.0\ADMIN\10GR2\UDUMP read) has not been granted to RAJESH. The PL/SQL to grant
this is dbms_java.grant_permission( 'RAJESH', 'SYS:java.io.FilePermission', 'E:\ORACLE\PRODUCT\10.1.0\ADMIN\10GR2\UDUMP', 'read'
)
ORA-06512: at "RAJESH.GET_DIR_LISTS", line 1
ORA-06512: at line 1

Elapsed: 00:00:00.50

So, weʹll authorize ourselves to do this

rajesh@10GR2>
rajesh@10GR2> begin
  2      dbms_java.grant_permission(
  3      grantee=>user,
  4      permission_type=>'java.io.FilePermission',
  5      permission_name=>'E:\ORACLE\PRODUCT\10.1.0\ADMIN\10GR2\UDUMP',
  6      permission_action=>'read');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> exec get_dir_lists('E:\ORACLE\PRODUCT\10.1.0\ADMIN\10GR2\UDUMP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select * from gtt_file_names where rownum <= 5;

FILE_NAME
------------------------------
10GR2_ora_1016.trc
10GR2_ora_1032.trc
10GR2_ora_1052.trc
10GR2_ora_1072.trc
10GR2_ora_1076.trc

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2>