Extending PL/SQL with Java Libraries, concluded

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).

Building Internal Server Java Procedures

Building a procedure will follow very similar rules to building functions. PL/SQL procedures have an IN or IN and OUT mode. However, you cannot use an IN and OUT mode in PL/SQL when wrapping a Java method. If you attempt to define a package body with a procedure using IN and OUT modes, it will raise the following exception:

PLS-00235: the external type is not appropriate for the parameter

You’ll now build an IN mode procedure as a wrapper to a Java class method. When you use Java methods in the context of a procedure, you return a void type from the Java method.

Why Would I Use This?

We use internal server Java procedures for the same reasons we write PL/SQL procedures, which is to process result sets that may or may not return a result and which involve Data Manipulation Language (DML) commands. Java procedures have the ability to mirror PL/SQL procedures and call external libraries to leverage Java Archive Repository (JAR) files.

This feature is very effective when we have an application written in Java and want to enable the development team to write their server-side code in the same language. We have found that enabling them to stay in Java minimizes
errors.

The following Java source file supports an IN mode PL/SQL procedure:

– Available online as part of HelloWorld3.java file.
// Oracle class imports.
import java.sql.*;
import oracle.jdbc.driver.*;
// Class definition.
public class HelloWorld3
{
 
// Define the doDML() method.
  public static void doDML(String statement
                          ,String name) throws SQLException
 
{
    // 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.setString(1,name);
    ps.execute();
 
} // End of the doDML() method.
  // ————————————–/
 
// Define the doDQL() method.
  public static String doDQL(String statement) throws SQLException
  {
   
// Define and initialize a local return variable.
    String result = new String();
    // Define a connection for Oracle.
    Connection conn = new OracleDriver().defaultConnection();
   
// Define and initialize a prepared statement.
    PreparedStatement ps = conn.prepareStatement(statement);
   
// Execute a query.
    ResultSet rs = ps.executeQuery();
   
// Use a while-loop even though only one row is returned.
    while (rs.next())
    {
     
// Assign the cursor return.
      result = rs.getString(1);
    }
   
// Return the user name.
    return result;
 
} // End of the doDQL() method.
} // End of HelloWorld3 class.

The program does the following:

■ It defines a single class with two static methods. One method returns a void and the other returns a String, which maps to a VARCHAR2 data type. The methods do the following:

  • The myDML() method has a signature with two formal parameters. Both parameters are String data types. One takes the SQL statement and the second sends the data to be inserted. It creates a Connection and PreparedStatement with the first formal parameter. Then, it maps the second parameter to the SQL statement and executes the statement. This is the pattern for DML statements.
  • The myDQL() method has a signature with one formal parameter, which is the SQL query submitted as an actual parameter. It creates a Connection and PreparedStatement with the formal parameter. It returns a String, which is the return value for the last row fetched in the while-loop.

There is no main() method in the HelloWorld3.java class file. Including a main() method to test the program externally to the database would require changing the connection to a client-side or OCI driver. You can refer to Appendix D if you wish to build a test externally to the database instance.

Most likely, you have built the PLSQL schema, but if not, you should run the create_user.sql script now. When you have the PLSQL schema built, compile it with the javac utility as covered earlier in the chapter. Once compiled, you’ll load it into the Oracle JVM with the loadjava utility using the following:

loadjava -r -f -o -user plsql/plsql HelloWorld2.class

The loadjava utility command loads the Java HelloWorld3 class file into the Oracle JVM under the PLSQL schema. After loading the Java class file into the database, you’ll need to build a mytable table and PL/SQL wrapper to use it.

The mytable table is built by using the following command:

– Available online as part of HelloWorld3.sql file.
CREATE TABLE mytable (character VARCHAR2(100));

The following HelloWorld3.sql script builds the package and package body as a wrapper to the Java class library:

– Available online as part of HelloWorld3.sql file.

– Create a PL/SQL wrapper package to a Java class file.
CREATE OR REPLACE PACKAGE hello_world3 AS

  — Define a single argument procedure.
  PROCEDURE doDML
  ( dml VARCHAR2
  , input VARCHAR2 );

 
– Define a single argument function.
  FUNCTION doDQL
  ( dql   VARCHAR2 )
  RETURN VARCHAR2;

END hello_world3;
/

