Skip Headers

Oracle9i JDBC Developer's Guide and Reference
Release 2 (9.2)

Part Number A96654-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page


This chapter provides an overview of the Oracle implementation of JDBC, covering the following topics:


This section presents a brief introduction to Oracle JDBC, including a comparison to SQLJ.

What is JDBC?

JDBC (Java Database Connectivity) is a standard Java interface for connecting from Java to relational databases. The JDBC standard was defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers.

JDBC is based on the X/Open SQL Call Level Interface and complies with the SQL92 Entry Level standard.

In addition to supporting the standard JDBC API, Oracle drivers have extensions to support Oracle-specific datatypes and to enhance performance.

JDBC versus SQLJ

Developers who are familiar with the Oracle Call Interface (OCI) layer of client-side C code will recognize that JDBC provides the power and flexibility for the Java programmer that OCI does for the C or C++ programmer. Just as with OCI, you can use JDBC to query and update tables where, for example, the number and types of the columns are not known until runtime. This capability is called dynamic SQL. Therefore, JDBC is a way to use dynamic SQL statements in Java programs. Using JDBC, a calling program can construct SQL statements at runtime. Your JDBC program is compiled and run like any other Java program. No analysis or checking of the SQL statements is performed. Any errors that are made in your SQL code raise runtime errors. JDBC is designed as an API for dynamic SQL.

However, many applications do not need to construct SQL statements dynamically because the SQL statements they use are fixed or static. In this case, you can use SQLJ to embed static SQL in Java programs. In static SQL, all the SQL statements are complete or "textually evident" in the Java program. That is, details of the database object, such as the column names, number of columns in the table, and table name, are known before runtime. SQLJ offers advantages for these applications because it permits error checking at precompile time.

The precompile step of a SQLJ program performs syntax-checking of the embedded SQL, type checking against the database to assure that the data exchanged between Java and SQL have compatible types and proper type conversions, and schema checking to assure congruence between SQL constructs and the database schema. The result of the precompilation is Java source code with SQL runtime code which, in turn, can use JDBC calls. The generated Java code compiles and runs like any other Java program.

Although SQLJ provides direct support for static SQL operations known at the time the program is written, it can also interoperate with dynamic SQL through JDBC. SQLJ allows you to create JDBC objects when they are needed for dynamic SQL operations. In this way, SQLJ and JDBC can co-exist in the same program. Convenient conversions are supported between JDBC connections and SQLJ connection contexts, as well as between JDBC result sets and SQLJ iterators. For more information on this, see the Oracle9i SQLJ Developer's Guide and Reference.

The syntax and semantics of SQLJ and JDBC do not depend on the configuration under which they are running, thus enabling implementation on the client or database side or in the middle tier.

General Guidelines for Using JDBC and SQLJ

SQLJ is effective in the following circumstances:

JDBC is effective in the following circumstances:

Overview of the Oracle JDBC Drivers

This section introduces the Oracle JDBC drivers, their basic architecture, and some scenarios for their use. This information describes the core functionality of all JDBC drivers. However, there is special functionality for the OCI driver, which is described Chapter 17, "JDBC OCI Extensions".

Oracle provides the following JDBC drivers:

Figure 1-1 illustrates the driver-database architecture for the JDBC Thin, OCI, and server-side internal drivers.

The rest of this section describes common features of the Oracle drivers and then discusses each one individually, concluding with a discussion of some of the considerations in choosing the appropriate driver for your application.

Figure 1-1 Driver-Database Architecture

Text description of dbarch.gif follows
Text description of the illustration dbarch.gif

Common Features of Oracle JDBC Drivers

The server-side and client-side Oracle JDBC drivers provide the same basic functionality. They all support the following standards and features:

Oracle JDBC drivers implement standard Sun Microsystems java.sql interfaces. Through the oracle.jdbc package, you can access the Oracle features in addition to the Sun features. This package is equivalent to the oracle.jdbc.driver package which is deprecated for Oracle9i.

Table 1-1 shows how the client-side drivers compare.

Table 1-1 JDBC Client-Side Drivers Compared at a Glance  
Driver Type Size Protocol 100%
Use External Libraries Needed Platform-
Performance Completeness of Features





Applet and
















Most JDBC 2.0 functionality, including that for objects, arrays, and LOBs, is available in a JDK 1.1.x environment through Oracle extensions.

JDBC Thin Driver

