26-1.

(c). Oracle8i’s Java Virtual Machine is called Aurora (Aur-ora…get it?)

26-2.

The statements are:

26-3.

(b). Java programs that are compiled and stored in the database are called Java stored procedures (JSPs).

26-4.

(d). PL/SQL procedures and functions are most similar to Java methods.

26-5.

(d). Compiling a Java program creates a class file that ends in .class.

26-6.

(a). A call spec establishes a logical correspondence between a Java method and a PL/SQL procedure or function (standalone or packaged) or a member method of a SQL object type.

26-7.

(b). The Java class name is the missing component in the listing; note that the Java class name is similar to a PL/SQL package name in that it identifies a group of items, such as variables and methods.

26-8.

(d). The method name is the missing component in the listing. Java methods are similar to PL/SQL procedures or functions.

26-9.

(b). The parameter datatype is the missing component in the listing. Like PL/SQL, Java parameters have a name and a type; unlike PL/SQL, Java parameters’ datatypes are specified before their parameter names.

26-10.

(e). The method return type is the missing component in the listing. All Java methods must have an associated type, even if it doesn’t actually return anything (in this case, the return type is void, rather than a “true” type such as int or double).

26-11.

(d). The function created in example (d) is a call spec, a PL/SQL function (or procedure) that maps a PL/SQL function (or procedure) onto a Java method. The call spec associates a PL/SQL function (or procedure) with a specific method within a class and defines a one-to-one mapping between the PL/SQL function’s (or procedure’s) parameters and the Java method’s parameters.

26-12.

These steps for accessing a Java class from PL/SQL are now in order:

26-13.

The operating system commands that compile and load the helloWorld class are:

javac helloWorld.java
loadjava "u scott/tiger "force helloWorld.class

26-14.

The Oracle8i DDL command that loads the helloWorld class directly into the database is:

CREATE OR REPLACE JAVA SOURCE NAMED "helloWorld" AS
   public class helloWorld {
      public static void doit () {
          System.out.println("Hello World!!!");
      }
   };

26-15.

(b). You include the LANGUAGE JAVA clause in a function or procedure header to create a call spec for a Java method.

26-16.

Executing DBMS_JAVA.SET_OUTPUT (buffer size) is the equivalent of the command SET SERVEROUTPUT ON. You will not see any output from the Java’s System.out or System.err until you execute this command.

26-17.

(c). You should use a procedure to publish a void Java method.

26-18.

Here is the completed table (remember that there are many Java equivalents for each PL/SQL datatype).

26-19.

The call spec for the OutputTest class is:

CREATE OR REPLACE PROCEDURE output_test (str IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'OutputTest.printIt (java.lang.String)';

26-20.

The call spec for the Fibonacci class is:

CREATE OR REPLACE FUNCTION fib (n NUMBER) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'Fibonacci.fib(int) return int';

26-21.

The Java source code (the .java file) must have the same name as the class it contains; otherwise, you receive an error:

E:JAVA>javac test.java
test.java:1: Public class helloWorld must be defined in a file called
   "helloWorld.java".
public class helloWorld {
             ^
1 error

E:JAVA>

26-22.

The DDL statement to create the call spec will work fine; it won’t report any creation errors. When you attempt to execute the my_hello procedure, however, you receive an error message stating that there is no method named doIt in the class. The problem is that the method’s name is doit, not doIt (remember, case matters in Java!). To fix the problem, you must change the call spec to match the method name. Here’s the output from SQL*Plus:

SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_JAVA.SET_OUTPUT(1000);

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE my_hello
  2  AS LANGUAGE JAVA
  3  NAME 'helloWorld.doIt()';
  4  /

Procedure created.

SQL> EXEC my_hello
java.lang.NoSuchMethodException: No applicable method found

..
BEGIN my_hello; END;

*
ERROR at line 1:
ORA-29531: no method doIt in class helloWorld
ORA-06512: at "SCOTT.MY_HELLO", line 0
ORA-06512: at line 1

26-23.

As in the previous exercise, the call spec compiles but doesn’t run. The problem in this case is that you can publish only static methods. To fix the problem, add the static keyword to the definition of doit, recompile, and reload the class.

26-24.

The following query displays information about the Java objects in a user schema:

SELECT object_name, object_type, status, timestamp
  FROM user_objects
 WHERE (object_name NOT LIKE 'SYS_%'
         AND object_name NOT LIKE 'CREATE$%'
         AND object_name NOT LIKE 'JAVA$%'
         AND object_name NOT LIKE 'LOADLOB%')
   AND object_type LIKE 'JAVA %'
 ORDER BY object_type, object_name;

26-25.

The following procedure is an example of how to fetch the source code for a JSP into a CLOB, and then print it (note that you need a more sophisticated method to print bigger source files!):

/* Filename on web page: show_java_source.sp */
CREATE OR REPLACE PROCEDURE show_java_source (
   name IN VARCHAR2,
   schema IN VARCHAR2 := NULL
   )
IS
  b CLOB;
  v VARCHAR2(2000) ;
  i INTEGER ;
BEGIN
  /* Move the Java source code to a CLOB. */
  DBMS_LOB.CREATETEMPORARY (b, FALSE);
  DBMS_JAVA.EXPORT_SOURCE (name, NVL (schema, USER), b);

  /* Read the CLOB to a VARCHAR2 variable and display it. */
  i := 1000;
  DBMS_LOB.READ (b, i, 1, v);
  DBMS_OUTPUT.PUT_LINE (v); /* run pl.sp to create this procedure */
END;
/

SQL> EXEC SHOW_JAVA_SOURCE('Hello'),
public class Hello {
      public static String hello() {
         return "Hello World";   } };

PL/SQL procedure successfully completed.

SQL>

26-26.

The smallestFirst method swaps the parameter values so that the smallest one comes first. Since the parameters change, you must declare your corresponding PL/SQL parameters as IN OUT:

CREATE OR REPLACE PROCEDURE smallest_first
   (a IN OUT NUMBER, b IN OUT NUMBER) AS LANGUAGE JAVA
NAME 'NumberTest.smallestFirst(int[], int[])';

The following listing shows the results if you make the parameters IN only:

SQL> CREATE OR REPLACE PROCEDURE smallest_first
  2  (a IN NUMBER, b IN NUMBER)
  3  AS LANGUAGE JAVA
  4  NAME 'NumberTest.smallestFirst(int[], int[])';
  5  /

Procedure created.

SQL> declare
  2     x number := 100;
  3     y number := 10;
  4  begin
  5     dbms_output.put_line ('X => ' || x || ', y=> ' || y);
  6     smallest_first (x,y);
  7     dbms_output.put_line ('X => ' || x || ', y=> ' || y);
  8  end;
  9  /
X => 100, y=> 10
java.lang.NullPointerException

at NumberTest.smallestFirst(NumberTest.java:3)

declare
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
   java.lang.NullPointerException
ORA-06512: at "SCOTT.SMALLEST_FIRST", line 0
ORA-06512: at line 6

26-27.

The missing component is “oracle.sql.STRUCT”, which passes complex Oracle object datatypes to Java.

26-28.

Here is the specification for the basic file-operations package, along with the definition for the file_list_t type:

CREATE TYPE file_list_t IS TABLE OF VARCHAR2(2000);
/

CREATE OR REPLACE PACKAGE basic_file_ops
IS
   FUNCTION length (file IN VARCHAR2) RETURN NUMBER;
   FUNCTION delete (file IN VARCHAR2) RETURN BOOLEAN;
   PROCEDURE getDirContents (
      dir IN VARCHAR2,
      files IN OUT file_list_t);
END;

26-29.

One of the principles of good design is to remove hardcoded values (sometimes called magic values) from programs by replacing them with constants or functions. This technique frees you from worrying how Java represents a Boolean true; whether the class uses a 1, –1, or even 3.1459 is not your concern. You just have to make sure your PL/SQL program interprets these values correctly.

The easiest way to do this is to store the return values of the Java “get” methods in packaged variables, which you can then use in place of literal values. First, you need three private, global variables to hold the Java literals. Second, you need three functions to return the literal values that represent true, false, and the filename delimiter. Finally, you need to use the package-initialization section to actually store the function’s return value into the appropriate variable. Here’s the code:

/*Filename on web page: basic_file_ops.pkg */
CREATE OR REPLACE PACKAGE BODY basic_file_ops
IS
   /* Used to handle conversion from number to boolean. */
   g_true INTEGER;
   g_false INTEGER;
   g_listdelim CHAR(1);

   FUNCTION tval RETURN NUMBER AS LANGUAGE JAVA
      NAME 'JFile.tVal () return int';
   FUNCTION fval RETURN NUMBER AS LANGUAGE JAVA
      NAME 'JFile.fVal () return int';
   FUNCTION delim RETURN VARCHAR2 AS LANGUAGE JAVA
      NAME 'JFile.listDelimiter () return java.lang.STring';

   -- Other procedures go here

BEGIN
   g_true := tval;
   g_false := fval;
   g_listdelim := delim;
END;

26-30.

The easiest way to implement the delete-file function is to put a wrapper around the call spec to convert the results of the Java function into a PL/SQL Boolean value. This technique comes in handy in situations where you want to massage the results from a Java call:

/* Filename on web page: basic_file_ops.pkg */
   -- Here's the call spec...
   FUNCTION Idelete (file IN VARCHAR2) RETURN NUMBER
   AS LANGUAGE JAVA
      NAME 'JFile.delete (java.lang.String) return int';

   -- And here's the wrapper
   FUNCTION delete (file IN VARCHAR2) RETURN BOOLEAN
   AS
   BEGIN
      RETURN Idelete (file) = g_true;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE ('Error deleting: ' || SQLERRM);
         RETURN FALSE;
   END;

26-31.

The function to retrieve the file’s size is straightforward:

FUNCTION length (file IN VARCHAR2) RETURN NUMBER
   AS LANGUAGE JAVA
      NAME 'JFile.length (java.lang.String) return long';

26-32.

As with the delete function, you implement the getDirContents file with two procedures. The first simply retrieves the delimited string returned by the Java function. The second parses the string and places each element into a PL/SQL table:

/* Filename on web page: basic_file_ops.pkg */
   FUNCTION dirContents (dir IN VARCHAR2) RETURN VARCHAR2
   AS LANGUAGE JAVA
      NAME 'JFile.dirContents (java.lang.String) return java.lang.String';

   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;

26-33.

You must grant the JAVAUSERPRIV role to a schema before it can read or write a file. Then you must grant the JAVASYSPRIV role to create or delete files. For example:

GRANT JAVAUSERPRIV TO SCOTT;
GRANT JAVASYSPRIV TO BIG_SCHEMA;

Finally, modify the UTL_FILE_DIR in INIT.ORA to specify the accessible directories:

utl_file_dir = c:	emp