– Create a PL/SQL wrapper package to a Java class file.
CREATE OR REPLACE PACKAGE BODY hello_world3 AS

 
– Define a single argument procedure.
  PROCEDURE doDML
  ( dml   VARCHAR2
  , input VARCHAR2 ) IS
  LANGUAGE JAVA
  NAME ‘HelloWorld3.doDML(java.lang.String,java.lang.String)'; 

 – Define a single argument function.
  FUNCTION doDQL
  ( dql   VARCHAR2 )
  RETURN VARCHAR2 IS
 
LANGUAGE JAVA
  NAME ‘HelloWorld3.doDQL(java.lang.String) return String';

END hello_world3;
/

The script does the following:

  • It creates a package with one procedure and one function, which do the following:
    • The doDML procedure takes two formal parameters that are VARCHAR2 data types.

    • The doDQL function takes one formal parameter that is a VARCHAR2 and returns a VARCHAR2 data type.

  • It creates a package body with the procedure and function mapped to Java class files and methods. The PL/SQL NAME keyword provides a reference to the stored Java class file and the return value. You must fully qualify formal parameters by using the complete package path to the defined class, like the java.lang.String reference.

     

You can verify that all components are present to test by querying the user_ objects view with the following:

– Available online as part of HelloWorld3.sql file.
SELECT   object_name
,        object_type
,        status
FROM     user_objects
WHERE    object_name IN (‘HelloWorld3′,’HELLO_WORLD3′);

The script should output the following results:

– This output is generated from the online HelloWorld3.sql file.

OBJECT_NAME        OBJECT_TYPE    STATUS
—————–  ———–    ——-
HELLO_WORLD3       PACKAGE        VALID
HELLO_WORLD3       PACKAGE BODY   VALID
HelloWorld3        JAVA CLASS     VALID

If you did not get the same output, you’ll need to see what step you may have skipped. Please do this before attempting to proceed. If you did get the same output, you can now test the Java class library in SQL and PL/SQL. You can test it in SQL with a query or in PL/SQL with the DBMS_OUTPUT.PUT_LINE statement. The following illustrates a SQL query of the wrapper, which uses the internal Java class file:

SELECT  hello_world3.doDQL(‘SELECT character FROM mytable’)
FROM    dual;

The query returns the following results:

HELLO_WORLD3.DODQL(‘SELECTCHARACTERFROMMYTABLE’)
—————–
Bobby McGee

You’ve now covered how to build Oracle database instance-stored Java class files that map a Java method to a PL/SQL procedure. The next section discusses how to build real Java objects wrapped by PL/SQL object types.

{mospagebreak title=Building Internal Server Java Objects}

The Java programming language is Object-Oriented (OO). In the previous examples, Java stored objects were used as static functions. The potential to use Java to accomplish significant OO computing models lies in the Oracle object features introduced in Oracle 9i Release 2. Beginning with that release, you can construct instances of object types and use them as objects. After you develop an understanding of implementing stored Java objects in this section, you can see how PL/SQL objects work in Chapter 6.

Why Would I Use This?

We use internal server Java objects for the same reasons you use PL/SQL objects. Using Java as instantiable and threaded objects is where the value of using stored Java objects adds value once you understand how to use the SQLData interface. Java internal server objects have the ability to indirectly instantiate objects. The internal server Java objects are awkward to use for Java developers because they use external or indirect interfaces to effect communication.

This section illustrates how the SQLData interface is used and explains the concepts supporting it. We believe Java developers will find this feature useful but tricky to use at first. This section should help you jump-start your use of the feature.

Server-side stored Java programs support full runtime object behaviors starting with Oracle 9i, as noted earlier. This means you can now design, develop, and implement natural Java applications beneath PL/SQL object type wrappers. These Java classes can have instance methods, which mean nonstatic methods. You may also use static methods for libraries.

The balance of the differences covered earlier in the chapter still applies. You build Java object libraries by writing the Java class file and SQL object type definition. Object type bodies are not defined when the object type implementation is written in a stored Java object.

The substantial difference between external Java and server internal Java objects is the way you construct an instance of the class. You do not directly instantiate the class file and cannot use overriding constructors in the Java class file. The SQLData interface is the key to instantiating stored Java objects. It enables instantiating the Java class by passing back and forth the parameter values. This enables a class to return a reference to a copy or instance of the class.

TIP