The Oracle JDBC Thin driver is a 100% pure Java, Type IV driver. It is targeted for Oracle JDBC applets but can be used for applications as well. Because it is written entirely in Java, this driver is platform-independent. It does not require any additional Oracle software on the client side. The Thin driver communicates with the server using TTC, a protocol developed by Oracle to access the Oracle Relational Database Management System (RDBMS).

For applets it can be downloaded into a browser along with the Java applet being run. The HTTP protocol is stateless, but the Thin driver is not. The initial HTTP request to download the applet and the Thin driver is stateless. Once the Thin driver establishes the database connection, the communication between the browser and the database is stateful and in a two-tier configuration.

The JDBC Thin driver allows a direct connection to the database by providing an implementation of TCP/IP that emulates Oracle Net and TTC (the wire protocol used by OCI) on top of Java sockets. Both of these protocols are lightweight implementation versions of their counterparts on the server. The Oracle Net protocol runs over TCP/IP only.

The driver supports only TCP/IP protocol and requires a TNS listener on the TCP/IP sockets from the database server.


When the JDBC Thin driver is used with an applet, the client browser must have the capability to support Java sockets.

Using the Thin driver inside an Oracle server or middle tier is considered separately, under "JDBC Server-Side Thin Driver" below.


The JDBC OCI driver is a Type II driver for use with client-server Java applications. This driver requires an Oracle client installation, and therefore is Oracle platform-specific and not suitable for applets.


In Oracle9i, the OCI driver is a single OCI driver for use with all database versions. It replaces the distinct OCI8 and OCI7 drivers of previous releases. While the OCI8 and OCI7 drivers are deprecated for Oracle9i, they are still supported for backward compatibility.

The JDBC OCI driver provides OCI connection pooling functionality, which can either be part of the JDBC client or a JDBC stored procedure. OCI driver connection pooling requires fewer physical connections than standard connection pooling, it also provides a uniform interface, and allows you to dynamically configure the attributes of the connection pool. For a complete description of OCI driver connection pooling, see "OCI Driver Connection Pooling".

The OCI driver supports Oracle7, Oracle8/8i, and Oracle9i with the highest compatibility. It also supports all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and IPX/SPX.

The OCI driver, written in a combination of Java and C, converts JDBC invocations to calls to the Oracle Call Interface (OCI), using native methods to call C-entry points. These calls are then sent over Oracle Net to the Oracle database server. The OCI driver communicate with the server using the Oracle-developed TTC protocol.

The OCI driver uses the OCI libraries, C-entry points, Oracle Net, CORE libraries, and other necessary files on the client machine on which it is installed.

The Oracle Call Interface (OCI) is an application programming interface (API) that allows you to create applications that use the native procedures or function calls of a third-generation language to access an Oracle database server and control all phases of SQL statement execution. The OCI driver is designed to build scalable, multi-threaded applications that can support large numbers of users securely.

The Oracle9i JDBC OCI driver has the following functionality:

JDBC Server-Side Thin Driver

The Oracle JDBC server-side Thin driver offers the same functionality as the client-side Thin driver, but runs inside an Oracle database and accesses a remote database.

This is especially useful in two situations:

There is no difference in your code between using the Thin driver from a client application or from inside a server.


Statement cancel() and setQueryTimeout() methods are not supported by the server-side Thin driver.

About Permission for the Server-Side Thin Driver

The thin driver opens a socket to use for its connection. Because the Oracle server is enforcing the Java security model, this means that a check is performed for a SocketPermission object.

To use the JDBC server-side Thin driver, the connecting user must be granted with the appropriate permission. This is an example of how the permission can be granted for user SCOTT:

create role jdbcthin;
call dbms_java.grant_permission('JDBCTHIN',
'*', 'connect' );
grant jdbcthin to scott;

Note that JDBCTHIN in the grant_permission call must be in upper case. The '*' is a pattern. It is possible to limit the permission to allow connecting to specific machines or ports. See the Javadoc for complete details on the class. Also, refer to the Oracle9i Java Developer's Guide for further discussion of Java security inside the Oracle server.

JDBC Server-Side Internal Driver

The Oracle JDBC server-side internal driver supports any Java code that runs inside an Oracle database, such as in a Java stored procedures or Enterprise JavaBean, and must access the same database. This driver allows the Java virtual machine (JVM) to communicate directly with the SQL engine.

