A previous article of mine shows you how to use the DBMS_LOB package to read external files and load them into CLOB and BLOB columns. That article shows you how to use an explicit file name to access an internal file. Oracle delivers a more effective solution when you know how to use the DBMS_JAVA package to read the external file system. The power of reading external files occurs when you couple the DBMS_JAVA and DBMS_LOB packages. Especially, when you’re doing bulk uploads of large text or image files.

The DBMS_JAVA package gives you the ability to develop robust tools to leverage external files in your application programs. You need to use Oracle’s virtual directories with the DBMS_JAVA package. This article discusses the following:

  • How to create and grant privileges to virtual directories
  • How to find the physical directories of virtual directories
  • How to read the file names from external directories

You can skip the first section when you already understand how Oracle’s virtual directories work. That means you can start by learning how to find the physical directories that map to Oracle’s virtual directories.

How to create and grant privileges to virtual directories

Virtual directories let you map a name in the database to an external directory. They’re store that name in the data catalog while hiding the physical directories.

You create a virtual directory as the sys or system user in the Oracle Database 11g database with the following syntax:

SQL> CREATE DIRECTORY upload AS '/u01/app/oracle';

You also can create a virtual directory in Oracle Database 12c’s multitenant architecture. There is a slight difference between how you create a virtual directory in a pluggable database (PDB). You use the ADMIN user account rather than the sys or system user accounts.

After you create an UPLOAD virtual directory, you need to grant at least read privileges to the virtual directory. You also have the option of granting write and execute privileges.

For the purposes of this example, you only need to grant read privileges. The command to grant read privilege to a student user is:

SQL> GRANT READ ON DIRECTORY upload TO student;

After you create the virtual directory, you can query the data dictionary with the following query:

SQL> SELECT directory_path

  2  FROM   sys.dba_directories

  3  WHERE  directory_name = UPPER('upload');

It should display:

DIRECTORY_PATH

--------------------

'/u01/app/oracle'

Now, you know how to create and grant read privileges to an Oracle virtual directory.  The next section shows you how to relax security (not always a good thing), and access translate the virtual directory name to a physical directory name.

How to find the physical directories of virtual directories

You translate the virtual directory name to an external directory path by using a stored function. You should deploy the function in the system schema. Unfortunately, you can’t write a stored function in the system schema that accesses a sys view without granting the SELECT privilege first. That’s because system only has access through a role.

Connect as the sys user and grant the SELECT privilege with the following syntax:

SQL> GRANT SELECT ON dba_directories TO system;

 The SELECT grant lets you put a query or cursor inside a function that reads the sys view. The following get_directory function lets you submit the virtual directory name and return the physical directory name:

SQL> CREATE OR REPLACE FUNCTION get_directory

  2  ( virtual_directory IN VARCHAR2 )

  3  RETURN VARCHAR2 IS

  4

  5    /* Define return variable. */

  6    directory_path VARCHAR2(256) := '';

  7

  8    /* Define dynamic cursor. */

  9    CURSOR get_directory

 10    (virtual_directory VARCHAR2) IS

 11      SELECT   directory_path

 12      FROM     sys.dba_directories

 13      WHERE    directory_name = UPPER(virtual_directory);

 14

 15    /* Define an exception for a name violation. */

 16    directory_name EXCEPTION;

 17    PRAGMA EXCEPTION_INIT(directory_name,-22284);

 18  BEGIN

 19    OPEN  get_directory (virtual_directory);

 20    FETCH get_directory

 21    INTO  directory_path;

 22    CLOSE get_directory;

 23

 24    /* RETURN file name. */

 25    RETURN directory_path;

 26  EXCEPTION

 27    WHEN directory_name THEN

 28      RETURN null;

 29  END get_directory;

 30  /

Lines 9 through 13 define a get_directory cursor, which is a parameterized cursor that queries the dba_directories view. Lines 19 through 22 open the get_directory cursor and fetch the result into a local variable. Line 25 returns the physical directory path for a virtual directory.

You need to grant execute privileges on the get_directory function as the system user to the student user. The following syntax grants the execute privilege to the student user when you run it as the system user:

SQL> GRANT EXECUTE ON get_directory TO student;

Inside the student schema, you would need to refer to the get_directory function by using the system.get_directory syntax. A synonym simplifies access to the get_directory function. You can create a synonym for the get_directory function with the following syntax:

