Polishing up the delete method

Before moving on to new and exciting stuff, we should make sure that what we've done so far is optimal. 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 NUMBERAS LANGUAGE JAVA NAME 'JDelete.delete (java.lang.String) return int';

So what's the problem? The problem is that I have been forced to use clumsy, numeric representations for TRUE/FALSE values. As a result, I must write code like this:

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

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

I would much rather define adelete_file function with this header:

FUNCTION fDelete ( file IN VARCHAR2) RETURN BOOLEAN;

So let's see what it would take to present that clean, easy-to-use API to 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:

/* File on web: 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; now it's time to shift attention to my PL/SQL package. Here's the first pass at the specification of xfile:

/* File on web: xfile.pkg */CREATE OR REPLACE PACKAGE xfileIS 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:

/* File on web: xfile.pkg */CREATE OR REPLACE PACKAGE BODY xfileIS 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 NUMBERAS LANGUAGE JAVA NAME 'JFile.tVal ( ) return int'; FUNCTION fval RETURN NUMBERAS LANGUAGE JAVA NAME 'JFile.fVal ( ) return int';

I have now succeeded in softcoding 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 NUMBERAS 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 BOOLEANASBEGIN 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 method employed again and again in the xfile package body.