The server-side internal driver, the JVM, the database, KPRB (server-side) C library, and the SQL engine all run within the same address space, so the issue of network round trips is irrelevant. The programs access the SQL engine by using function calls.

The server-side internal driver is fully consistent with the client-side drivers and supports the same features and extensions. For more information on the server-side internal driver, see "JDBC in the Server: the Server-Side Internal Driver".


The server-side internal driver supports only JDK 1.2.x.

Choosing the Appropriate Driver

Consider the following when choosing a JDBC driver to use for your application or applet:

Overview of Application and Applet Functionality

This section compares and contrasts the basic functionality of JDBC applications and applets, and introduces Oracle extensions that can be used by application and applet programmers.

Application Basics

You can use either the Oracle JDBC Thin or OCI driver for a client application. Because the JDBC OCI driver uses native methods, there can be significant performance advantages in using this driver for your applications.

An application that can run on a client can also run in the Oracle server, using the JDBC server-side internal driver.

If you are using a JDBC OCI driver in an application, then the application will require an Oracle installation on its clients. For example, the application will require the installation of Oracle Net and client libraries.

The JDBC Thin and OCI drivers offer support for data encryption and integrity checksum features of the Oracle Advanced Security option (formerly known as ANO or ASO). See "JDBC Client-Side Security Features". Such security is not necessary for the server-side internal driver.

Applet Basics

This section describes the issues you should take into consideration if you are writing an applet that uses the JDBC Thin driver.

For more about applets and a discussion of relevant firewall, browser, and security issues, see "JDBC in Applets".

Applets and Security

Without special preparations, an applet can open network connections only to the host machine from which it was downloaded. Therefore, an applet can connect to databases only on the originating machine. If you want to connect to a database running on a different machine, you have two options:

Both of these topics are described in greater detail in "Connecting to the Database through the Applet".

The Thin driver offers support for data encryption and integrity checksum features of the Oracle Advanced Security option. See "JDBC Client-Side Security Features".

Applets and Firewalls

An applet that uses the JDBC Thin driver can connect to a database through a firewall. See "Using Applets with Firewalls" for more information on configuring the firewall and on writing connect strings for the applet.

Packaging and Deploying Applets

To package and deploy an applet, you must place the JDBC Thin driver classes and the applet classes in the same zip file. This is described in detail in "Packaging Applets".

Oracle Extensions

A number of Oracle extensions are available to Oracle JDBC application and applet programmers, in the following categories:

See Chapter 6, "Overview of Oracle Extensions" for an overview of type extensions and extended functionality, and succeeding chapters for further detail. See Chapter 12, "Performance Extensions" regarding Oracle performance enhancements.

Package oracle.jdbc

Beginning in Oracle9i, the Oracle extensions to JDBC are captured in the package oracle.jdbc. This package contains classes and interfaces that specify the Oracle extensions in a manner similar to the way the classes and interfaces in java.sql specify the public JDBC API.

Your code should use the package oracle.jdbc instead of the package oracle.jdbc.driver used in earlier versions of Oracle. Use of the package oracle.jdbc.driver is now deprecated, but will continue to be supported for backwards compatibility.

All that is required to convert your code is to replace "oracle.jdbc.driver" with "oracle.jdbc" in the source and recompile. This cannot be done piece-wise. You must convert all classes and interfaces that are referenced by an application. Conversion is not required, but is highly recommended. Future releases of Oracle may have features that are incompatible with use of the package oracle.jdbc.driver.

The purpose of this change is to enable the Oracle JDBC drivers to have multiple implementations. In all releases up to and including Oracle9i, all of the Oracle JDBC drivers have used the same top level implementation classes, the classes in the package oracle.jdbc.driver. By converting your code to use oracle.jdbc, you will be able to take advantage of future enhancements that use different implementation classes. There are no such enhancements in Oracle9i, but there are plans for such enhancements in the future.

Additionally, these interfaces permit the use of some code patterns that are difficult to use when your code uses the package oracle.jdbc.driver. For example, you can more easily develop wrapper classes for the Oracle JDBC classes. If you wished to wrap the OracleStatement class in order to log all SQL statements, you could easily do so by creating a class that wraps OracleStatment. That class would implement the interface oracle.jdbc.OracleStatement and hold an oracle.jdbc.OracleStatement as an instance variable. This wrapping pattern is much more difficult when your code uses the package oracle.jdbc.driver as you cannot extend the class oracle.jdbc.driver.OracleStatement.

