9.3 A Simple Demonstration | Oracle PL/SQL Programming: Guide to Oracle8i Features


Team-Fly

	Oracle PL/SQL Programming Guide to Oracle 8 i Features By Steven Feuerstein
	Table of Contents

	Chapter 9. Calling Java from PL/SQL

9.3 A Simple Demonstration

Before diving into the details, let's just walk through all the different steps needed to access Java from within PL/SQL. In the process, I'll introduce the various pieces of technology you need to get the job done.

So here is my challenge: I need to be able to delete a file from within PL/SQL. Prior to Oracle 8.1, I had the following options:

Even in Oracle 7.3, I could send a message to a database pipe, and then have a C listener program grab the message ("Delete file X") and do all the work.
In Oracle 8.0, I could set up a library that pointed to a C DLL or shared library, and then from within PL/SQL, call a program in that library to delete the file.

The pipe technique is handy, but it is a clumsy workaround. The external procedure implementation in Oracle 8.0 is a better solution, but it is far less than straightforward, especially if you don't know the C language.

Java, on the other hand, comes with prebuilt ( foundation ) classes that offer clean, easy-to-use APIs to a wide array of functionality, including file I/O.

Here are the steps I will perform in this demonstration:

Identify the Java functionality I need to access.
Build a class of my own to make the underlying Java feature callable through PL/SQL.
Compile the class and load it into the database.
Build a PL/SQL program to call the class method I created.
Delete files from within PL/SQL.

9.3.1 Finding the Java Functionality

My O'Reilly & Associates editor, Deborah Russell, was kind enough to send me a whole bunch of their Java books, so I grabbed the big, fat Java Fundamental Class Reference , by Mark Grand and Jonathan Knudsen, and looked up "File" in the index (sure, I could use HTML documentation, too, but I like books). The entry for "File class" caught my eye and I hurried to page 161.

There I found information about the class named java.io.File, namely, that it "provides a set of methods to obtain information about files and directories." Well, fortunately, it doesn't just let you obtain information. It also contains methods (procedures and functions) to delete and rename files, make directories, and so on. I had come to the right place!

Here is a portion of the API offered by the File class:

 public class java.io.File {    public boolean delete();    public boolean mkdir (); }

I will, in other words, call a Boolean function in Java to delete a file. It the file is deleted, the function returns TRUE; otherwise , it returns FALSE.

9.3.2 Building a Custom Java Class

Now, you might be asking yourself: why should Steven have to build his own Java class on top of the File class? Why can't I just call that function directly inside my PL/SQL wrapper? There are two reasons:

A Java class method is, in almost every case (except for static methods), executed for a specific object instantiated from the class. From within PL/SQL, I cannot instantiate a Java object and then call the method against that object.
Even though Java and PL/SQL both have Boolean datatypes ( Java even offers a Boolean primitive and a Boolean class), they do not map to each other. I cannot pass a Boolean from Java back directly to a PL/SQL Boolean.

As a direct consequence, I need to build my own class that will:

Instantiate an object from the File class
Execute the delete method against that object
Return a value that PL/SQL interprets properly

Here is the very simple class that I wrote to take advantage of the File.delete method:

 /* Filename on companion disk: JDelete.java */ import java.io.File; public class JDelete {      public static int delete (String fileName) {       File myFile = new File (fileName);       boolean retval = myFile.delete();       if (retval) return 1; else return 0;       } }

Figure 9.2 explains each of the steps in this code, but the main effect is clear: the JDelete.delete method simply instantiates a dummy File object for the specified filename, so that I can call the delete method for that file. By declaring my method to be static, I make that method available without the need to instantiate an object. Static methods are associated with the class and not with objects declared from that class.

This class highlights a number of differences between Java and PL/SQL you should keep in mind:

There are no BEGIN and END statements in Java for blocks, loops , or conditional statements. Instead, you use an open - brace ( { ) to start a block of related code, and a close-brace ( } ) to close the block.
Java is case sensitive; "if " is definitely not the same thing as "IF".
The assignment operator is a plain equals sign (=) rather than the complex symbol used in PL/SQL (:=).
When you call a method that does not have any arguments (such as delete), you still must provide open and close parentheses. Otherwise, the Java compiler will try to interpret the method as a class member or data structure.

Figure 9.2. A simple Java class used to delete a file

Hey, that was easy! Of course, you didn't watch me fumble around with Java for a day, getting over the nuisance of minor syntax errors, the agony of a case-sensitive language (I have a hard time with operating systems and programming languages that are case sensitive), and confusion concerning setting the CLASSPATH. I'll leave all that to the imagination of my readersand your own day of fumbling!

