Extending PL/SQL with Java Libraries

If you are looking for a way to extend stored programs with Java, look no further. This article, the first 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).

Extending stored programs with Java is a very popular solution. PL/SQL is essential to the process of stored Java class libraries. PL/SQL wraps access to the Java class libraries, which means PL/SQL becomes the gate through which data moves to and from Java stored objects.

This chapter stands independent of others in the book since all its content works in currently supported versions of the Oracle database. We’ll be covering the following topics:

  • Java architecture in Oracle
  • Oracle JDBC connection types

    • Client-side driver, or JDBC thin driver

    • Oracle call interface driver, or middle-tier thick driver

    • Oracle server-side internal driver, or server-tier thick driver

  • Building Java class libraries in Oracle

    • Building internal server Java functions

    • Building internal server Java procedures

    • Building internal server Java objects

    • Troubleshooting Java class library build, load, drop, and use

  • Mapping Oracle types

This chapter introduces you to the big picture of thehows small boxes ne Oracle Java architecture. After explaining the architecture, you’ll see how to develop and extend Java components as application programming components.

Why Would I Use This?

We’ll use PL/SQL to interface between stored Java class libraries and other PL/SQL stored and anonymous block programs. We’ll also map native and user-defined data types between Oracle and Java to write effective interfaces and support external Java Server Pages (JSPs).

{mospagebreak title=Java Architecture in Oracle}

The Oracle 9i and 10g databases provide a robust architecture for developing server-side or internal Java programming components. Java components are Object-Oriented (OO) structures that fit naturally into Oracle’s Object-Relational model. The component architecture is a library stack that contains

  1. Platform-dependent operating systems, like UNIX, LINUX, and Microsoft Windows
  2. Platform-dependent Oracle database management files and libraries
  3. Oracle database instance Java Virtual Machine, which is platform independent
  4. Java core class libraries, which are ported to various platforms
  5. Oracle-supported Java Application Programming Interfaces (APIs), like SQLJ, JDBC, and JNDI
  6. Oracle PL/SQL stored objects, which provide an interface between SQL and PL/SQL programs, as well as server-side Java classes

The Oracle and Java libraries store and manage application programs like a ubiquitous file system. Together they mask the structures and limitations of operating systems. Oracle libraries make storing, retrieving, and recovering files a standard process across many diverse platforms. The Java Virtual Machine (JVM) provides a standard environment where you can build well-documented OO programs. Oracle PL/SQL enables the development of wrapper packages to access Java libraries from other PL/SQL stored objects and SQL.

The architecture of the Oracle JVM is shown in the following illustration:

Oracle JVM uses two types of namespaces, the long name and short name. The long name is exactly as the class is named in Java. You can call stored Java programs by their native namespace. While the chapter examples are short and not placed into packages, you’ll most likely put your Java programs into packages. The namespace for Java stored code includes the entire package hierarchy. When this is larger than 30 characters, Oracle uses a hashed namespace in the data dictionary views. Use the DBMS_JAVA package and LONGNAME function to get the full namespace. You can also use the DBMS_JAVA package and SHORTNAME function to get the short name.

The JVM enjoys automated storage management, which means you do not need to allocate and free memory explicitly. Also, Java is a strongly typed programming language like PL/SQL. The combination of strong typing and a garbage collector to manage memory provides a scalable and simplified environment like the PL/SQL runtime engine.

Both Java and PL/SQL are interpreted languages and they require Just-In-Time (JIT) compilation. Oracle 9i introduces native compilation for PL/SQL and Java programs. Native compilation enables Ahead-of-Time compilation. It changes PL/SQL and Java byte code into machine-executable programming code.

Native compilation speeds execution by eliminating JIT compilation delay. Unfortunately, it takes time to compile the interpreted languages programs into machine code. If you rarely change your code, the trade-off may be worth using native compilation.

There are three ways to put Oracle into the database instance. Your options are

  1. A two-step process: (a) compiling the Java source file, <file_name> .java, with the javac executable to generate a Java byte code program, and (b) using the Oracle loadjava utility to put the file into the database instance.
  2. A one-step process using the loadjava utility to compile and put the Java class file into the database instance.
  3. A one-step process using Data Definition Language to build and compile the Java source file as a stored Java class.

There are occasionally parser problems in Oracle 9i R1 and using DDL commands to build the Java program can fail. These are fixed in 9i R2 and later versions. Java source files will be compiled and then loaded into the database instance with the loadjava utility in all examples.


If you opt to use the one-steploadjava utility, please note you may encounter an ORA-29533 error for attempting to overwrite the file.The replace option in theloadjava utility does not work in some releases. Use dropjava with the –user option and the <file_name>.class before rerunning theloadjava utility.  

This chapter assumes you have a basic familiarity with Java. Basic familiarity means that you can compile and run Java programs. Sample command-line instructions are provided in the chapter examples, but Appendix D provides a basic tutorial as well as Java environment configuration instructions.

Java stored program units are like traditional PL/SQL program units. They are called with either definer’s-rights or invoker’s-rights access from a single session. There are differences between how Java works externally and internally within the Oracle database instance. The differences are qualified in the following:

  • Execution control differs substantially from native Java. External to the Oracle instance, Java applications contain a main() method, which is invoked to run the program. Java programs internal to the instance do not contain a main() method. Java programs stored in an Oracle instance have two types of behaviors. They are

    • Java stored programs that serve as packages with functions and procedures are not instantiable classes. All variables and methods for these programs must be static, which means they act as class methods. This fixes their scope as mirrors of PL/SQL packages with functions and procedures. The coding syntax changes as does their accessibility to external Java applications.

    • Java stored programs that serve as object type body implementations can be instantiable classes. Variables and methods may be static and nonstatic. Unlike external Java classes, they cannot have overriding constructors, only a single default constructor. They are instantiated by implementing the SQLData interface defined in the JDBC2 API, and instantiation involves mapping data types between PL/SQL and Java.

  • Java classes are stored in clear text, Java byte code, and compressed Java archives externally to the Oracle database instance. Oracle manages these as source, class, and resource Java objects. Schemas contain a JAVA$OPTIONS table, which can be accessed and configured by the DBMS_JAVA package, the SET_COMPILER_OPTION and RESET_ COMPILER_OPTION procedures, or the GET_COMPILER_OPTION function.
  • User interfaces are not supported for internal Java class files. This means there’s no way to directly display to console or native sound device(s). Sound files can be manipulated from within Oracle, but they do not have access to the native sound devices. Oracle 10g differs slightly from Oracle 9i because it uses Java SDK 1.4.x, which supports Headless AWT.
  • Internal Oracle Java class names are maintained in two forms. One is the short form that supports standard schema database objects and is limited to 30 characters. When a fully qualified package and class name exceeds the limit, Oracle automatically creates a hashed name as the class short name and stores the long name elsewhere.
  • The standard Java Class.forName() isn’t supported for internal Oracle Java classes. Oracle 9i and Oracle 10g support the use of multiple resolvers, which locate classes. You can get unexpected results from a search expecting one resolver that runs another.
  • Operating resources are restricted. You can only alter these as the privileged user SYSDBA. Use the DBMS_JAVA package and GRANT_PERMISSION procedure to open operating resources like file IO.
  • Java threading works differently for Oracle internal Java classes. The Oracle JVM uses a nonpreemptive threading model. This means that all threads run in a single operating system thread and the Oracle JVM merely switches contexts between threads. Switching context means that the Oracle JVM spawns one thread for a time slice and then another, in a round-robin fashion, until all threads complete.


The version of Oracle that you’re using does dictate which version of the Java Software Development Kit you should use. For simplification, all examples were done using Java SDK 1.3.x, supported in Oracle 9i. They also work in Java SDK 1.4.x.  

The Oracle Java Developer’s Guide lists two key error codes, but there are a number of others. Rather than list these error codes, we’ve included a troubleshooting section later in the chapter titled “Troubleshooting Java Class Library Build, Load, Drop, and Use.”

