Oracle PL/SQL Programming: Guide to Oracle8i Features

The new Oracle built-in package DBMS_JAVA gives you access to, and the ability to modify, various characteristics of the Aurora Java Virtual Machine. The DBMS_JAVA_TEST package lets you test your Java stored procedures.

The DBMS_JAVA package contains a large number of programs, many of which are intended for Oracle internal use only. Nevertheless, there are a number of very useful programs that we can take advantage of. Most of these programs can also be called within SQL statements. Table 9.5 summarizes the programs.

Table 9.5. DBMS_JAVA Programs

9.7.1 LONGNAME: Converting Java Long Names

Java class and method names can easily exceed the maximum SQL identifier length of 30 characters . In such cases, Oracle creates a unique "short name" for the Java code element and uses that name for SQL- and PL/SQL- related access.

Use the following function to obtain the full (long) name for a given short name:

FUNCTION DBMS_JAVA.LONGNAME (shortname VARCHAR2) RETURN VARCHAR2

The following query displays the long name for all Java classes defined in the currently connected schema for which the long name and short names do not match:

/* Filename on companion disk: longname.sql */ SELECT object_name shortname, DBMS_JAVA.LONGNAME (object_name) longname FROM USER_OBJECTS WHERE object_type = 'JAVA CLASS' AND object_name != DBMS_JAVA.LONGNAME (object_name);

This query is also available inside the myJava package found in the myJava.pkg file; its use is shown here. Suppose that I define a class with this name:

