Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Reading an external directory from SQL or PL/SQL

with 3 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. You can ignore it by changing the catch block to an empty block with open and closing curly braces, {}.

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; }}
/

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.

Advertisements

Written by maclochlainn

June 5, 2008 at 4:38 am

3 Responses

Subscribe to comments with RSS.

  1. […] on the Internet and of the Oracle documentation didn’t unearth an example. The referenced code and instructions show you how to implement the necessary pieces with a PL/SQL wrapper function. […]

  2. This function saved my butt! Thanks!!!
    km

    Kel

    June 18, 2009 at 2:35 pm

  3. Updates and more current content are here.

    maclochlainn

    June 30, 2009 at 8:24 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: