HomeOracle Page 3 - Extending PL/SQL with Java Libraries, concluded
More on Building Internal Server Java Objects - Oracle
If you are looking for a way to extend stored programs with Java, look no further. This article, the second of two parts, is excerpted from chapter five of Expert Oracle PL/SQL, written by Ron Hardman and Michael McLaughlin (McGraw-Hill/Osborne, 2005; ISBN: 0072261943).
If you have not built the PLSQL schema, please run the create_user.sql script now. When you have the PLSQL schema built, you can compile it with the javac utility as covered earlier in the chapter. However, there is an alternative syntax that enables you to load and compile against the Oracle JVM libraries. You can directly load a Java source, or text, file with the loadjava utility as follows:
The loadjava utility command behaves slightly differently when you choose this option. It parses, stores the Java source as a text entry, and compiles the stored Java source into a Java byte stream in the Oracle JVM under the PLSQL schema.
After loading the Java class file into the database this way, you won’t be able to use the dropjava utility to remove the HelloWorld4.class file. Instead, use the dropjava utility to remove the HelloWorld4.java file, which also drops the HelloWorld4.class file.
You’ll need to build a SQL object type to wrap the Java stored object class. The following HelloWorld4.sql script builds the object type as a wrapper to the Java class object:
-- Available online as part of HelloWorld4.sql file. -- Create a PL/SQL wrapper package to a Java class file. CREATE OR REPLACE TYPE hello_world4 AS OBJECT EXTERNAL NAME 'HelloWorld4' LANGUAGE JAVA USING SQLData ( instanceName VARCHAR2(100) EXTERNAL NAME 'java.lang.String' , CONSTRUCTOR FUNCTION hello_world4 RETURN SELF AS RESULT , MEMBER FUNCTION getQualifiedName RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'HelloWorld4.getQualifiedName() return java.lang.String' , MEMBER FUNCTION getSQLTypeName RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'HelloWorld4.getSQLTypeName() return java.lang.String' ) INSTANTIABLE FINAL; /
The SQL object type wrapper does the following:
It defines an object type using an external name that is the case-sensitive Java class name and the USING SQLData clause. The USING SQLData clause requires at least one variable with an external name that identifies the Java data type.
It defines a single constructor function that takes no arguments. If you provide an overriding constructor, it will not be used.
It defines two functions. One reads the instance qualifiedName value and another reads the SQLTypeName value.
Any attempt to use SQLData without a mapped type will raise an exception. If you want to instantiate a class and not pass any variables to it, you can designate a blank VARCHAR2(1) EXTERNAL NAME ‘java.lang.String’ in the wrapper. Then, you simply avoid defining the streams in the SQLData interface methods readSQL and writeSQL and pass a NULL argument when instantiating the PL/SQL wrapper in your PL/SQL programs.
After you’ve defined the PL/SQL object type wrapper, you can see that both the object type and body have been registered in the Oracle instance metadata. You can see this by running the following query:
COL object_name FORMAT A30 COL object_type FORMAT A12 COL status FORMAT A7 SELECT object_name , object_type ' status FROM user_objects WHERE object_name = 'HELLO_WORLD4';
The output, if you have run everything successfully, will be the following:
OBJECT_NAME OBJECT_TYPE STATUS ----------------- ----------- ------ HELLO_WORLD4 TYPE VALID HELLO_WORLD4 TYPE BODY VALID
If you use the dropjava utility at this point, you’ll invalidate the TYPE BODY. Reloading the Java source file with the loadjava utility leaves the TYPE BODY in an invalid status. The first call to the object results in the following error:
--Available online as part of HelloWorld4.sql script as qualified above. DECLARE * ERROR at line 1: ORA-29549: class PLSQL.HelloWorld4 has changed, Java session state cleared ORA-06512: at "PLSQL.HELLO_WORLD4", line 0 ORA-06512: at line 10
A second call to the object results in success, but the Oracle instance metadata will still report that the TYPE BODY is invalid. The metadata report is incorrect, but you’ll need to run an ALTERcommand to fix it. For example, you can use the following:
ALTER TYPE hello_world4 COMPILE BODY;
Now, you’ll test this PL/SQL object type wrapper by instantiating two object instances with the following script:
-- Available online as part of HelloWorld4.sql file. DECLARE -- Define and instantiate an object instance. my_obj1 hello_world4 := hello_world4('Adam'); my_obj2 hello_world4 := hello_world4('Eve'); BEGIN -- Test class instance. dbms_output.put_line('Item #1: ['||my_obj1.getQualifiedName||']'); dbms_output.put_line('Item #2: ['||my_obj2.getQualifiedName||']'); dbms_output.put_line('Item #3: ['||my_obj1.getSQLTypeName||']'); dbms_output.put_line('Item #4: ['||my_obj1.getSQLTypeName||']'); -- Test metadata repository with DBMS_JAVA. dbms_output.put_line( 'Item #5: ['||user||'.'||dbms_java.longname('HELLO_WORLD4')||']'); END; /
The SQLData interface allows you to pass a User Defined Type (UDT), which means you can use any defined user structure. If you debug the execution of the Java instance, you’ll find that each invocation of the instance method actually reinstantiates the class instance.
The next section discusses troubleshooting the Java class library processes that build, load/drop, and use Java server stored object classes.