Now that you’ve reviewed the key components of the Oracle Java architecture, in the next section we’ll introduce you to the various JDBC drivers.

{mospagebreak title=Oracle JDBC Connection Types}

Oracle implements the Java Database Connection (JDBC) in three ways in order to meet three different needs. These are the thin, thick, and default connections. Respectively, they map to the client-side driver, the call interface driver (or middle-tier driver), and the server-side (or internal) driver. In the next sections, you’ll examine all three.

The Client-Side Driver, or JDBC Thin Driver

The Oracle thin connection is probably the most used by Java applications, Java Server Pages (JSPs), and Enterprise Java Beans (EJBs). It provides many advantages to building code without directly accessing Oracle library files.

The advantages of the Oracle JDBC thin driver are numerous for external Java applications because it requires the least setup and configuration. First though, make sure your Java programming environment has access to the standard Java library and the Oracle JDBC library. You can set this up by configuring your CLASSPATH environment variable, which should include the Oracle classes12.zip Java archive file. You can find details about how to set these in Appendix D at the back of the book.

Unfortunately, you can’t use the Oracle thin JDBC driver unless you’ve configured and started your database listener. You’ll likewise need to provide the host name, listener port number, database name, and your user ID and password each time you spawn a connection to the database instance.

Why Would I Use This?

You need to know what your options are when connecting Java programs to the Oracle database. When you know how they work, you’re in a better position to select the correct JDBC driver to then connect your programs to the Oracle instance.

We find Java a very useful extension to the Oracle programming stack. Unfortunately, we also find it is critical to understand the nuances of your choices before matching a technology to a problem. We believe if you
understand your Java options, you will make better choices of how to leverage Java in your applications.


The Oracle client-side or thin driver returns a rather meaningless error message if the host name, listener port number, or database name is incorrect. In fact, it will report a 17002 error. This error is found in Oracle’s implementation of the JDBC API. Appendix D demonstrates a clean mechanism to audit for the error.

The uses of the Oracle JDBC thin driver are limited to external Java applications, JSPs, and EJBs. A multithreaded Java servlet is an example of a Java application that would implement an Oracle JDBC thin driver file. Oracle JDBC thin connections can be optimistic or pessimistic connections.

Optimistic connections are temporary connections transmitted using the Hypertext Transfer Protocol (HTTP), which are limited to a 15-second pipelined TCP socket connection. These are ideal for JSPs but resource-expensive because they must establish a connection for each communication.

Pessimistic connections are typically transmitted using a state-aware TCP socket that’s open through the duration of the connection. Pessimistic connections are used by multithreaded Java servlets to create and maintain database connection pools. Java servlets can be implemented in two-tier or n-tier solutions, and avoid resource-expensive short-lived connections and disconnections across HTTP.

The Oracle Call Interface Driver, or Middle-Tier Thick Driver

The Oracle call interface (OCI) driver is more tightly coupled with the Oracle C/C++ libraries than the Oracle JDBC thin driver. If you use the Oracle JDBC call interface (or middle-tier thick) driver, you’ll need to ensure that the PATH, CLASSPATH, and LD_LIBRARY_PATH environment variables map to Oracle libraries. The libraries need to be on the same physical platform or map through a storage area network (SAN), like NFS in UNIX.

The OCI driver can maintain persistent connection pools through Java servlets. The performance of the OCI driver is often slower than the Oracle JDBC thin driver. As a rule, you’ll have an easier configuration if you use the Oracle JDBC thin driver in your servlet. Also, you won’t suffer performance degradation if you maintain active connection pools in your Java servlet.

The Oracle Server-Side Internal Driver, or Server-Tier Thick Driver

The Oracle server-side internal driver is likewise tightly coupled with, and dependent on, the Oracle C/C++ libraries. Unfortunately, there’s no other choice available to build Java programs as stored objects in the Oracle database.

The Oracle server-side internal driver uses the defaultConnection() method of the Connection class to connect to the database. This poses a bit of a testing problem if you want to test the Java program externally. It’s best if you test the Java code in your development instance and avoid building a work-around.

Unlike the OCI driver, the server-side internal drive is faster than the Oracle JDBC thin driver. As you read the chapter and examine the code, you’ll find that embedding Java in the Oracle database requires a few tricks and techniques.

The next section examines how to build and troubleshoot class libraries and instantiable Java stored objects.

{mospagebreak title=Building Java Class Libraries in Oracle}

When you choose to build Java class libraries, you have two deployment choices. You can build call-interface driven (middle-tier) or server-side Java class libraries.

Call-interface libraries act like server-side includes to your Apache server. They must be replicated to all nodes of your Apache server and are managed within the structure of your web server load-balancing tool. These components act like external programs that call into the Oracle server and are better treated in Enterprise Java books.

Why Would I Use This?

You need to know when a Java class belongs as an internal or external library. We find Java libraries deployed internally in the database have well-defined but narrow uses. Likewise, we find external libraries to be powerful components but less convenient than locally stored objects. You will need to understand these two technologies to make good deployment choices.



While call-interface driver or middle-tier Java class libraries are not directly covered, they do require direct reference in their path to the Oracle OCI libraries. The OCI libraries are in the Oracle Application Server but not on other web servers.

Server-side Java class libraries are stored objects within the Oracle JVM, which is a subcomponent of the Oracle database. Server-side Java class libraries are the core theme of this chapter. In the next two sections, you’ll learn how to build internal server Java functions and procedures.


If you’re unfamiliar with configuring and testing a Java JDBC connection, please check Appendix D for instructions.

Java programming ranges from simple to complex, but these examples should be straightforward. You have two core executables to run Java programs, which you’ll use in the examples. They are

  • javac   Compiles your text file Java programs into Java byte code
  • java   Runs your compiled Java byte code programs

The file-naming convention in Java is case-sensitive so you should ensure you name files consistent with the web-based code example files. If you attempt to compile a Java file when the file name and class name are different, you’ll receive an error. Also, the file extension for Java programs is a lowercase .java.

You’ll now build a simple HelloWorld1.java file to make sure the balance of the examples works. If you’re working in Microsoft Windows, please open a Command Prompt Window. If you’re working in UNIX, please use a terminal window. The following is the code for HelloWorld.java:

— Available online as part of HelloWorld1.java file.
// Class definition.
public class HelloWorld1
  // ————————————–/
  // Static main to print Hello World. 
  public static void main(String args[])
// Print the message to console.
    System.out.println(“Hello World.”);
  } // End of static main.
  // ————————————–/
} // End of HelloWorld1 class.

Java text files are compiled by the following syntax:

javac HelloWorld1.java

Successful compilation does not return anything to the console. The lack of any message is a good thing. The way to verify whether or not you have a Java byte code file is to run the Microsoft Windows directory (dir) command or UNIX list (ls) command for files matching HelloWorld1.* in the present working directory. You should see two files displayed to the console:


After building the Java byte code compiled program, you can test its execution by doing the following:

java HelloWorld1


You do not provide the .class extension when running Java programs because it is assumed. Appending .class to the file name will raise the following exception: java.lang.NoClassDefFoundError: HelloWorld1/class.


You can also raise the java.lang .NoClassDefFoundError: HelloWorld1/class error if you do not have your present working directory in your PATH and CLASSPATH environment variables.

You’ll receive the following results:

Hello World.

The next section covers how you build server-side or internal server Java programming units. You’ll learn how to build Java class files to support stored functions and procedures and how to wrap their existence in PL/SQL packages. The following two sections are sequential and the second section assumes you have worked through the first.

{mospagebreak title=Building Internal Server Java Functions}

You build an internal server Java function by building a Java class file that will use the server-side internal or JDBC thick connection. As qualified earlier in the chapter, the JDBC thick connection depends on Oracle Call Interface (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 built and loaded into the server, you build a PL/SQL wrapper to the Java class library.

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. As mentioned earlier, you should use Appendix D to ensure you have correctly configured your environment.

The example builds a Java class library with two methods. These methods are overloaded, which means they have different signatures or formal parameter lists. They each return a variable length character array or Java string. Both of the overloaded methods will map to two overloaded PL/SQL functions that return VARCHAR2 native Oracle data types. The code for HelloWorld2.java follows:

Why Would I Use This?

We use internal server Java functions for the same reasons we write PL/SQL functions, which is to process and return a result that does not involve Data Manipulation Language (DML) commands. Java functions have the ability to mirror PL/SQL functions 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.


— Available online as part of HelloWorld2.java file.
// Oracle class imports.
import oracle.jdbc.driver.*;
/ Class definition.
public class HelloWorld2
// ————————————–/
  // The Hello method.
  public static String hello()
// Call overloaded method with a null String.
    return “Hello World.”;
  } // End of hello() method.
// ————————————–/   // The hello method.
  public static String hello(String name)
// Call overloaded method with a null String.
    return “Hello ” + name + “.”;
} // End of hello() method.
// ————————————–/
// Static main to test instance outside of Oracle.
  public static void main(String args[])
// Print the return message to console.
// Print the return message to console.
} // End of static main.
  // ————————————–/
} // End of HelloWorld2 class.

The program does the following:

  • It defines a single class with two Hello() class methods. The methods are overloaded, which means they have different signatures or formal parameter lists. They are both static methods because only static methods can be referenced by a PL/SQL wrapper package. Static methods do not require an instance of the class to run. They function much like a function in C or a PL/SQL stored package function.
  • It defines a main() method, which you can use to test the program before loading it into the Oracle database instance. The main() method will be ignored when the class file is loaded into the Oracle instance. In the main method, both static Hello() and Hello(String name) methods are called and the result passed as an actual parameter to the console.

As a rule, you want to remove testing components like the main() method before loading them into the database. If they are left, they have no effect on the stored Java class library.

Use the following syntax to test the program with the Java utility:

java HelloWorld2

The following output will be displayed to the console:

Hello World.
Hello Larry.

If you have not built the PLSQL schema, please 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 as follows:

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


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_HOMEbin% in your PATH environment variable.

The loadjava utility command loads the Java HelloWorld2 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 PL/SQL wrapper to use it. The following HelloWorld2.sql script builds the package and package body as a wrapper to the Java class library:

— Available online as part of HelloWorld2.sql file.
— Create a PL/SQL wrapper package to a Java class file.
  — Define a null argument function.
  FUNCTION hello
— Define a one argument function.
  FUNCTION hello
  ( who VARCHAR2 )
END hello_world2;
— Create a PL/SQL wrapper package to a Java class file.
— Define a null argument function.
  FUNCTION hello
  NAME ‘HelloWorld2.Hello() return String’;

  — Define a null argument function.
  FUNCTION hello
  ( who VARCHAR2 )
  NAME ‘HelloWorld2.Hello(java.lang.String) return String’;
END hello_world2;

The script does the following:

  • It creates a package with two overloaded Hello functions that return VARCHAR2 data types. One is a null argument signature and the other has one formal parameter.
  • It creates a package body with two overloaded Hello functions that implement a stored Java class file. 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. The return type can be shortened to String because Oracle understands it as the external data type.

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

— Available online as part of HelloWorld2.sql file.
SELECT   object_name
,        object_type
,        status
FROM     user_objects
WHERE    object_name IN (‘HelloWorld2′,’HELLO_WORLD2’);


The script should output the following results:

— This output is generated from the online HelloWorld2.sql file.
——————–    ————  ——
HELLO_WORLD2            PACKAGE       VALID HELLO_WORLD2            PACKAGE BODY  VALID HelloWorld2             JAVA CLASS    VALID

If you did not get the same output, you’ll need to see what step you may have skipped. Please do that 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_world2.hello(‘Paul McCartney’) FROM    dual;

The query will return the following results:

Hello Paul McCartney.

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

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

chat sex hikayeleri Ensest hikaye