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:
/* File on web: 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 all 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 to 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 (this is more structured data, and 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 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 of 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 and references to the exceptionsdeclared in UTL_FILE.