Once again, your code should use the new package oracle.jdbc instead of the package oracle.jdbc.driver. Conversion is not required as oracle.jdbc.driver will continue to be supported for backwards compatibility. Conversion is highly recommended as there may in later releases be features that are not supported if your code uses oracle.jdbc.driver.

Server-Side Basics

By using the Oracle JDBC server-side internal driver, code that runs in an Oracle database, such as in Java stored procedures or Enterprise JavaBeans, can access the database in which it runs.

For a complete discussion of the server-side driver, see "JDBC in the Server: the Server-Side Internal Driver".

Session and Transaction Context

The server-side internal driver operates within a default session and default transaction context. For more information on default session and transaction context for the server-side driver, see "Session and Transaction Context for the Server-Side Internal Driver".

Connecting to the Database

The server-side internal driver uses a default connection to the database. You can connect to the database with either the DriverManager.getConnection() method or the Oracle-specific OracleDriver class defaultConnection() method. For more information on connecting to the database with the server-side driver, see "Connecting to the Database with the Server-Side Internal Driver".

Environments and Support

This section provides a brief discussion of platform, environment, and support features of the Oracle JDBC drivers. The following topics are discussed:

Supported JDK and JDBC Versions

Starting at Oracle8i release 8.1.6, Oracle has two versions of the Thin and OCI drivers--one that is compatible with versions JDK 1.2.x and higher, and one that is compatible with JDK 1.1.x. The JDK 1.2.x versions support standard JDBC 2.0. The JDK 1.1.x versions support most JDBC 2.0 features, but must do so through Oracle extensions because JDBC 2.0 features are not available in JDK 1.1.x versions.

Very little is required to migrate from a JDK 1.1.x environment to a JDK 1.2.x environment. For information, see "Migration from JDK 1.1.x to JDK 1.2.x".

  • The server-side internal driver supports only JDK 1.2.x.
  • Each driver implementation uses its own JDBC classes ZIP for JDK 1.4, 1.3.x, and1.2.x versions, and for JDK 1.1.x versions.

For information about supported combinations of driver versions, JDK versions, and database versions, see "Requirements and Compatibilities for Oracle JDBC Drivers".

JNI and Java Environments

Beginning with Oracle8i release 8.1.6, the Oracle JDBC OCI driver uses the standard JNI (Java Native Interface) to call Oracle OCI C libraries. Prior to 8.1.6, when the OCI drivers supported JDK 1.0.2, they used NMI (Native Method Interface) for C calls. NMI was an earlier specification by Sun Microsystems and was the only native call interface supported by JDK 1.0.2.

Because JNI is now supported by Oracle JDBC, you can use the OCI driver with Java virtual machines other than that of Sun Microsystems--in particular, with Microsoft and IBM JVMs. These JVMs support only JNI for native C calls.


The Oracle JDeveloper Suite provides developers with a single, integrated set of products to build, debug, and deploy component-based database applications for the Oracle Internet platform. The Oracle JDeveloper environment contains integrated support for JDBC and SQLJ, including the 100% pure JDBC Thin driver and the native OCI drivers. The database component of Oracle JDeveloper uses the JDBC drivers to manage the connection between the application running on the client and the server. See your Oracle JDeveloper documentation for more information.

Changes At This Release

Release 2 (9.2) of Oracle JDBC provides the following enhancements:

Desupport Of J2EE In The Oracle Database

With the introduction of Oracle9i Application Server Containers for J2EE (OC4J)--a new, lighter-weight, easier-to-use, faster, and certified J2EE container--Oracle will desupport the Java 2 Enterprise Edition (J2EE) and CORBA stacks from the database, starting with Oracle9i Database release 2. However, the database-embedded Java VM (Oracle JVM) will still be present and will continue to be enhanced to offer Java 2 Standard Edition (J2SE) features, Java stored procedures, JDBC, and SQLJ in the database.

As of Oracle9iDB Release 2 (version 9.2.0), Oracle will no longer support the following technologies in the database:

Customers will no longer be able to deploy servlets, JSP pages, EJBs and CORBA objects in Oracle databases . Oracle9i Release 1 (version 9.0.1) will be the last database release to support the J2EE and CORBA stack. Oracle is encouraging customers to migrate existing J2EE applications running in the database to OC4J now.

Go to previous page Go to next page
Copyright © 1999, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index