MacLochlainns Weblog

In this post, you build an internal server Java function by building a Java class file that will use the server-side internal connection or JDBC thick connection. As qualified earlier in the chapter, the JDBC thick connection depends on OCI libraries. All OCI libraries are directly accessible from your Java class file when you’ve loaded it into the Oracle JVM.

Java internal or server-side class files are built and accessed by a three-step process. You use Java to build and compile the class file. Then, you use the Oracle loadjava utility to load the compiled class file into the server. Once the class file is built and loaded into the server, you build a PL/SQL wrapper to the Java class library. Deployment requires that you load the compiled Java class file into the database, which you do with the loadjava utility.

Building a Java Library

To build a Java library for the Oracle Database server, the CLASSPATH must resolve to an ojdbcX.jar file (where X maps to the version for the Oracle Database Server). You can modify the earlier javaenv.bat file to include a CLASSPATH reference to the directory that contains the ojdbcX.jar file:

C:>copy con javaenv.bat
SET PATH=%ORACLE_HOME%\jdk\bin;%PATH%
SET CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc6.jar;.
^Z
1 file(s) copied.

You’ll get the following prompt when the javaenv.bat file already exists:

Overwrite javaenv.bat? (Yes/No/All): Yes

Naturally, you have the alternative of using any text editor of your choice. Several free or inexpensive editors are available, but we recommend Sublime as an inexpensive editor or Notepad++ as a free editor.

The Linux or Unix operating system would require the following changes for a javaenv.sh Bash shell environment:

export set PATH=%ORACLE_HOME%/jdk/bin:%PATH%
export set CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc6.jar:.

You need to source the javaenv.bat or javaenv.sh file before compiling the Java source file. You source the Windows batch file by simply running it from the command line, and you must use the dot (.) in the Bash or Korn shell or the keyword source in Tcsh.

Java Database Connectivity (JDBC) lets you build connections by using the DriverManager class. This is a change over the defaultConnection() method for internal Java class files and external connections. They now both use the getConnection() static method from the DriverManager class. The only difference between a thin client and a thick client is the actual parameter provided to the method. Examples in this chapter use the internal syntax, and examples in the appendix use the external thin client syntax.

The following assumes you have the correct CLASSPATH and PATH to use Java. If you are unable to compile or test the Java programs, it’s possible your environment is configured incorrectly.

// Oracle class imports.
import oracle.jdbc.driver.*;
 
// Class definition.
public class HelloWorld2 {
 
  public static String hello() {
    return "Hello World."; }
 
  public static String hello(String name) {
    return "Hello " + name + "."; }
 
  public static void main(String args[]) {
    System.out.println(HelloWorld2.hello());
    System.out.println(HelloWorld2.hello("Larry")); }
}

The program defines two overloaded hello methods. One takes no formal parameters and the other takes one. You can compile HelloWorld2.java with the following syntax:

javac HelloWorld2.java

After you compile and run this program with this syntax:

java HelloWorld2

the method without any formal parameters always prints

Hello World.

while the one that takes one formal parameter always prints

Hello Larry.

This happens because the static main() method always sends either no parameter or the same actual parameter to the dynamic method. As a rule, you want to remove testing components like the main() method before loading them into the database and pass actual parameters to dynamic methods. You can leave the static main() method in the program because it harms nothing and enables your testing of the program with the ojvmjava interactive utility.

Deploying a Java Library

You deploy a Java library with the loadjava utility, which is run from the operating system’s command-line interface. This step requires the HelloWorld2.class file from the prior section. You should note that sometimes problems can occur with the loadjava utility. If you run into problems with the loadjava utility, check the “Building, Loading, and Dropping Java Class Library Objects” below in this blog post.

Deploying a Java library also requires a demo schema, which may be a container database (CDB) or a pluggable database (PDB). If you’re unfamiliar with the difference between a CDB and PDB, the former refers to the base container and the latter are pluggable from one container to another.

After creating the demo schema in a PDB and compiling the Java class file, you load it into the Oracle JVM with the loadjava utility as follows:

loadjava -r -f -o -user video/video@video HelloWorld2.class

You can verify that the loadjava command worked with the following query:

SQL> SELECT object_name
  2  FROM user_objects
  3  WHERE object_name = 'HelloWorld2'
  4  AND object_type = object_type LIKE 'JAVA%';

It displays:

OBJECT_NAME
--------------
HelloWorld2

On the Microsoft platform, you may get a message that states “The procedure entry point kpuhhalo could not be located in the dynamic link library OCI.dll.” If you receive this error, it means you don’t have %ORACLE_HOME\bin% in your PATH environment variable.

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

Wrapping a Java Library

PL/SQL lets you wrap Java libraries. This section shows you how to wrap the previously created HelloWorld2 Java library, which differs from wrapping a PL/SQL stored program unit. The following HelloWorld2.sql script (included in the online resources file) builds the hello_world2 package specification and a package body. The package body serves as a wrapper to the two methods implemented in the HelloWorld2 Java class.

SQL> CREATE OR REPLACE PACKAGE hello_world2 AS
  2    FUNCTION hello
  3    RETURN VARCHAR2;
  4
  5    FUNCTION hello
  6    ( who VARCHAR2 )
  7    RETURN VARCHAR2;
  8  END hello_world2;
  9  /