SQL> CREATE SYNONYM get_directory FOR system.get_directory;

After creating the synonym, you can query the get_directory function like this:

SQL> COLUMN path FORMAT A40 HEADING "Path"

SQL> SELECT get_directory_path('upload') AS path

  2  FROM   dual;

It should return

Path

-------------------------

/u01/app/oracle/upload

This section has shown you how to find the physical directory of a virtual directory. The next section shows you how to work with Java to read the contents of a physical directory.

How to read the file names from external directories

While you granted read privileges to the virtual directory, you need to also grant permission to the internal Java Virtual Machine (JVM). You need to run the following anonymous block as the sys user:

SQL> BEGIN

  2    DBMS_JAVA.GRANT_PERMISSION(

  3        grantee => 'STUDENT'

  4      , permission_type =>'SYS:java.io.FilePermission'

  5      , permission_name => '/u01/app/oracle/upload'

  6      , permission_action => 'read');

  7  END;

  8  /

After granting the permissions, you need to create a SQL data type to hold a list of file names. You can create that type with the following syntax:

SQL> CREATE OR REPLACE

  2    TYPE file_list AS TABLE OF VARCHAR2(255);

  3  /

The next step requires you to write a Java library and deploy it inside an Oracle database. The Java library will use two key Java classes. One is the ListVirtualDirectory class and the other is the ArrayDescriptor class.

The ListVirtualDirectory library uses the ArrayDescriptor class to map a native Java collection to an Oracle ARRAY Java library. You can deploy a Java library inside an Oracle database by using the loadjava utility or through SQL*Plus. The simplest way for those new to Java uses SQL*Plus to deploy the Java class.

The following creates the ListVirtualDirectory library file:

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE

  2    NAMED "ListVirtualDirectory" AS

  3

  4    // Import required classes.

  5    import java.io.*;

  6    import java.security.AccessControlException;

  7    import java.sql.*;

  8    import java.util.Arrays;

  9    import oracle.sql.driver.*;

 10    import oracle.sql.ArrayDescriptor;

 11    import oracle.sql.ARRAY;

 12

 13    // Define the class.

 14    public class ListVirtualDirectory {

 15

 16      // Define the method.

 17      public static ARRAY getList(String path) throws SQLException {

 18

 19      // Declare variable as a null.

 20      ARRAY listed = null;

 21

 22      // Define a connection.

 23      Connection conn =

 24        DriverManager.getConnection("jdbc:default:connection:");

 25

 26      // Use a try-catch block to trap a Java permission

 27      // error on the directory.

 28      try {

 29        // Declare a class with the file list.

 30        File directory = new File(path);

 31

 32        // Declare a mapping schema SQL collection type.

 33        ArrayDescriptor arrayDescriptor =

 34          new ArrayDescriptor("FILE_LIST",conn);

 35

 36        // Translate the Java String[] collection type.

 37        listed = new ARRAY(arrayDescriptor

 38                          ,conn

 39                          ,((Object[]) directory.list())); }

 40      catch (AccessControlException e) {

 41        throw new AccessControlException(

 42                    "Directory permissions restricted."); }

 43    return listed; }}

 44  /

Line 17 declares the getList method that returns a collection of the Oracle ARRAY class. The statement on lines 33 through 34 creates an ArrayDescriptor instance with the file_list collection type. Lines 37 through 39 constructs a new ARRAY instance with the ArrayDescriptor instance, a connection to the database, and a generic Java collection of the base Java Object class.

You wrap the Java ListVirtualDirectory class with the following PL/SQL statement:

SQL> CREATE OR REPLACE FUNCTION list_files(path VARCHAR2)

  2  RETURN FILE_LIST IS LANGUAGE JAVA NAME

  3  'ListVirtualDirectory.getList(java.lang.String) return oracle.sql.ARRAY';

  4  /

After creating all the necessary elements, you can query the contents of a directory with this SQL statement and a fully qualified path:

SQL> SELECT column_value

  2  FROM   TABLE(list_files(get_directory('UPLOAD')));

This returns a list of files from the physical directory.  You then have the ability to call the load_clob_from_file or load_blob_from_file procedures (presented in my article on the DBMS_LOB package) inside a loop. The caveat would be that these other procedures are written with the assumption you know whether the file is a large text or image file. My recommendation is that you put large text files in one directory and image files in another directory.

This article has shown you how to leverage the DBMS_JAVA file to read external files for bulk upload processing.