9.8 Publishing and Using Java in PL/SQLOnce 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 SpecsYou 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:
CREATE OR REPLACE FUNCTION fDelete ( file IN VARCHAR2) RETURN NUMBER AS LANGUAGE JAVA NAME 'JDelete.delete ( java.lang.String) return int';
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;
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 WrappersNote the following rules for Java wrappers:
9.8.3 Mapping DatatypesEarlier, 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.
9.8.4 Calling a Java Method in SQLYou 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:
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 JavaOn 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; /
Copyright (c) 2000 O'Reilly & Associates. All rights reserved. | ||||||||||||||||||||||||||
|