public class DropAnyObjectIdentifiedByTypeAndName {

That is too long for Oracle, and we can verify that Oracle creates its own short name as follows :

SQL> exec myjava.showlongnames Short Name Long Name ---------------------------------------------------- Short: /247421b0_DropAnyObjectIdentif Long: DropAnyObjectIdentifiedByTypeAndName

9.7.2 GET_, SET_, and RESET_COMPILER OPTIONS: Getting and Setting Compiler Options

You can also set compiler option values in the database table JAVA$OPTIONS (called the options table from here on). Then, you can selectively override those settings using loadjava command-line options. A row in the options table contains the names of source schema objects to which an option setting applies. You can use multiple rows to set the options differently for different source schema objects.

The compiler looks up options in the options table unless they are specified on the loadjava command line. If there is no options-table entry or command-line value for an option, the compiler uses the following default values (you can find more information about nondefault values in the Oracle8i SQLJ Developer's Guide and Reference documentation):

encoding = latin1 online = true // applies only to SQLJ source files

You can get and set options-table entries using the following DBMS_ JAVA functions and procedures:

FUNCTION DBMS_JAVA.GET_COMPILER_OPTION ( what VARCHAR2, optionName VARCHAR2) PROCEDURE DBMS_JAVA.SET_COMPILER_OPTION ( what VARCHAR2, optionName VARCHAR2, value VARCHAR2) PROCEDURE DBMS_JAVA.RESET_COMPILER_OPTION ( what VARCHAR2, optionName VARCHAR2)

The parameter what is the name of a Java package, the full name of a class, or the empty string. After searching the options table, the compiler selects the row in which what most closely matches the full name of the schema object. If what is the empty string, it matches the name of any schema object.

optionName is the name of the option being set. Initially, a schema does not have an options table. To create one, use the procedure DBMS_ JAVA.SET_COMPILER_OPTION to set a value . The procedure creates the table if it does not exist. Enclose parameters in single quotes, as shown in the following example:

SQL> DBMS_JAVA.SET_COMPILER_OPTION ('X.sqlj', 'online', 'false');

9.7.3 SET_OUTPUT: Enabling Output from Java

The System.out and System.err classes send their output to the current trace files (when executed within the Oracle database). This is certainly not a very convenient repository if you simply want to test your code to see if it is working properly. DBMS_JAVA supplies a procedure you can call to redirect output to the DBMS_OUTPUT text buffer so that it can be flushed to your SQL*Plus screen automatically. The syntax of this procedure is:

PROCEDURE DBMS_JAVA.SET_OUTPUT (buffersize NUMBER);

Here is an example of how you would use this program:

//* Filename on companion disk: ssoo.sql */ SET SERVEROUTPUT ON SIZE 1000000 CALL DBMS_JAVA.SET_OUTPUT (1000000);

Documentation on the interaction between these two commands is skimpy; my testing has uncovered the following rules:

• The minimum (and default) buffer size is a measly 2000 bytes; the maximum size is 1,000,000 bytes. You can pass a number outside of that range without causing an error (unless the number is really big); it will simply be ignored.

• The buffer size specified by SET SERVEROUTPUT supersedes that of DBMS_JAVA.SET_OUTPUT. If, in other words, you provide a smaller value for the DBMS_JAVA call, it will be ignored, and the larger size used.

• If your output in Java exceeds the buffer size, you will not receive the error you get with DBMS_OUTPUT, namely:

  ORU-10027: buffer overflow, limit of nnn bytes

  The output will instead be truncated to the buffer size specified, and execution of your code will continue.

As is the case with DBMS_OUTPUT, you will not see any output from your Java calls until the stored procedure through which they are called finishes executing.

9.7.4 EXPORT_SOURCE, _RESOURCE, and _CLASS: Exporting Schema Objects

Oracle's DBMS_JAVA package offers the following set of procedures to export source, resources, and classes:

PROCEDURE DBMS_JAVA.EXPORT_SOURCE ( name VARCHAR2, [ blob BLOB clob CLOB ] ); PROCEDURE DBMS_JAVA.EXPORT_SOURCE ( name VARCHAR2, schema VARCHAR2, [ blob BLOB clob CLOB ] ); PROCEDURE DBMS_JAVA.EXPORT_RESOURCE ( name VARCHAR2, [ blob BLOB clob CLOB ] ); PROCEDURE DBMS_JAVA.EXPORT_RESOURCE ( name VARCHAR2, schema VARCHAR2, [ blob BLOB clob CLOB ] ); PROCEDURE DBMS_JAVA.EXPORT_CLASS ( name VARCHAR2, blob BLOB ); PROCEDURE DBMS_JAVA.EXPORT_CLASS ( name VARCHAR2, schema VARCHAR2, blob BLOB );

In all cases, name is the name of the Java schema object to be exported, schema is the name of the schema owning the object (if not supplied, then the current schema is used), and blobclob is the large object that receives the specified Java schema object.

You cannot export a class into a CLOB, only into a BLOB. In addition, the internal representation of the source uses the UTF8 format, so that format is used to store the source in the BLOB as well.

The following prototype procedure offers an idea of how you might use the export programs to obtain source code of your Java schema objects, when appropriate:

/* Filename on companion disk: showjava.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); pl (v); /* run pl.sp to create this procedure */ END; /

If I then create a Java source object using the CREATE JAVA statement as follows:

CREATE OR REPLACE JAVA SOURCE NAMED "Hello" AS public class Hello { public static String hello() { return "Hello Oracle World"; } }; /

I can view the source code as shown here ( assuming that DBMS_OUTPUT has been enabled):

SQL> exec show_java_source ('Hello') public class Hello { public static String hello() { return "Hello Oracle World"; } };

9.7.5 Using DBMS_JAVA_TEST

DBMS_ JAVA_TEST provides a facility for testing your Java stored procedures. It contains a single, overloaded function named FUNCALL, as in "function call." It has this header:

FUNCTION DBMS_JAVA_TEST.FUNCALL ( class IN VARCHAR2, method IN VARCHAR2, s1 IN VARCHAR2 := NULL, s2 IN VARCHAR2 := NULL, s3 IN VARCHAR2 := NULL, s4 IN VARCHAR2 := NULL, s5 IN VARCHAR2 := NULL, s6 IN VARCHAR2 := NULL, s7 IN VARCHAR2 := NULL, s8 IN VARCHAR2 := NULL, s9 IN VARCHAR2 := NULL, s10 IN VARCHAR2 := NULL, s11 IN VARCHAR2 := NULL, s12 IN VARCHAR2 := NULL, s13 IN VARCHAR2 := NULL, s14 IN VARCHAR2 := NULL, s15 IN VARCHAR2 := NULL, s16 IN VARCHAR2 := NULL, s17 IN VARCHAR2 := NULL, s18 IN VARCHAR2 := NULL, s19 IN VARCHAR2 := NULL, s20 IN VARCHAR2 := NULL) RETURN VARCHAR2

In actuality, DBMS_ JAVA_TEST contains 20 overloadings of FUNCALL, each with a different number of s1 through s20 parameters, without any NULL default values. That interface is required for the call through to Java.

Here is an example of calling this function:

BEGIN p.l (DBMS_JAVA_TEST.FUNCALL ( 'JFile2', 'length', 'd:\java\jfile2.java')); END; /