HomeOracle Page 3 - Extending PL/SQL with Java Libraries
Oracle JDBC Connection Types - Oracle
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).
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.