Home arrow Oracle arrow Page 4 - Extending PL/SQL with Java Libraries, concluded

Troubleshooting Java Class Library Build, Load, Drop, and Use - 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).

TABLE OF CONTENTS:
  1. Extending PL/SQL with Java Libraries, concluded
  2. Building Internal Server Java Objects
  3. More on Building Internal Server Java Objects
  4. Troubleshooting Java Class Library Build, Load, Drop, and Use
  5. Mapping Oracle Types
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 12
January 19, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

Why Would I Use This?

 As weíve worked with the advanced product stack in Oracle, helpful troubleshooting hints have saved us hours of time that might be spent banging away fruitlessly on the keyboard. Weíve found that Oracle server-side Java components have a number of errors that have led us astray. Therefore, we want to let you know what they are.

We find it frustrating to use Oracle features when the troubleshooting steps are spread across three to ten manuals. Too often these are not cross-referenced
and are hard to find. We believe this section will save you hours of setup and configuration effort to make the examples work.

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.



 
 
>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: