MacLochlainns Weblog

Michael McLaughlin’s Technical Blog

Site Admin

Read external directory files

with 9 comments

There doesn’t appear to be a neat little function to read an external directory. At least, there’s not one in the UTL_FILE or DBMS_LOB packages where you’d think it should be found. Unfortunately, that leaves us with two alternatives. One is to write an external library in C, C++, or C#. Another is to write an internal Java library that reads the file system. You accomplish this by granting permissions to a target directory or directories.

1. The first step is to create a scalar array of VARCHAR2 variables, like

CREATE OR REPLACE TYPE file_list AS TABLE OF VARCHAR2(255);
/

2. The first step is to write the Java library file. You can write it three ways. One accepts default error handling and the others override the default exception handling. If you’re new to Java, you should take the basic library with default handling. If you’ve more experience, you may want to override the helpful message with something that causes the developer to check with the DBA or simply suppress the message to enhance security.

You should note that the database connection is an Oracle Database 11g internal database connection. The connection only does one thing. It allows you to map the ArrayDescriptor to a schema-level SQL collection type. The element types of these collections should be scalar variables, like DATE, NUMBER, or VARCHAR2 datatypes.

The basic library with default exception handling is:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ListVirtualDirectory" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define the class.
  public class ListVirtualDirectory {
    // Define the method.
    public static ARRAY getList(String path) throws SQLException, AccessControlException {
 
    // Define a connection (this is for Oracle 11g).
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
    // Declare a class with the file list.
    File directory = new File(path);
 
    // Declare a mapping to the schema-level SQL collection type.
    ArrayDescriptor arrayDescriptor = new ArrayDescriptor("FILE_LIST",conn);
 
    // Translate the Java String[] to the Oracle SQL collection type.
    ARRAY listed = new ARRAY(arrayDescriptor,conn,((Object[])directory.list()));
  return listed; }}
/

The more advanced method overrides exception handling by suppressing information about the java.properties settings. You can do it by catching the natively thrown exception and rethrow it or ignore it. The example rethrows it.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ListVirtualDirectory" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define the class.
  public class ListVirtualDirectory {
 
    // Define the method.
    public static ARRAY getList(String path) throws SQLException {
 
    // Declare variable as a null, required because of try-catch block.
    ARRAY listed = null;
 
    // Define a connection (this is for Oracle 11g).
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
    // Use a try-catch block to trap a Java permission error on the directory.
    try {
      // Declare a class with the file list.
      File directory = new File(path);
 
      // Declare a mapping to the schema-level SQL collection type.
      ArrayDescriptor arrayDescriptor = new ArrayDescriptor("FILE_LIST",conn);
 
      // Translate the Java String[] to the Oracle SQL collection type.
      listed = new ARRAY(arrayDescriptor,conn,((Object[]) directory.list())); }
    catch (AccessControlException e) {
      throw new AccessControlException("Directory permissions restricted."); }
  return listed; }}
/

You can ignore it by changing the catch block to an empty block with open and closing curly braces, {}. Simply replace the catch block with the following to ignore the missing file, which then acts as like a cursor returing no rows.

    catch (AccessControlException e) {}

3. Next, write a wrapper function, like:

CREATE OR REPLACE FUNCTION list_files(path VARCHAR2) RETURN FILE_LIST IS
LANGUAGE JAVA
NAME 'ListVirtualDirectory.getList(java.lang.String) return oracle.sql.ARRAY';
/

4. You MUST grant the internal JVM authority to read the external directory befor you can return the directory contents. Any attempt to read a directory without the proper permissions raises an ORA-29532 exception. The following is an anonymous block to grant permissions to a directory. You must run it from the SYSDBA role.

BEGIN
  DBMS_JAVA.GRANT_PERMISSION('PLSQL'
                             ,'SYS:java.io.FilePermission'
                             ,'C:\JavaDev\images'
                             ,'read');
  END;
/

5. You can now read the contents of an external file from another PL/SQL block or from a SQL statement. Here’s an example of the SQL statement call:

SELECT column_value FROM TABLE(list_files('C:\JavaDev\images'));

Hope this helps a few folks. Any suggestions on improvements are always welcome.

Written by maclochlainn

June 5th, 2008 at 4:38 am

Posted in Uncategorized

9 Responses to 'Read external directory files'

Subscribe to comments with RSS or TrackBack to 'Read external directory files'.

  1. Hi,

    This literally saved my a$$ from a lot of pain. I cannot believe that PL/SQL has a UTIL_FILE package for file manipulation but no direct way to list files in a directory!

    Thanks!!!

    Iman

    Iman Mayes

    10 Dec 08 at 1:56 pm

  2. Hi,

    Thanks for the code. It works well, but how can this code handle a directory that is empty? We’d like to simply ignore it and not have it throw the message: “a java null point exception has ocurred….”

    Thanks.

    Eliseo Hernandez

    23 Dec 08 at 6:07 am

  3. Eliseo,

    I’d thought the one-liner about changing the catch block answered the question, but thanks for letting me know that it didn’t do the trick. I modified the blog page with a bit more information.

    You simply change the catch block as follows, and it will ignore the empty directory and act like a no rows returned cursor.

        catch (AccessControlException e) {}

    maclochlainn

    23 Dec 08 at 10:23 pm

  4. thanks a lot.

    u really saved my bacon …

    Orian

    4 May 09 at 7:39 am

  5. Awesome… I was pulling my hair out trying to figure out a way to do this.

    bryan

    24 Jul 09 at 8:47 am

  6. Have anyone use this method with a mapped shared drive name?

    In all our examples we used the local drive C:\… and it was working fine until I tried to access using a map drive H:. It is pointing to a network share xx on \\Server01\xx.

    It throws the exception “Directory permissions restricted”. But I have granted the java permission on H:\…

    KAYU

    19 Oct 09 at 5:27 pm

  7. The restriction is probably linked to the domain rights of the user. Is the owner of the remote directly the same Administrative user that owns the installation?

    maclochlainn

    19 Oct 09 at 9:38 pm

  8. Hi This in align with the question KAYU asked.

    I was trying to access another system drive in my home network with path like //somefolder/anotherfolder

    I have granted permissions as well. When I tried

    SELECT column_value list_of_files FROM TABLE(list_files(‘\\VAIO4TAJI\share’))

    I get no error message, but no results either.

    Hope to get some help

    Cheers
    Ligon

    LIGON

    16 Jan 10 at 4:51 pm

  9. I’ll try to test this later in the week.

    maclochlainn

    16 Jan 10 at 6:35 pm

Leave a Reply