Oracle PL/SQL Programming: Guide to Oracle8i Features

The main focus in this section is an expansion of the JDelete class into the JFile class, providing significant new file- related features in PL/SQL. Following that, I'll explore how to write Java classes and PL/SQL programs around them to manipulate Oracle objects.

9.9.1 Extending File I/O Capabilities

The UTL_FILE package of Oracle is notable more for what is missing than for what it contains. With UTL_FILE, you can read and write the contents of files sequentially. That's it. You can't delete files, change privileges, copy a file, obtain the contents of a directory, set a path , etc., etc. Java to the rescue! Java offers lots of different classes to manipulate files. You've already met the File class, and seen how easy it is to add the "delete a file" capability to PL/SQL.

I am now going to take my lessons learned from JDelete and the rest of this chapter and create a new class called JFile that will allow PL/SQL developers to answer the questions and take the actions listed here:

• Can I read from a file? Write to a file? Does a file exist? Is the named item a file or a directory?

• What is the number of bytes in a file? What is the parent directory of a file?

• What are the names of all the files in a directory that match a specified filter?

• How can I make a directory? Rename a file? Change the extension on a file?

I'm not going to explain all the methods in the JFile class and its corresponding package. There is a lot of repetition; most of the Java methods look just like the delete( ) function I built at the beginning of the chapter. I will, instead, focus on the unique issues addressed in different areas of the class and package. You can find the full definition of the code in the following files on the companion disk:

JFile.java

A Java class that draws together various pieces of information about operating system files and offers it through an API accessible from PL/SQL.

xfile.pkg

The PL/SQL package that wraps the JFile class. Stands for "eXtra stuff for FILEs."

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 optimaland 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 returnand 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.

9.9.2 Passing Objects to Java

Suppose that a union-busting firm is meeting with some of the largest corporations in the United States to develop a strategy to diminish the power of unions in this country. They need to keep track of different sources of labor and how much they plan to pay them, so the computer geek present, name of Steven, creates the following object type in Oracle:

/* Filename on companion disk: collsql2.sql */ CREATE TYPE labor_source_t AS OBJECT (labor_type VARCHAR2(30), hourly_rate NUMBER); /

Steven is an object-oriented devotee and is committed to taking 100% advantage of both the limited object features of Oracle and the full object-oriented capabilities of Java. He needs, as a result, to be able to pass an object defined inside Oracle straight through to Oracle. How would he go about this?

One approach he can take is to utilize a special class provided by Oracle called oracle.sql.STRUCT. This class relies on default JDBC mappings for the attribute types. (See Oracle's and Java's documentation for more information about JDBC.) The STRUCT class offers a getAttributes( ) method that returns an array of Java objects, one object for each attribute in the Oracle object type, each object containing the value of the attribute. Given the labor source type just shown, for example, a call to getAttributes( ) would return an array with objects defined at indexes 0 and 1 ( Java arrays always start at 0).

The following class demonstrates how you can build a Java class to pass an Oracle object and use the getAttributes( ) method to retrieve object attribute values. Notice the long list of import commands; whenever you use Oracle classes and JDBC, you will need to include these lines (or variations thereof). The java.math.* classes are imported because Oracle attributes of type NUMBER map to Java's BigDecimal class, which is not available by default.

/* Filename on companion disk: UnionBuster.java */ import java.sql.*; import java.io.*; import oracle.sql.*; import oracle.jdbc.driver.*; import oracle.oracore.*; import oracle.jdbc2.*; import java.math.*; public class UnionBuster { public static void wageStrategy (STRUCT e) throws java.sql.SQLException { // Get the attributes of the labor_source object. Object[] attribs = e.getAttributes(); // Access individual attributes by array index, // starting with 0. String laborType = (String)(attribs[0]); BigDecimal hourly_rate = (BigDecimal)(attribs[1]); // We'll simply display the two attribute values. System.out.println ( "Pay " + laborType + " $" + hourly_rate + " per hour"); } }

This line probably looks quite odd to you:

BigDecimal hourly_rate = (BigDecimal)(attribs[1]);

What you are seeing here is a cast from one class to another. The attribs array is composed of "generic" objects. The object contains nothing more, however, than the attribute value. So if I want to extract the number from this object, I prefix the reference to the array element with the name of the class to which I want the object converted. Oracle, by the way, also supports the cast operation to a more limited degree with the SQL CAST operator, described in Chapter 10.

Once Steven has compiled and loaded this class into the Oracle database, he creates his PL/SQL cover, a procedure in this case, since the wageStrategy( ) method does not return any data:

/* Filename on companion disk: passobj.tst */ CREATE OR REPLACE PROCEDURE bust_em_with ( labor_source_in IN labor_source) AS LANGUAGE JAVA NAME 'UnionBuster.wageStrategy (oracle.sql.STRUCT)'; /

Notice that Steven must include the fully qualified datatype in his PL/SQL cover program, even though the Java class parameter is specified only as STRUCT.

And now companies all over the world can use this procedure to depress the hourly wages of workers generally , and destroy unions in particular, as shown here:

/* Filename on companion disk: passobj.tst */ BEGIN bust_em_with ( labor_source ('Workfare', 0)); bust_em_with ( labor_source ('Prisoners', '5')); END; /

We see this output in SQL*Plus when this script is run:

makeOne: [B@2862355c 109 SCOTT.LABOR_SOURCE class oracle.sql.STRUCT makeOne returns: class oracle.sql.STRUCT Pay Workfare 0 per hour makeOne: [B@eb7eefb2 109 SCOTT.LABOR_SOURCE class oracle.sql.STRUCT makeOne returns: class oracle.sql.STRUCT Pay Prisoners 5 per hour