Listing Files in a Specified Directory Using Pl/Sql

Oracle PL/SQL monitor directory for new files

If you're on Oracle 11g you can use the new File Watcher feature of Oracle Scheduler.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse005.htm#CIAJIADA

http://www.oracle-base.com/articles/11g/scheduler-enhancements-11gr2.php#file_watcher

problem in finding list of files in directory

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.

Create a directory on the local file system using SQL Developer and PL/SQL

CREATE DIRECTORY and UTL_FILE is only on the server where Oracle database instance is running.

UTL_FILE to load recursive folders and files with wildcard extension

UTL_FILE only works with DIRECTORY objects, which are pre-defined pointers to folders on the database server. You cannot use wildcards in the file names or traverse the directory tree. You cannot use UTL_FILE to load files from a client system, either unless (in some cases but not all) they are on a shared network drive that is visible to the database.

From the documentation: https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS069

Assuming the user has both READ and WRITE access to the directory
object USER_DIR, the user can open a file located in the operating
system directory described by USER_DIR, but not in subdirectories or
parent directories of this directory.

The only way I have seen this handled in the past is

1. Place all files in the same directory on the DB server
2. Have a shell script that runs on the database server OS list and loop through the files in the directory, and in turn run a SQL script with SQL*Plus to call a stored procedure with each file name as an input. The stored procedure would then open the file in the directory and ingest the data. When the stored procedure and in turn the SQL script were complete, control would return to the shell script which would loop on to the next file in the list and repeat.

OR

3. If all of the files are providing data for the same destination table and have the same record structure, place them all in the same directory and create an external table that points to all of the files at once. You must still name each file explicitly in the external table definition - no wildcards. Then run a stored procedure to perform any ingest operation all at once on the entire data set.

---

Related Topics