Compiling and Loading into Oracle

Now that my class is written, I need to compile. To do this I open an MS-DOS session on a Windows platform, change to the d:\Java directory (or wherever the Sun JDK is installed on your machine), 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 class—and give it the right parameter list—you 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, it will automatically be called to display your custom description of the object. This is especially useful when your object consists of many elements that make sense only when presented a certain way, or that otherwise require formatting to be readable.

So let's add a simple main method (shown in bold) toJDelete:

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

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

D:\Java>javac JDelete.java D:\Java>java JDelete c:\temp\te_employee.pks1 D:\Java>java JDelete c:\temp\te_employee.pks0

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 again, 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 while 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! Though Alex Romankeuich, one of our technical reviewers, notes that if you declare "private static final PrintStream o = System.out;" at the beginning of the class, you can then display output in the class with the command "o.println"—only 9 characters in all!

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.showobjectsObject Name Object Type Status Timestamp---------------------------------------------------Hello JAVA CLASS VALID 1999-05-19:16:42JDelete JAVA CLASS VALID 1999-06-07:13:20JFile2 JAVA CLASS VALID 1999-05-26:17:07JFile3 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.

22.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.

/* File on web: fdelete.sf */CREATE OR REPLACE FUNCTION fDelete ( file IN VARCHAR2) RETURN NUMBERAS 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, not a class, so that is the complete specification.

22.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, how about 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 later in this chapter. First, however, let's take a closer look at each of the steps I just performed.