Skip to content

Directory File List in PL/SQL

August 10, 2010

UTL_FILE, unfortunately, does not allow you to query the contents of a directory. One option is to create a java routine.

Note: this is not my code – a colleague sent the basics to me and I’ve just modified it somewhat to suit my purpose.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED
"DirectoryLister" AS
import java.io.File;
import java.util.Arrays;
public class DirectoryLister
{
  public static String getFileList(String idir, String sep)
  {
    File aDirectory = new File(idir);
    File[] filesInDir = aDirectory.listFiles();
    String result = "";
    for ( int i=0; i<filesInDir.length; i++ )
    {
        if ( filesInDir[i].isFile()
             && !filesInDir[i].isHidden() )
        {
            result = result + sep + filesInDir[i].getName();
        }
    }
    return result;
  }
};
/
CREATE OR REPLACE FUNCTION dirlist_csv
(p_dir IN VARCHAR2, p_sep IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA NAME
'DirectoryLister.getFileList(java.lang.String, java.lang.String) return String';
/
EXEC DBMS_JAVA.grant_permission('USER', 'java.io.FilePermission', '<>', 'read');
EXEC DBMS_JAVA.grant_permission('USER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC DBMS_JAVA.grant_permission('USER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

(replace “USER” in the above commands with the user name)

FUNCTION get_path (dir IN VARCHAR2) RETURN VARCHAR2 IS
  o_path ALL_DIRECTORIES.directory_path%TYPE;
BEGIN
  SELECT directory_path INTO o_path
  FROM ALL_DIRECTORIES d
  WHERE  d.directory_name = dir;
  RETURN o_path;
END get_path;

Sample script:

DECLARE
  csv VARCHAR2(32767);
  filename VARCHAR2(1000);
BEGIN
  csv := dirlist_csv(get_path('MYDIR'), ',');
  LOOP
    EXIT WHEN csv IS NULL;
    filename := SUBSTR(csv, INSTR(csv, ',', -1) + 1);
    dbms_output.put_line(filename);
    csv := SUBSTR(csv, 1, INSTR(csv, ',', -1) - 1);
  END LOOP;
END;
/

Note: If the number of files is expected to be large, it would be better to get the java program to insert the file names into a global temporary table, then query that, instead of returning the list as a CSV string.

About these ads

From → PL/SQL

4 Comments
  1. I’ve got a similar Java Stored Procedure based on one from Tim Hall of Oracle-Base. Mine returns the list as an array of String, then the PL/SQL shell package can return the array via a pipelined table function. That means that I can SELECT * FROM TABLE(my_pipeline_function).

    • Nice :) how do you return an array from the java routine, and how do you declare the PL/SQL wrapper function?

  2. You might like this – no java :-)

    http://www.oracle-developer.net/display.php?id=513

    • Thanks Connor, good to mention it for future visitors, if there are any :)

      I was aware of that one. This post was about a 10g database though (I probably should have mentioned that).

      If/when they upgrade to 11g I’d probably look at removing this entire thing anyway, because the only reason we want a directory listing is to drive a home-grown “File Watcher” – and 11g has a built-in File Watcher feature in dbms_scheduler.

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 205 other followers

%d bloggers like this: