|Oracle® Database JDBC Developer's Guide and Reference
10g Release 1 (10.1)
Part Number B10979-02
This chapter provides an overview of the Oracle implementation of JDBC, covering the following topics:
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.
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 19, "JDBC OCI Extensions".
Oracle provides the following JDBC drivers:
Thin driver, a 100% Java driver for client-side use without an Oracle installation; can be used with applets and applications
OCI driver for client-side use with an Oracle client installation; can be used with applications
server-side Thin driver, which is functionally the same as the client-side Thin driver, but is for code that runs inside an Oracle server and needs to access another session either on the same server or a remote server, including middle-tier scenarios
server-side internal driver for code that runs inside an Oracle server and accesses the same session (that is, inside the Oracle session that it must access)
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
The Thin and OCI drivers support the following JDKs: 1.2.x, 1.3.x and 1.4.x. The server side Thin driver and server side internal driver support JDK 1.4.1. All the JDBC drivers support the following standards and features:
same syntax and APIs
same Oracle extensions
full support for multi-threaded applications
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.
The Oracle JDBC Thin driver is a 100% pure Java, Type IV driver that can be used in applications and applets. 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).
The JDBC Thin driver allows a direct connection to the database by providing an implementation of SQL*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 Thin driver runs over TCP/IP only.
The driver supports only the TCP/IP protocol and requires a TNS listener on the TCP/IP sockets from the database server.
Note:When the JDBC Thin driver is used with an applet, the client browser must have the capability to support Java sockets.
For applets, the Thin driver 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.
Using the Thin driver inside an Oracle server is considered separately, under "JDBC Server-Side Thin Driver" below.
The OCI driver 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 communicates 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. At this release, the OCI driver supports Instant Client deployment; see Chapter 20, "OCI Instant Client" for details.
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 JDBC OCI driver has the following functionality:
OCI optimized fetch
Client-side object cache
Transparent Application Failover (TAF)
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 or a different session on the same database.
This is especially useful in two situations:
to access a remote Oracle server from an Oracle server acting as a middle tier
more generally, to access one Oracle server from inside another, such as from a Java stored procedure
There is no difference in your code between using the Thin driver from a client application or from inside a server.
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
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
create role jdbcthin; call dbms_java.grant_permission('JDBCTHIN', 'java.net.SocketPermission', '*', 'connect' ); grant jdbcthin to scott;
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
java.net.SocketPermission class. Also, refer to the Oracle Database Java Developer's Guide for further discussion of Java security inside the Oracle server.
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, 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 Chapter 26, "Server-Side Internal Driver".
In general, unless you need OCI-specific features such as support for non-TCP/IP networks, use the Thin driver. At 10g Release 1 (10.1), the Thin driver has excellent performance and functionality.
If you want maximum portability and performance, use the JDBC Thin driver. You can connect to an Oracle server from either an application or an applet using the JDBC Thin driver.
If you want to use LDAP over SSL, use the Thin driver. See Table 3-3, "Supported Database Specifiers" for details.
If you are writing a client application for an Oracle client environment and need OCI-driver-specific features, such as support for non-TCP/IP networks, then choose the JDBC OCI driver.
If you are writing an applet, you must use the Thin driver.
For code that runs in an Oracle server acting as a middle tier, use the server-side Thin driver.
If your code will run inside the target Oracle server, then use the JDBC server-side internal driver to access that server. (You can also access remote servers using the server-side Thin driver.)
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.
For more about applets and a discussion of relevant firewall, browser, and security issues, see Chapter 24, "JDBC in Applets".
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".
Your applet can take advantage of the data encryption and integrity checksum features of the Oracle Advanced Security option. See Chapter 23, "JDBC Client-Side Security Features".
An applet 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.
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".
A number of Oracle extensions are available to Oracle JDBC application and applet programmers, in the following categories:
type extensions (such as ROWIDs and REF CURSOR types)
wrapper classes for SQL types (the
support for custom Java classes to map to user-defined types
extended LOB support
extended connection, statement, and result set functionality
See Chapter 10, "Oracle Extensions" for an overview of type extensions and extended functionality, and succeeding chapters for further detail. See Chapter 22, "Performance Extensions" regarding Oracle performance enhancements.
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 Chapter 26, "Server-Side Internal Driver".
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".
The server-side internal driver uses a default connection to the database. You connect to the database with the
OracleDataSource.getconnection() 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".
This section provides a brief discussion of platform, environment, and support features of the Oracle JDBC drivers. The following topics are discussed:
Starting at 10g Release 1 (10.1), all the JDBC drivers are compatible with JDK 1.2.x and higher; the
nls_charset11.zip files are no longer provided.
The 10g Release 1 (10.1) JDBC drivers are certified to work with 10.0.x, 9.2.x, 9.0.1.x, and 8.1.7.x database releases.
The 9.2 Oracle JDBC drivers are certified to work with 9.2.x, 9.0.1.x, and 8.1.7 database releases.
The 9.2 Oracle JDBC drivers are not certified to work with older, unsupported database releases, such as 8.0.x and 7.x.
Existing supported JDBC drivers (Oracle8i 126.96.36.199 and Oracle9i JDBC drivers) are certified to work against Oracle Database 10g; known limitations will be documented.
Note:You can find a complete up-to-date list of supported databases at
The Oracle JDBC OCI driver uses the standard JNI (Java Native Interface) to call Oracle OCI C libraries. You can use the OCI driver with Java virtual machines other than that of Sun Microsystems—in particular, with Microsoft and IBM JVMs.
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, 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.
10g Release 1 (10.1) of Oracle JDBC provides many enhancements. This section gives an overview of those enhancements. It is divided into the following sections:
Support for the Oracle datatypes
TIMESTAMP WITH TIME ZONE, and
TIMESTAMP WITH LOCAL TIME ZONE. See Table 25-1, "Valid SQL Datatype-Java Class Mappings".
A new statement cache API; the old API is now deprecated. See Chapter 6, "Statement Caching".
Improved Performance in the JDBC Drivers. The JDBC Thin, OCI, and server-side internal drivers have been completely restructured to improve performance.
Compliance with the JDBC3.0 and J2EE 1.3 standards. See Chapter 5, "JDBC Standards Support".
Support for Oracle 10g database features, including support for EEE double, VARRAY enhancements, INTERVAL-DAY-TO-SECOND, LONG-to-LOB conversion, UNLIMITED LSIZE LOBs and native IEEE float.
Improved Connection Caching. The Implicit Connection Cache is an improved JDBC3.0-compliant connection cache implementation for
DataSource. Java and J2EE applications now benefit from transparent access to the cache, support for multiple users, and the ability to request connections based on user-defined profiles. See Chapter 7, "Implicit Connection Caching".
Updated Globalization Support. A new globalization file,
orai18n.jar, supersedes the old
nls_charset files. See Chapter 12, "Globalization Support".
Named SQL Parameter Support.
CallableStatement now support referring to SQL parameters by name as well as by numeric position. See "Interface oracle.jdbc.OracleCallableStatement" and "Interface oracle.jdbc.OraclePreparedStatement" .
Two New Encryption Algorithms. The JDBC Thin driver now supports
3DES168 as values for the connection property
SQLNET.ENCRYPTION_TYPES_CLIENT in the JDBC Thin driver.
Thin Driver PL/SQL Index Table. You can now send and receive PL/SQL tables using the Thin driver. For example, you can exchange Java collections with PL/SQL collections. The Thin driver also supports index-by table parameters.
Instant Client For JDBC-OCI Driver. The JDBC drivers now support Easy Instant Client for OCI. See Chapter 20, "OCI Instant Client" for details.
String Length Increased in LONG Columns.
OraclePreparedStatement.setString() now accepts
Strings up to 32766 characters long and can insert these
LONG columns. If you specify a longer string, an
ORA-17157 error is thrown.
Two new JAR files,
ojdbc14dms_g.jar, have been added to the release. If your application uses JDBC1.4 features and DMS, you must add one of these files to your
ojdbc14dms.jar if you use both JDK1.4 and DMS; use
jdbc14dms_g.jar if you use JDK1.4 and DMS and need debugging features.
Note:These two JAR files are only available as part of Oracle Application Server 10g.
ojdbc14_g.jar and ojdbc14dms_g.jar now use java.util.logging instead of OracleLog.
Fast connection failover. This High Availability feature supports rapid detection and restarting of failed connections to a RAC database in the JDBC connection cache. When this feature is enabled, JDBC subscribes to RAC event notifications for instance and host failures. Upon receiving these events, JDBC processes the connection cache to remove invalid connections and replace them as necessary. See Chapter 8, "Fast Connection Failover".
End-to-end metric support. JDBC now supports the
Module metrics. See Chapter 21, "End-To-End Metrics Support".
Full support for binary_float and binary_double as JSP parameters and in the server-side internal driver.
Support for proxy connections to the database. The
oracle.jdbc.OracleConnection class now supports the methods
openProxySession(), to create a proxy session, and
isProxySession(), which returns
true if the current session is a proxy session,
Native XA support. The Thin driver has a high-performance native XA implementation, which is the implementation used by default.
Oracle.jdbc.xa.OracleXADatasource has the methods
setNativeXA(true) to use the native XA implementation (this is the default); call
setNativeXA(false) to use the older, generic XA implementation. The
getNativeXA() method returns
true if the native implementation is in use,
Support for checking PL/SQL compiler warnings. The OCI and Thin drivers now support fetching and checking PL/SQL compiler warnings, enabling and disabling these warnings, and specifying which categories of warnings to receive.
The class oracle.jdbc.OracleConnection has a new method, setPlsqlWarnings(), which allows users to enable and disable all or some categories of warnings. This method takes a
String argument which specifies warning settings. When there are PL/SQL compiler warnings, JDBC automatically generates
SQLWarning exceptions; if a
SQLWarning has the error code 24439, there are compiler warnings available to check. See the Javadoc for further information.
Server-side Internal Driver support for JDBC3.0. The server-side internal driver provides JDBC 3.0 support similar to that provided by
Support for the JDBC3.0 class WebRowSet. See Chapter 18, "Row Set".
Support for the Native Java Interface in calling server-side Java code. Previously, calling Java stored procedures and functions from a database client required JDBC calls to associated PL/SQL wrappers. As of this release, applications can call Java stored procedures using a new API for direct invocation of static Java methods.
Support for new system properties in
oracle.jdbc.driver.OracleDriver supports the following new system properties, all of which default to
oracle.jdbc.TcpNoDelay. When set to
true, sets the
TcpNoDelay flag on the
Socket. This may speed up the data transfer rate, depending on the native implementation of the
oracle.jdbc.defaultNChar. When set to
true, the default behavior for handling character datatypes is changed to make
NVARCHAR2 the default. Setting this property to
true removes the need to use
setFormOfUse() when using
oracle.jdbc.useFetchSizeWithLongColumn. When set to
true, increases the number of
LONG column entries that are prefetched at one time. This property applies to the Thin driver only.
oracle.jdbc.V8Compatible. When set to
true, makes JDBC use a representation for
DATE data that is compatible with the Oracle8i database.
Note:These are system properties; setting a connection property overrides the value specified as a system property.
The class OracleConnectionCacheImpl. The new Implicit Connection Cache replaces this class. You should migrate your application to the new connection cache as quickly as possible, because the new implementation is more powerful and easier to use.
ZIP files. All class libraries are now supplied in JAR format only.
Support for JDK1.1. 10g Release 1 (10.1) of JDBC does not support JDK1.1. The files
classes111_g.jar are not included in this release.
The multi-language globalization files
nls_charset12.jar. To support globalization, add
orai18n.jar to your
CLASSPATH. See Chapter 12, "Globalization Support".
OracleLog is deprecated when using ojdbc14.jar. If your application uses
ojdbc14_g.jar, you should be aware of the following issues:
OracleLog.setTraceEnable() is supported and must be called to turn on tracing.
OracleLog.setLogStream() is supported, but
OracleLog.setLogWriter() is not supported.
OracleLog() methods are supported.
We recommend that you use the standard Java logging facilities in
NLS_LANG dependency removal. The
NLS_LANG variable is now completely desupported in JDBC; setting
NLS_LANG now has no effect.
This release contains the following changes to the interfaces of existing methods:
The interface for
OracleStatement.defineColumnType() has changed; see "Defining Column Types" .
Handling of international character sets has changed. See Chapter 12, "Globalization Support" for details.
CallableStatement instances that invoke PL/SQL procedures must register all
out parameters using
CallableStatement.registerOutParameter(). If a
CallableStatement invokes a procedure without registering its
out parameters, a
NullPointerException may be thrown.
As of this release, you must supply the
size parameter when invoking
OracleStatement.defineColumnType() on a
VARCHAR column. In previous releases, the
size parameter was interpreted in bytes; it is now interpreted in Java
chars. When using the Thin driver, it is best to avoid using
defineColumnType(). No benefit is derived from using this method; it can cause problems if the arguments are not optimal. If
defineColumnType() is not used, the Thin driver behaves exactly as if the optimal arguments were used.