9.3.3 Compiling and Loading into Oracle

Now that my class is written, I need to compile. To do this I open an MS-DOS session in Windows NT, change to the d:\Java directory, and compile the class:

 D:\Java> javac JDelete.java

Now that it's compiled, I realize that it would make an awful lot of sense to test the function before I stick it inside Oracle and try it from PL/SQL. You are always better off building and testing incrementally . Java gives us an easy way to do this: the main method. If you provide a void method (procedure) called main in your classand give it the right parameter listyou can then call the class, and this code will execute.

The main method is one example of how Java treats certain elements in a special way if they have the right signature. Another example is the toString method. If you add a method with this name to your class, then whenever you reference an object of that class where a string is needed, it will automatically call the toString method to display your custom description of the object.

So let's add a simple main method to JDelete (shown in bold in the following code):

 public class JDelete {    public static int delete ...  public static void main (String args[]) {       System.out.println (          delete (args[0])          );    }  }

In other words: call delete for the first value passed to the class and then display the value being returned. Now I will recompile the class and then run it, as shown (this example is taken from a DOS Window):

 D:\Java>javac JDelete.java D:\Java>java JDelete c:\temp\te_employee.pks 1 D:\Java>java JDelete c:\temp\te_employee.pks 0

Notice that the first time I run the main method it displays 1 (TRUE), indicating that the file was deleted. So it will come as no surprise that when I run the same command, main displays 0. It couldn't delete a file that had already been deleted.

That didn't take too much work or know-how, did it?

In another demonstration of the superiority of Java over PL/SQL, please note that whereas you have to type 20 characters in PL/SQL to display output (DBMS_OUTPUT.PUT_LINE), you needn't type any more than 18 characters in Java (System.out.println). Give us a break , you language designers!

Now that my class compiles and I have verified that the delete method works, I will load it into the SCOTT schema of the Oracle database using the loadjava command:

 D:\Java>loadjava -user scott/tiger -oci8 -resolve JDelete.class

I can even verify that the class is loaded by querying the contents of the USER_OBJECTS data dictionary via a utility I'll introduce later in this chapter:

 SQL> exec myjava.showobjects Object Name  Object Type   Status  Timestamp --------------------------------------------------- Hello        JAVA CLASS    VALID   1999-05-19:16:42 JDelete      JAVA CLASS    VALID   1999-06-07:13:20 JFile2       JAVA CLASS    VALID   1999-05-26:17:07 JFile3       JAVA CLASS    VALID   1999-05-27:12:53

That takes care of all the Java-specific steps, which means that it's time to return to the cozy world of PL/SQL.

9.3.4 Building a PL/SQL Wrapper

I will now make it easy for anyone connecting to my instance to delete files from within PL/SQL. To accomplish this goal, I will create a PL/SQL wrapper that looks like a PL/SQL function on the outside, but is really nothing more than a pass-through to the underlying Java code.

 /* Filename on companion disk: fdelete.sf */ CREATE OR REPLACE FUNCTION fDelete (    file IN VARCHAR2)     RETURN NUMBER AS LANGUAGE JAVA    NAME 'JDelete.delete (             java.lang.String)              return int'; /

The implementation of the fdelete function consists of a string describing the Java method invocation. The parameter list must reflect the parameters of the method, but in place of each parameter I specify the fully qualified datatype name. In this case, that means that I cannot simply say "String", but instead must add the full name of the package containing the String class. The RETURN clause simply lists int for integer. The int is a primitive datatype and not a class, so that is the complete specification.

9.3.5 Deleting Files from PL/SQL

So I compile the function and then perform my magical , previously difficult if not impossible feat:

 SQL> @fdelete.sf Function created. Input truncated to 12 characters SQL> exec DBMS_OUTPUT.PUT_LINE (        fdelete('c:\temp\te_employee.pkb')) 1 SQL> exec DBMS_OUTPUT.PUT_LINE (        fdelete('c:\temp\te_employee.pkb')) 0

I can also build utilities on top of this function. How about a procedure that deletes all of the files found in the rows of a nested table? Even better, a procedure that accepts a directory name and filter ("all files like *.tmp", for example) and deletes all files found in that directory that pass the filter.

In reality, of course, what I should do is build a package and then put all this great new stuff in there. And that is just what I will do in Section 9.9. Before we do that, however, let's take a closer look at each of the steps I just performed.


Team-Fly

Top