Troubleshooting Java Class Library Build, Load, Drop, and Use - Oracle

This section covers how to troubleshoot Java class libraries. Some of this becomes intuitive after a while but initially it is very tricky.

Building, Loading, and Dropping Java Class Library Objects

When you build Java class libraries, you can encounter a number of problems. Many errors occur through simple syntax rule violations, but often the PATH or CLASSPATH environment variable excludes required Java libraries. You need to ensure that your PATH environment variable includes the Java SDK released with the Oracle database you’re using. It’s best if you research which Java class libraries you’ll require and then source them into your CLASSPATH. The following illustrates the minimum for the examples used in this chapter by the operating system:

WINDOWS

C:> set PATH=%PATH%;C:%ORACLE_HOME%\jdk\bin C:> set CLASSPATH=%CLASSPATH%;C:%ORACLE_HOME%\jdbc\lib\classes12.zip

If you want to use the JPublisher command-line tool, you need to add both of the following Java archive files:

%ORACLE_HOME%\sqlj\lib\translator.zip
%ORACLE_HOME%\sqlj\lib\runtime12.zip

UNIX

# export PATH=$PATH:/<mount>/$ORACLE_HOME/jdk/bin
# export CLASSPATH=$CLASSPATH:/<mount>/$ORACLE_HOME/jdbc/lib/classes12.zip

If you want to use the JPublisher command-line tool, you must add both of these Java archive files to your CLASSPATH environment variable:

$ORACLE_HOME/sqlj/lib/translator.zip $ORACLE_HOME/sqlj/lib/runtime12.zip

Another potential problem in configuring Java archive access can be found in the LD_LIBRARY_PATH used in the listener.ora file. Check to make sure it’s set as follows:

LD_LIBRARY_PATH=C:\oracle\ora92\lib;C:\oracle\ora92\jdbc\lib

You may also encounter an error like this, which says you cannot drop a Java class file directly from your database instance. The error is raised by running the dropjava utility with the following syntax:

C:> dropjava -u plsql/plsql HelloWorld4.class

The following error message should then appear:

Error while dropping class HelloWorld4
    ORA-29537: class or resource cannot be created or dropped directly

The reason for the error is that you used loadjava with the source file, HelloWorld4.java. Thus, you should use dropjava and the source file, which will delete the class and source file.

NOTE

The behavior is generally consistent with this preceding description, but occasionally the command will work and delete both the source and class files from the Oracle JVM.

The error signaling that you have excluded something from your CLASSPATH environment variable should appear as follows:

C:\>loadjava -r -f -o -user plsql/plsql HelloWorld4.class
errors   : class HelloWorld4
    ORA-29521: referenced name oracle/jdbc2/SQLData could not be found
    ORA-29521: referenced name oracle/jdbc2/SQLInput could not be found
    ORA-29521: referenced name oracle/jdbc2/SQLOutput could not be found 
The following operations failed
    class HelloWorld4: resolution
exiting  : Failures occurred during processing

If you get an ORA-29549 error, you’re missing a Java archive reference. As noted early in the chapter, an ORA-29549 error is also raised when the Java class is removed and replaced the first time it’s called.

TIP

If you replace your Java class files, make sure you call them once from the target schema to avoid users managing the Java session change.

Now that you’ve reviewed the major issues with building, loading, and dropping Java stored object class files, let’s examine some errors in the SQL and PL/SQL environment.

Using Java Class Library Objects

When you use Java stored object classes, you should ensure you define only one constructor in the PL/SQL object type definition. The only constructor acted on by a PL/SQL object type wrapper is the default constructor.

TIP

Avoid overriding constructors unless you plan to call them from other Java libraries wrapped as procedures and functions.

An example of overriding constructors being ignored is found in the HelloWorld4e.sql script. The script references the HelloWorld4.class file addressed earlier in the chapter. HelloWorld4e.sql defines two constructors for the HelloWorld4.class file. One is a null argument constructor and the other is a single formal parameter argument. Since there’s no duplicate constructor defined in the targeted class file, you would expect the following object type definition to fail:

-- 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
, CONSTRUCTOR FUNCTION hello_world4 
  ( instanceName VARCHAR2 )
  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;
/

It does not fail, however, and instead succeeds to define a type that misrepresents the internal Java program’s capabilities. You can run this test program found in the HelloWorld4e.sql script, which demonstrates that the type fails to support the overriding constructor:

-- This is found in HelloWorld4e.sql file.
DECLARE
  -- Define and instantiate an object instance.
  my_obj1 hello_world4 := hello_world4('Adam');
  my_obj2 hello_world4 := hello_world4('Eve');
  PROCEDURE write_debug
  ( number_in NUMBER
  , value_in VARCHAR2 ) IS
  BEGIN
    INSERT INTO java_debug VALUES (number_in,value_in);
END write_debug;
BEGIN
  -- Test class instance. 
  dbms_output.put_line('Item #1: ['||my_obj1.getQualifiedName||']');
  write_debug(101,'Item #1 Completed');
  dbms_output.put_line('Item #2: ['||my_obj2.getQualifiedName||']');
  write_debug(102,'Item #2 Completed');
  dbms_output.put_line('Item #3: ['||my_obj1.getSQLTypeName||']');
  write_debug(103,'Item #3 Completed');
  dbms_output.put_line('Item #4: ['||my_obj1.getSQLTypeName||']');
  write_debug(104,'Item #4 Completed');
  -- Test metadata repository with DBMS_JAVA.
  dbms_output.put_line(
    'Item #5: ['||user||'.'||dbms_java.longname('HELLO_WORLD4')||']');
END;
/

This will send the following output to your console:

-- This output is generated from the HelloWorld4e.sql file.
DECLAR
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 4

This would imply that the overriding constructor takes a single VARCHAR2 formal parameter and cannot support a VARCHAR2 value. The real issue is that the SQLData type is what is passed and it’s managed as a SQLData type. As noted earlier, the methods used in the SQLData interface define how values are passed.

You may encounter many issues when first implementing stored Java object classes and thus may benefit from building a java_debug error management table like the following:

CREATE TABLE java_debug
( debug_number NUMBER
, debug_value VARCHAR2(4000) );

Adding the following method to your Java class files will enable you to write to the java_debug table:

// Define the debugLog() method.
public void debugLog(int debug_number
                    ,String debug_value) throws SQLException
{
  String statement = "INSERT INTO java_debug VALUES (?,?)";
  // Define a connection for Oracle.
  Connection conn = new OracleDriver().defaultConnection();
  // Define and initialize a prepared statement.
  PreparedStatement ps = conn.prepareStatement(statement);
  // Assign the cursor return.
  ps.setInt(1,debug_number);
  ps.setString(2,debug_value);
  ps.execute();
} // End of the debugLog() method.

You have now covered the major issues with troubleshooting Java stored object classes. The next section summarizes the mapping of Oracle types to Java types.