Oracle PL/SQL Programming: Guide to Oracle8i Features

Once you have written your Java classes and loaded them into the Oracle RDBMS, you can call their methods from within PL/SQL (and SQL)but only after you "publish" those methods via a PL/SQL wrapper.

9.8.1 Call Specs

You only need to build wrappers in PL/SQL for those Java methods you want to make available through a PL/SQL interface. Java methods can access other Java methods in the Java Virtual Machine directly, without any need for a wrapper. To publish a Java method, you write a call spec a PL/SQL program header (function or procedure) whose body is actually a call to a Java method via the LANGUAGE JAVA clause. This clause contains the following information about the Java method: its full name , its parameter types, and its return type. You can define these call specs as standalone functions or procedures, as programs within a package, and as methods in an object type:

CREATE [OR REPLACE] --Only if a standalone program <Standard PL/SQL procedure/function header> {IS AS} LANGUAGE JAVA NAME ' method_fullname ( java_type_fullname [, java_type_fullname ]...) [return java_type_fullname ]';

Where java_type_fullname is the full name of the Java type, such as java.lang.String.

The NAME clause string identifies uniquely the Java method being wrapped. The fully qualified Java names and the call spec parameters, which are mapped by position only, must correspond , one to one, with the parameters in the program. If the Java method takes no arguments, code an empty parameter list for it but not for the function or procedure.

Here are a few examples:

• A standalone function calling a method:

CREATE OR REPLACE FUNCTION fDelete ( file IN VARCHAR2) RETURN NUMBER AS LANGUAGE JAVA NAME 'JDelete.delete ( java.lang.String) return int';

• A packaged procedure with the LANGUAGE clause in the specification that passes an object type as a parameter:

CREATE OR REPLACE PACKAGE nat_health_care IS PROCEDURE consolidate_insurer (ins Insurer) AS LANGUAGE JAVA NAME 'NHC_consolidation.process(oracle.sql.STRUCT)'; END nat_health_care;

• An object type method with the LANGUAGE clause in the specification:

CREATE TYPE WarCriminal AS OBJECT ( name VARCHAR2(100), victim_count NUMBER, MEMBER FUNCTION sentencing_date ( name_in IN VARCHAR2) RETURN DATE AS LANGUAGE JAVA NAME 'warCriminal.dos (java.lang.String) return java.sql.Timestamp'

9.8.2 Some Rules for Java Wrappers

Note the following rules for Java wrappers:

• A PL/SQL call spec and the Java method it publishes must reside in the same schema.

• A call spec exposes a Java method's top-level entry point to Oracle. As a result, you can publish only public static methods, unless you are defining a member method of a SQL object type. In this case, you can publish instance methods as member methods of that type.

• A method in object-oriented languages cannot assign values to objects passed as arguments; the point of the method is to apply to the object to which it is attached. When you want to call a method from SQL or PL/SQL and change the value of an argument, you must declare it as an OUT or IN OUT parameter in the call spec. The corresponding Java parameter must then be a one-element array.

• You can replace the element value with another Java object of the appropriate type, or (for IN OUT parameters only) modify the value if the Java type permits . Either way, the new value propagates back to the caller. For example, you might map a call spec OUT parameter of type NUMBER to a Java parameter declared as float[] p, then assign a new value to p[0].

9.8.3 Mapping Datatypes

Earlier, I showed you one very simple example of a PL/SQL wrapper. That delete function passed a VARCHAR2 value to a java.lang.String parameter. The Java method returned an int, which was then passed back through the RETURN NUMBER clause of the PL/SQL function. Those are two straightforward examples of datatype mapping , that is, setting up a correspondence between a PL/SQL datatype and a Java datatype.

When you build a PL/SQL call spec, the PL/SQL and Java parameters, as well as the function result, are related by position and must have compatible datatypes. Table 9.6 lists all the datatype mappings currently allowed between PL/SQL and Java. If you rely on a supported datatype mapping, Oracle will convert from one to the other automatically.

As you can see with a quick glance at the mapping table, Oracle supports only automatic conversion for SQL datatypes. PL/SQL-specific datatypes, including BINARY_INTEGER, PLS_INTEGER, BOOLEAN, and index-by table types, are not supported. In those cases, you will have to perform manual conversion steps to transfer data between these two execution environments. See Section 9.9 for examples of nondefault mappings; see Oracle documentation for even more detailed examples involving the use of JDBC.

Table 9.6. Legal Datatype Mappings

9.8.4 Calling a Java Method in SQL

You can call PL/SQL functions of your own creation from within SQL DML statements. You can also call Java methods wrapped in PL/SQL from within SQL. However, these methods must conform to the following purity rules:

• If you call a method from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the method is not allowed to modify any database tables.

• If you call a method from an INSERT, UPDATE, or DELETE statement, the method cannot query or modify any database tables modified by that statement.

• If you call a method from a SELECT, INSERT, UPDATE, or DELETE statement, it cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM). You also cannot execute DDL statements, since they automatically perform a commit in your session.

The objective of these restrictions is to control side effects that might disrupt your SQL statements. If you try to execute a SQL statement that calls a method violating any of these rules, you will receive a runtime error when the SQL statement is parsed.

It is also possible to call Java from PL/SQL via the SQL layer using the CALL command syntax and native dynamic SQL, as shown in the following code (the implementation of dropany is shown in the next section):

DECLARE Tp varchar2(30):='TABLE'; Nm varchar2(30):='mytable'; BEGIN EXECUTE IMMEDIATE 'CALL dropany(:tp,:nm)' USING tp, nm; END;

9.8.5 Exception Handling with Java

On the one hand, the Java exception handling architecture is very similar to that of PL/SQL. In Java-speak, you throw an exception and then catch it. In PL/SQL-speak, you raise an exception and then handle it.

On the other hand, exception handling in Java is much more robust. Java offers a foundation class called Exception. All exceptions are objects based on that class, or on classes derived from (extending) Exception. You can pass exceptions as parameters and manipulate them pretty much as you would objects of any other class.

When a Java stored method executes a SQL statement and an exception is thrown, then that exception is an object from a subclass of java.sql.SQLException. That class contains two methods that return the Oracle error code and error message: getErrorCode( ) and getMessage( ).

If a Java stored procedure called from SQL or PL/SQL throws an exception that is not caught by the JVM, the caller gets an exception thrown from a Java error message. This is how all uncaught exceptions (including non-SQL exceptions) are reported . Let's take a look at different ways of handling errors and the resulting output.

Suppose that I create a class that relies on JDBC to drop objects in the database (this is drawn from an example in Oracle documentation):

/* Filename on companion disk: dropany.java */ import java.sql.*; import java.io.*; import oracle.jdbc.driver.*; public class DropAny { public static void object (String object_type, String object_name) throws SQLException { // Connect to Oracle using JDBC driver Connection conn = new OracleDriver().defaultConnection(); // Build SQL statement String sql = "DROP " + object_type + " " + object_name; try { Statement stmt = conn.createStatement(); stmt.executeUpdate(sql); stmt.close(); } catch (SQLException e) {System.err.println(e.getMessage());} } }

This version traps and displays any SQLException with this line:

} catch (SQLException e) {System.err.println(e.getMessage());}

I wrap this class inside a PL/SQL procedure as follows :

CREATE OR REPLACE PROCEDURE dropany ( tp IN VARCHAR2, nm IN VARCHAR2 ) AS LANGUAGE JAVA NAME 'DropAny.object ( java.lang.String, java.lang.String)'; /

When I attempt to drop a nonexistent object, I will see one of the following two outcomes :

SQL> CONNECT scott/tiger Connected. SQL> SET SERVEROUTPUT ON SQL> BEGIN dropany ('TABLE', 'blip'); END; / PL/SQL procedure successfully completed. SQL> CALL DBMS_JAVA.SET_OUTPUT (1000000); Call completed. SQL> BEGIN dropany ('TABLE', 'blip'); END; / ORA-00942: table or view does not exist

What you are seeing in these examples is a reminder that output from System.err.println will not appear on your screen until you explicitly enable it with a call to DBMS_ JAVA.SET_OUTPUT. In either case, however, no exception was raised back to the calling block, since it was caught inside Java. After the second call to dropany, you can see that the error message supplied through the getMessage( ) method is one taken directly from Oracle.

If I comment out the try and catch lines in the DropAny.obj method, I will get very different behavior, as shown:

SQL> BEGIN 2 dropany ('TABLE', 'blip'); 3 EXCEPTION 4 WHEN OTHERS 5 THEN 6 DBMS_OUTPUT.PUT_LINE (SQLCODE); 7 DBMS_OUTPUT.PUT_LINE (SQLERRM); 8 END; 9 / java.sql.SQLException: ORA-00942: table or view does not exist at oracle.jdbc.kprb.KprbDBAccess.check_error(KprbDBAccess.java) at oracle.jdbc.kprb.KprbDBAccess.parseExecuteFetch(KprbDBAccess.java) at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java) at oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleStatement.java) at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java) at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java) at DropAny.object(DropAny.java:14) -29532 ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: ORA-00942: table or view does not exist

This takes a little explaining. Everything between:

java.sql.SQLException: ORA-00942: table or view does not exist

and

-29532

represents an error stack dump generated by Java and sent to standard output, regardless of how you handle the error in PL/SQL. In other words, even if my exception section looked like this:

EXCEPTION WHEN OTHERS THEN NULL;

I would still get all that output to the screen, and then processing in the outer block (if any) would continue. The last three lines of output displayed are generated by the calls to DBMS_OUTPUT.PUT_LINE. Notice that the Oracle error is not ORA-00942, but instead is ORA-29532, a generic Java error. This is a problem. If you trap the error, how can you discover what the real error is? Looks like it's time for Write-A-Utility Man!

It appears to me that the error returned by SQLERRM is of this form:

ORA-29532: Java call ...: java.sql.SQLException: ORA- NNNNN ...

So I can scan for the presence of "java.sql.SQLException" and then SUBSTR from there. Here is a procedure that returns the error code and message for the current error, building in the smarts to compensate for the Java error message format:

/* Filename on companion disk: getErrorInfo.sp */ CREATE OR REPLACE PROCEDURE getErrorInfo ( errcode OUT INTEGER, errtext OUT VARCHAR2) IS c_keyword CONSTANT CHAR(23) := 'java.sql.SQLException: '; c_keyword_len CONSTANT PLS_INTEGER := 23; v_keyword_loc PLS_INTEGER; v_msg VARCHAR2(1000) := SQLERRM; BEGIN v_keyword_loc := INSTR (v_msg, c_keyword); IF v_keyword_loc = 0 THEN errcode := SQLCODE; errtext := SQLERRM; ELSE errtext := SUBSTR ( v_msg, v_keyword_loc + c_keyword_len); errcode := SUBSTR (errtext, 4, 6 /* ORA-NNNNN */); END IF; END; /

The following block demonstrates how I might use this procedure (it relies on the log81 package, created by the log81.pkg file, to write the error information to the log):

/* Filename on companion disk: dropany2.tst */ BEGIN dropany ('TABLE', 'blip'); EXCEPTION WHEN OTHERS THEN DECLARE v_errcode PLS_INTEGER; v_errtext VARCHAR2(1000); BEGIN getErrorInfo (v_errcode, v_errtext); log81.saveline (v_errcode, v_errtext); END; END; /