There’s no way to instantiate directly a default constructor when using a stored Java object class. You also cannot use overriding constructors. The SQLData interface allows you to pass values to an instantiated class based on known class scope instance variables. Instance variables are not static variables. These limits are imposed by the implementation of the SQLData interface.

Implementing the SQLData interface is done by providing a variable definition and three concrete methods in your Java class file. The following are the components:

  • A String data type named sql_type.
  • A getSQLTypeName() method that returns a String data type.
  • A readSQL() method that takes two formal parameters and returns a void. One formal parameter is a SQLInput that contains a stream. The other is a string that contains a data type name.
  • A writeSQL() method that takes one formal parameter, which is a SQLOutput that contains a stream.

Details on implementing runtime Java classes will be illustrated in the following examples. The HelloWorld4 Java class file is designed to work as an instantiable Java stored object type body. The source code for the class is:

– Available online as part of HelloWorld4.java file.
// Oracle class imports.
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.oracore.*;
// Class definition.
public class HelloWorld4 implements SQLData {
 
// Define and initialize a private class name variable.
  private String className = new String(“HelloWorld4.class”);
 
// Define a formal parameter signature variable.
  private String instanceName;
  // Define a private schema qualified name value.
  private String qualifiedName;
 
// Define a class instance variable to support SQLData Interface.
  private String sql_type;
 
// ————————————–/
 
// Define default constructor.
  public HelloWorld4()
  {
   
// Define local String variables.
    String user = new String();
   
// Use a try-catch block because of SQL statement.
    try
    {
     
// Call a method of the inner class.
      user = getUserName();
   
}
    catch (Exception e) {}
   
// Set the class instance variable.
    qualifiedName = user + “.” + className;
 
} // End of default constructor.
  // ————————————–/
 
// Define a method to return a qualified name.
  public String getQualifiedName() throws SQLException
  { 
   
// Define and initialize a return variable.
    return this.qualifiedName + “.” + instanceName;
 
} // End of getQualifiedName() method.
 
// ————————————–/
 
// Define a method to return the database object name.
  public String getSQLTypeName() throws SQLException
  {
    // Returns the UDT map value or database object name.
    return sql_type;
 
} // End of getSQLTypeName() method.
  // ————————————–/
 
// Define getUserName() method to query the instance.
  public String getUserName() throws SQLException
  {
   
// Define and initialize a local return variable.
    String userName = new String();
   
// Define and initialize a query statement.
    String getDatabaseSQL = “SELECT user FROM dual”;
   
// Define a connection for Oracle.
    Connection conn = new OracleDriver().defaultConnection();
   
// Define and initialize a prepared statement.
    PreparedStatement ps = conn.prepareStatement(getDatabaseSQL);
   
// Execute a query.
    ResultSet rs = ps.executeQuery();
   
// Use a while-loop even though only one row is returned.
   
while (rs.next())
   
{
    // Assign the cursor return.
      
userName = rs.getString(1);
    }
   
// Return the user name.
   
return userName;
 
} // End of getUserName() method.
 
// ————————————–/
 
// Implements readSQL() method from the SQLData interface.
  public void readSQL(SQLInput stream, String typeName) throws SQLException
  {
    // Define sql_type to read input and signal overloading signatures.
   
sql_type = typeName;
   
// Pass values into the class.
    instanceName = stream.readString();
  } // End of readSQL() method.
  // ————————————-/   // Implements readSQL() method from the SQLData interface.
  public void writeSQL(SQLOutput stream) throws SQLException
  {
   
// You pass a value back by using a stream function.
   
// stream.writeString(‘variable_name’);
 
} // End of readSQL() method.
} // End of HelloWorld4 class.

