9.9.1.1 Polishing up the delete method

Before we move on to new and exciting stuff, we should make sure that what we've done so far is optimal -- and the way I defined the JDelete.delete( ) method and the delete_file function is far from ideal. Here's the code I showed you earlier:

public static int delete (String fileName) {
   File myFile = new File (fileName);
   boolean retval = myFile.delete();
   if (retval) return 1; else return 0;
   }

CREATE OR REPLACE FUNCTION fDelete (
   file IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
   NAME 'JDelete.delete (java.lang.String) 
            return int';
/

You might be asking yourself, so what's the problem? The problem is that I have been forced to use clumsy, numeric representations for TRUE/FALSE values. I must as a result write code like this:

IF fdelete ('c:\temp\temp.sql') = 1 THEN ...

and that is very ugly, hardcoded software. Not only is it ugly, but the person writing the PL/SQL code must know about the values for TRUE and FALSE embedded within a Java class.

I would much rather define a delete_file function with this header:

 FUNCTION fDelete (
   file IN VARCHAR2) RETURN BOOLEAN;

So let's see what it would take to be able to present that clean, easy-to-use API for users of the xfile package.

First, I will rename the JDelete class to JFile to reflect its growing scope. Then, I will add methods that encapsulate the TRUE/FALSE values its other methods will return -- and call those inside the delete( ) method. Here is the result:

/* Filename on companion disk: JFile.java */
import java.io.File;

public class JFile {
 
   public static int tVal () { return 1; };
   public static int fVal () { return 0; };   

   public static int delete (String fileName) {
      File myFile = new File (fileName);
      boolean retval = myFile.delete();
      if (retval) return tVal(); 
         else return fVal();
      }
}   

That takes care of the Java side of things; it's time to shift attention to my PL/SQL package. Here's the first pass at the specification of xfile:

/* Filename on companion disk: xfile.pkg */
CREATE OR REPLACE PACKAGE xfile
IS
   FUNCTION delete (file IN VARCHAR2) 
      RETURN BOOLEAN;
END xfile;

So now we have the Boolean function specified. But how do we implement it? I have two design objectives:

1. Hide the fact that I am relying on numeric values to pass back TRUE or FALSE.

2. Avoid hardcoding the 1 and 0 values in the package.

To achieve these objectives, I will define two global variables in my package to hold the numeric values:

/* Filename on companion disk: xfile.pkg */
CREATE OR REPLACE PACKAGE BODY xfile
IS
   g_true INTEGER;
   g_false INTEGER;

And way down at the end of the package body, I will create an initialization section that calls these programs to initialize my globals. By taking this step in the initialization section, I avoid unnecessary calls (and overhead) to Java methods:

BEGIN
   g_true := tval;
   g_false := fval;    	
END xfile;

Back up in the declaration section of the package body, I will define two private functions, whose only purpose is to give me access in my PL/SQL code to the JFile methods that have encapsulated the 1 and 0:

FUNCTION tval RETURN NUMBER
AS LANGUAGE JAVA
   NAME 'JFile.tVal () return int';

FUNCTION fval RETURN NUMBER
AS LANGUAGE JAVA
   NAME 'JFile.fVal () return int';

I have now succeeded in soft-coding the TRUE/FALSE values in the JFile package. To enable the use of a true Boolean function in the package specification, I create a private "internal delete" function that is a wrapper for the JFile.delete( ) method. It returns a number:

FUNCTION Idelete (file IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
   NAME 'JFile.delete (java.lang.String) return int';

Finally, my public delete function can now call Idelete and convert the integer value to a Boolean by checking against the global variable:

FUNCTION delete (file IN VARCHAR2) RETURN BOOLEAN
AS
BEGIN
   RETURN Idelete (file) = g_true;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN FALSE;
END;

And that is how you convert a Java Boolean to a PL/SQL Boolean. You will see this same method employed again and again in the xfile package body.

9.9.1.2 Obtaining directory contents

One of my favorite features of JFile is its ability to return a list of files found in a directory. It accomplishes this feat by calling the File.list( ) method; if the string you used to construct a new File object is the name of a directory, it returns a String array of filenames found in that directory. Let's see how I can make this information available in PL/SQL.

I create a String method called dirContents, as follows:

/* Filename on companion disk: JFile.java */
public static String dirContents (String dir) {
   File myDir = new File (dir);
   String[] filesList = myDir.list();
   String contents = new String();
   for (int i = 0; i < filesList.length; i++) 
      contents = contents + listDelimiter + filesList[i];
   return contents;
   }

This method instantiates a File object called myDir and then assigns the myDir.list( ) to a String array called filesList. I then use a Java "for" loop to concatenate each of the files into a single String, separated by the listDelimiter, and return that String.

Over on the PL/SQL side of the world, I will create a wrapper that calls this method:

FUNCTION dirContents (dir IN VARCHAR2) 
   RETURN VARCHAR2
   AS LANGUAGE JAVA
      NAME 'JFile.dirContents (java.lang.String) 
               return java.lang.String';

But what am I do with this string? Let's build some additional code elements on top of my wrapper functions to make the information more developer friendly. First, I'd like to let users of xfile manipulate files either as string lists or as nested tables (much more structured data; easier to scan and manipulate). So I will define a nested table type as follows:

CREATE TYPE file_list_t IS TABLE OF VARCHAR2(2000);
/

Then I define a procedure to return the files in a directory in a nested table of this type. Note the call to the dirContents wrapper function and also the reference to g_listdelim, which contains the delimiter passed back from JFile (just like the numeric values for TRUE and FALSE):

PROCEDURE getDirContents (
   dir IN VARCHAR2, 
   files IN OUT file_list_t)
IS
   file_list VARCHAR2(32767);
   next_delim PLS_INTEGER;
   start_pos PLS_INTEGER := 1;
BEGIN
   files.DELETE;

   file_list := dirContents (dir);

   LOOP
      next_delim := 
         INSTR (file_list, g_listdelim, start_pos);
      EXIT WHEN next_delim = 0;
      files.EXTEND; 
      files(files.LAST) := 
         SUBSTR (file_list, 
            start_pos, 
            next_delim - start_pos);
      start_pos := next_delim + 1;
   END LOOP;
END;

From there, it's all just fun and games with PL/SQL. You will find in the xfile package the following programs built on top of getDirContents:

getDirContents, the filter version

Allows the user to pass a filter, such as "*.tmp" or "%.tmp", and retrieve only files that match the filter. The character "_" will be treated as a single-character wildcard, following the SQL standard.

showDirContents

Displays all the files found in the specified directory that match your filter.

chgext

Changes the extension of the specified files.

In the xfile package, you will also find all of the entry points of the UTL_FILE package, such as FOPEN and PUT_LINE. I add those so that you can avoid the use of UTL_FILE for anything but declarations of file handles as UTL_FILE.FILE_TYPE.