problem in finding list of files in directory

Question

I want to retrieve list of all file in a specific folder that included oracle form and menu and report and some txt file...

Do you have any idea how I can retrieve these data in ORACLE form, and insert them into my data block, automatically?

I use oracle form 6.0.

Answer 1

I did something along these lines:

Create an Oracle directory for the directory you want to list:

create or replace directory YOURDIR
  as '\path\to\your\directory';

Build a temporary table:

create global temporary table DIR_LIST
(
  FILENAME VARCHAR2(255),
)
on commit preserve rows;
grant select, insert, update, delete on DIR_LIST to PUBLIC;

You'll need a java stored procedure:

create or replace and compile java source named dirlist as
import java.io.*;
  import java.sql.*;
  import java.text.*;

  public class DirList
  {
  public static void getList(String directory)
                     throws SQLException
  {
      File dir = new File( directory );
      File[] files = dir.listFiles();
      File theFile;

      for(int i = 0; i < files.length; i++)
      {
          theFile = files[i];
          #sql { INSERT INTO DIR_LIST (FILENAME)
                 VALUES (:theName };
      }
  }

  }

And a PL/SQL callable procedure to invoke the java:

CREATE OR REPLACE PROCEDURE get_dir_list(pi_directory IN VARCHAR2)
AS LANGUAGE JAVA
name 'DirList.getList(java.lang.String)';

Finally, calling the procedure get_dir_list inside your form will populate the table with the files in your directory, which you can then read into your form block.

The java code came straight out of a Tom Kyte book (don't recall which one).

EDIT:

Actually, all the code is pretty much lifted from this AskTom thread.