The following package body builds wrapper methods in the HelloWorld2 Java class library:

SQL> CREATE OR REPLACE PACKAGE BODY hello_world2 AS
  2    FUNCTION hello
  3    RETURN VARCHAR2 IS
  4    LANGUAGE JAVA
  5    NAME 'HelloWorld2.hello() return java.lang.String';
  6
  7    FUNCTION hello
  8    ( who VARCHAR2 )
  9    RETURN VARCHAR2 IS
 10    LANGUAGE JAVA
 11    NAME 'HelloWorld2.hello(java.lang.String) return java.lang.String';
 12  END hello_world2;
 13  /

Line 5 and line 11 designate a lowercase hello method. It is critical that the case-sensitive string description in the PL/SQL wrapper exactly match the case-sensitive method names that are used in the Java code. You raise the following exception if they don’t:

SELECT hello_world2.hello() FROM dual
*
ERROR AT line 1:
ORA-29531: no method hello IN class HelloWorld2

When you ensure a case-sensitive match and run this in your schema, it creates a wrapper to the HelloWorld2.class file that you previously loaded. The return type of your PL/SQL wrapper is a VARCHAR2 data type. You map it to a java.lang.String class, and it must be that fully qualified path.

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

COLUMN object_name FORMAT A30
SQL> SELECT object_name
  2  ,      object_type
  3  ,      status
  4  FROM user_objects
  5  WHERE object_name IN ('HelloWorld2','HELLO_WORLD2')
  6  ORDER BY CASE
  7    WHEN object_type = 'JAVA CLASS' THEN 1
  8    WHEN object_type = 'PACKAGE' THEN 2
  9    ELSE 3
 10  END;

The query will return the following results:

Method Call
-----------------------------------
Hello Paul McCartney.

This section has shown you how to wrap and identify Java class libraries and related PL/SQL packages. Next you’ll see how to compile, deploy, and wrap a Java library.

Compiling, Deploying and Wrapping a Java Library

As an alternative to using the loadjava utility, you can also build the entire Java source, publishing specification, and implementation in a single SQL file. Then, you can run it directly from the SQL*Plus command line. This is done by using a single Data Definition Language (DDL)
command.

The prototype for the DDL command is

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED <java_class_name> AS <java_source>
/

You must use a forward slash shown in the prototype to execute a DDL command that builds an internal Java class. If you substitute a semicolon, you’ll raise an ORA-29536 exception.

The Java class file name uniquely identifies the Java class in the Oracle JVM’s CLASSPATH. You can treat a class with multiple methods like a package equivalent and define a package as a wrapper to complete the Java class. Alternatively, you can ignore the similarity to a package and implement individual Java methods as functions or procedures. Java methods that return a static value act like PL/SQL procedures, and Java methods that return a value act like PL/SQL functions.

Overloading doesn’t work when you implement a schema-level function or procedure for each Java method in a Java class. You must implement PL/SQL wrappers inside a package when you want to leverage object-oriented overloading in Oracle’s PL/SQL programming language.

You create the Java HelloWorldSQL class with the following DDL command:

SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
  2  // Class Definition.
  3  PUBLIC class HelloWorldSQL {
  4
  5    PUBLIC static String hello() {
  6    RETURN "Hello World."; }
  7
  8    PUBLIC static String hello(String name) {
  9    RETURN "Hello " + name + "."; }
 10  }
 11  /

RESOLVE JAVA SOURCE NAMED targets the class file name and parses and compiles the Java source into the instance. You can then publish individual methods as functions or procedures, or the class as a package.

The following publishes the class as a package specification:

SQL> CREATE OR REPLACE PACKAGE hello_world_sql AS
  2    FUNCTION hello
  3    RETURN VARCHAR2;
  4
  5    FUNCTION hello
  6    ( who VARCHAR2 )
  7    RETURN VARCHAR2;
  8  END hello_world_sql;
  9  /

And this wraps the class as a package body:

SQL> CREATE OR REPLACE PACKAGE BODY hello_world_sql AS
  2    FUNCTION hello
  3    RETURN VARCHAR2 IS
  4    LANGUAGE JAVA
  5    NAME 'HelloWorldSQL.hello() return String';
  6
  7    FUNCTION hello
  8    ( who VARCHAR2 )
  9    RETURN VARCHAR2 IS
 10    LANGUAGE JAVA
 11    NAME 'HelloWorldSQL.hello(java.lang.String) return String';
 12  END hello_world_sql;
 13  /

The package provides an overloaded hello function. You can call it with or without an actual parameter. You can then publish only a single method of the class as a function, like this:

SQL> CREATE OR REPLACE FUNCTION hello
  2  ( who VARCHAR2) RETURN VARCHAR2 IS
  3  LANGUAGE JAVA
  4  NAME 'HelloWorldSQL.hello(java.lang.String) return String';
  5  /

You can query the function by using

SQL> SELECT hello('Nathan') AS SALUTATION;

and it returns

SALUTATION
-----------------------------------
Hello Nathan.

You have now covered how to build Oracle database instance-stored Java class files that map
methods to functions. The next blog post will examine how you build components to deliver
procedure DML behaviors.