MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Read external directory files

with 25 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 before 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

25 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

  10. I just wanted to say thank you. It really helped me.

    Ravi

    11 Mar 10 at 10:30 am

  11. Is this the same procedure for ORACLE 10G…

    Abhiram

    31 Mar 10 at 11:53 am

  12. No, you’ll need this for an Oracle 10g connection:

    Connection conn = new OracleDriver().defaultConnection();

    That’s actually found on page #518 in Chapter 15 of Oracle Database 11g PL/SQL Programming.

    maclochlainn

    31 Mar 10 at 3:02 pm

  13. Same problem here. I have no error, but no result either.

    SELECT column_value FROM TABLE(list_files(‘C:’))

    no rows selected

    Please help. Thanks.

    Hans

    28 Apr 10 at 3:35 am

  14. Did you grant permissions to the root directory, like this:

    BEGIN
  DBMS_JAVA.GRANT_PERMISSION('PLSQL'
                             ,'SYS:java.io.FilePermission'
                             ,'C:'
                             ,'READ');
  END;
/

    maclochlainn

    29 Apr 10 at 11:33 pm

  15. Yup all executed successfully but there is no result though the C:\ contains a file.

    Btw, is the C:\ directory here located in local pc as a separate machine from the server where oracle is running?

    Hans

    30 Apr 10 at 3:29 am

  16. The directory must be on the same physical or virtual machine as the server. If you can’t access it and it is on the same server, try opening it in Windows Explorer to check readability. That’s a restricted directory unless you override the default setting.

    maclochlainn

    30 Apr 10 at 11:10 am

  17. hello
    very useful code!

    Additional question/request.

    I need to be able to return a set of rows with a set of columns, i.e.

    select col1,col2,col3 — or select * even better
    from table ( myJavaMethod(‘/dir/filename.csv’) );

    Where myJavaMethod.java will read a CSV file and do column splitting and other processing so that each input record is split into a Java String[] and/or Oracle Array of column values.

    Is this possible in Oracle 11g R2?

    MS-SQL 2005+ allows this to be done from C#, I need to port the code to Oracle 11g R2 and Java…..
    THANKS!

    yuri

    5 Dec 12 at 8:08 pm

  18. The code works at 10g also, pretty amazing!

    Mingwei Wu

    31 Jan 14 at 10:21 am

  19. […] more on writing Java inside the Oracle […]

    Oracle DBMS_JAVA requires uppercase user

    21 Mar 14 at 12:51 am

  20. found a minor typo in Step 4. missing the letter e
    “to read the external directory befor you can…”

    Cory Allred

    3 Mar 15 at 6:25 pm

  21. Cory, Good catch, the typo is fixed.

    maclochlainn

    3 Mar 15 at 8:17 pm

  22. Thanks!
    Found this to be very useful!

    One question.
    Would it be easy enough to extend the Java to get the file timestamps, sizes, etc. similar to when performing an ls -l?

    So the simple file_list type would become a little more complex – something like:

    CREATE TYPE file_details AS OBJECT
(
  fpermissions   VARCHAR2(10),
  flink          VARCHAR2(3),
  fowner         VARCHAR2(8),
  fgroup         VARCHAR2(7),
  fsize          VARCHAR2(100),
  fdate          VARCHAR2(100),
  fname          VARCHAR2(255)
);
 
CREATE TYPE file_list AS TABLE OF file_details;

    Tony

    10 Mar 15 at 5:15 am

  23. Yes, you only need to return an Oracle Java STRUCT data type.

    maclochlainn

    15 Mar 15 at 6:10 pm

  24. You should give credit to the site you took this from….

    bc

    17 Dec 18 at 12:03 pm

  25. Hi, I’m the original author of that article, which I wrote from scratch eleven years ago. Who did you think wrote it?

    maclochlainn

    28 Feb 19 at 10:13 pm

Leave a Reply