The Java class file does the following:

  • It defines five key import statements. Make sure you use these as your starting point for stored Java object classes.
  • It defines four String data type class instance variables. The className variable is defined and initialized but the instanceName and qualifiedName variables are only defined. The sql_type variable is also only defined, but that’s how it should be done when using the SQLData interface. Also note that all four are private access level variables.
  • It defines a single class with a no argument constructor. This is typically called the default constructor. You’ll see why it’s important to note when you examine how to instantiate this object in PL/SQL. Within the default constructor, the qualifiedName variable is initialized. This is the only place where the variable can be assigned a value. It’s implemented this way to demonstrate that the default constructor is executed.
  • It defines five methods. Two are unique to the test class and three are required to implement the SQLData interface. They do the following:

    • The getQualifiedName() method returns the this.qualifiedName variable, which is an instance variable for the class. If you were to attempt to reference a class-level variable in a Java stored class supporting a PL/SQL function and procedure wrappers, it would fail. The loadjava would raise an exception to prevent putting it into the database instance.
    • The getSQLTypeName() method implements a method from the SQLData interface and assigns a user defined type to a local variable. This enables the class to be managed within the scope of the Oracle JVM.
    • The getUserName() method queries the database for the current user of the stored Java class.
    • The readSQL() method manages the incoming stream to the Java stored object class. The example uses one direct mapping to illustrate the differences between two instantiated classes during testing.
    • The writeSQL() method manages the outgoing stream from the Java stored object class. The example requires no direct mapping of the outgoing stream but the syntax is there for your convenience. This is done because all variables are defined with private access. Private access variables are encapsulated by design and are available only through published methods.

{mospagebreak title=More on Building Internal Server Java Objects}

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:

loadjava -r -f -o -user plsql/plsql HelloWorld4.java

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.

TIP

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:

  1. 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.
  2. It defines a single constructor function that takes no arguments. If you provide an overriding constructor, it will not be used.
  3. It defines two functions. One reads the instance qualifiedName value and another reads the SQLTypeName value.

NOTE

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 ALTER command 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;
/

You should see the following output displayed:

Item #1: [PLSQL.HelloWorld4.class.Adam]
Item #2: [PLSQL.HelloWorld4.class.Eve]
Item #3: [PLSQL.HELLO_WORLD4]
Item #4: [PLSQL.HELLO_WORLD4]
Item #5: [PLSQL.HELLO_WORLD4]

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.

{mospagebreak title=Troubleshooting Java Class Library Build, Load, Drop, and Use}

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%jdkbin C:> set CLASSPATH=%CLASSPATH%;C:%ORACLE_HOME%jdbclibclasses12.zip

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

%ORACLE_HOME%sqljlibtranslator.zip
%ORACLE_HOME%sqljlibruntime12.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:oracleora92lib;C:oracleora92jdbclib

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.

{mospagebreak title=Mapping Oracle Types}

Oracle maps all native types and User Defined Types (UDTs) to Java types. When you use SQLData you map individual components and structures. The following table notes how Oracle types map to Java types:

SQL Data Types

Java Class Data Types

CHAR

oracle.sql.CHAR

LONG

java.lang.String

VARCHAR2

java.lang.Byte

 

java.lang.Short

 

java.lang.Integer

 

java.lang.Long

 

java.lang.Float

 

java.lang.Double

 

java.lang.BigDecimal

 

java.sql.Date

 

java.sql.Time

 

java.sql.Timestamp

 

byte

 

short

 

int

 

long

 

float

 

double

DATE

oracle.sql.DATE java.lang.String java.sql.Date java.sql.Time java.sql.Timestamp

NUMBER

oracle.sql.NUMBER java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.lang.BigDecimal byte short

 

int

 

long float

 

double

OPAQUE

oracle.sql.OPAQUE

RAW LONG RAW

oracle.sql.RAW byte[]

ROWID

oracle.sql.CHAR oracle.sql.ROWID java.lang.String

BFILE

oracle.sql.BFILE

BLOB

oracle.sql.BLOB oracle.jdbc.Blob (JDK 1.1.x

CLOB NCLOB

oracle.sql.CLOB oracle.jdbc.Clob (JDK 1.1.x

OBJECT Object types

oracle.sql.STRUCT java.sql.Struct (JDK 1.1.x)

 

java.sql.SQLData

 

oracle.sql.ORAData

REF

oracle.sql.REF

Reference types

java.sql.Ref (JDK 1.1.x)

 

oracle.sql.ORAData

TABLE

oracle.sql.ARRAY

VARRAY

java.sql.Array (JDK 1.1.x)

Nested table and types

oracle.sql.ORAData

VARRAY types

 

Any of the preceding

oracle.sql.CustomDatum

SQL types

oracle.sql.Datum

Native types and UDTs can be used and managed by the SQLData conventions covered in the chapter. The Oracle JPublisher tool enables you to develop SQLData stubs and programs to use your UDTs.

Summary

You should now have an understanding of how to implement and troubleshoot server-side or internal Java class libraries. With these skills, you can build robust solutions in Java, affording you a certain simplicity over using PL/SQL.

[gp-comments width="770" linklove="off" ]

chat