Oracle® Database
JDBC Developer’s Guide and Reference
10g Release 2 (10.2)
B14355-04
March 2010
This book describes how to use the Oracle JDBC drivers to
develop powerful Java database applications.
Oracle Database JDBC Developer's Guide and Reference, 10g Release 2 (10.2)
B14355-04
Copyright © 1999, 2010, Oracle and/or its affiliates. All rights reserved.
Primary Author: Venkatasubramaniam Iyer, Elizabeth Hanes Perry, Brian Wright, Thomas Pfaeffle
Contributing Author: Brian Martin
Contributor: Kuassi Mensah, Magdi Morsi, Ron Peterson, Ekkehard Rohwedder, Ashok Shivarudraiah,
Catherine Wong, Ed Shirk, Tong Zhou, Longxing Deng, Jean de Lavarene, Rosie Chen, Sunil Kunisetty, Joyce
Yang, Mehul Bastawala, Luxi Chidambaran, Srinath Krishnaswamy, Rajkumar Irudayaraj, Scott Urman,
Jerry Schwarz, Steve Ding, Soulaiman Htite, Douglas Surber, Anthony Lai, Paul Lo, Prabha Krishna, Ellen
Siegal, Susan Kraft, Sheryl Maring, Angie Long
This software and related documentation are provided under a license agreement containing restrictions on
use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your
license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,
transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse
engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is
prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If
you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on
behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data
delivered to U.S. Government customers are "commercial computer software" or "commercial technical data"
pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As
such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and

JDBC Server-Side Thin Driver 1-3
JDBC Server-Side Internal Driver 1-4
Choosing the Appropriate Driver 1-4
Feature Differences Between JDBC OCI and Thin Drivers 1-5
Overview of Application and Applet Functionality 1-5
Applet Basics 1-5
Oracle Extensions 1-6
Server-Side Basics 1-6
Environments and Support 1-7
Supported JDK and JDBC Versions 1-7
JNI and Java Environments 1-7
JDBC and IDEs 1-7
Changes At This Release 1-7
New Features 1-7
Desupported Features 1-9
Interface Changes 1-9
Feature List 1-10
2 Getting Started
Compatibilities for Oracle JDBC Drivers 2-1
Verifying a JDBC Client Installation 2-1
iv
Checking Installed Directories and Files 2-2
Checking the Environment Variables 2-3
Ensuring that the Java Code Can Be Compiled and Run 2-4
Determining the Version of the JDBC Driver 2-4
Testing JDBC and the Database Connection 2-5
3 Basic Features
Basic Steps in JDBC 3-1
Importing Packages 3-2
Opening a Connection to a Database 3-2

Retrieval of Auto-Generated Keys 4-6
java.sql.DatabaseMetaData 4-6
java.sql.Statement 4-6
java.sql.Connection 4-7
v
Sample Code 4-7
JDBC 3.0 LOB Interface Methods 4-8
Result Set Holdability 4-8
5 Oracle Extensions
Introduction to Oracle Extensions 5-1
Support Features of the Oracle Extensions 5-1
Support for Oracle Data Types 5-2
Support for Oracle Objects 5-2
Support for Schema Naming 5-3
DML Returning 5-4
Accessing PL/SQL Index-by Tables 5-4
Oracle JDBC Packages 5-4
Package oracle.sql 5-4
Package oracle.jdbc 5-10
Oracle Character Data Types Support 5-10
SQL CHAR Data Types 5-10
SQL NCHAR Data Types 5-10
Class oracle.sql.CHAR 5-11
Additional Oracle Type Extensions 5-13
Oracle ROWID Type 5-14
Oracle REF CURSOR Type Category 5-15
Oracle BINARY_FLOAT and BINARY_DOUBLE Types 5-16
The oracle.jdbc Package 5-17
Interface oracle.jdbc.OracleConnection 5-18
Interface oracle.jdbc.OracleStatement 5-19

Packaging Applets 6-8
Specifying an Applet in an HTML Page 6-8
CODE, HEIGHT, and WIDTH 6-8
CODEBASE 6-9
ARCHIVE 6-9
7 Features Specific to JDBC OCI
OCI Connection Pooling 7-1
Transparent Application Failover 7-1
OCI Native XA 7-1
OCI Instant Client 7-1
Overview of Instant Client 7-2
Benefits of Instant Client 7-2
JDBC OCI Instant Client Installation Process 7-3
Usage of Instant Client 7-4
Patching Instant Client Shared Libraries 7-4
Regeneration of Data Shared Library and ZIP files 7-5
Database Connection Names for OCI Instant Client 7-5
Environment Variables for OCI Instant Client 7-7
Instant Client Light (English) 7-8
Globalization Settings 7-9
Operation 7-9
Installation 7-10
8 Server-Side Internal Driver
Introduction 8-1
Connecting to the Database 8-1
Exception-Handling Extensions 8-3
Session and Transaction Context 8-4
Testing JDBC on the Server 8-4
Loading an Application into the Server 8-5
Server-Side Character Set Conversion of oracle.sql.CHAR Data 8-6

JSSE Related Properties 11-4
Enabling SSL 11-5
12 Proxy Authentication
Need for Proxy Authentication 12-1
Creating Proxy Connections 12-2
Caching Proxy Connections 12-3
Part IV Data Access and Manipulation
13 Accessing and Manipulating Oracle Data
Data Type Mappings 13-1
Table of Mappings 13-1
Notes Regarding Mappings 13-3
Data Conversion Considerations 13-3
viii
Standard Types Versus Oracle Types 13-4
Converting SQL NULL Data 13-4
Testing for NULLs 13-4
Result Set and Statement Extensions 13-5
Comparison of Oracle get and set Methods to Standard JDBC 13-5
Standard getObject Method 13-6
Oracle getOracleObject Method 13-6
Summary of getObject and getOracleObject Return Types 13-7
Other getXXX Methods 13-9
Return Types of getXXX Methods 13-9
Special Notes about getXXX Methods 13-11
Data Types For Returned Objects from getObject and getXXX 13-11
The setObject and setOracleObject Methods 13-12
Other setXXX Methods 13-13
Input Parameter Types of setXXX Methods 13-13
Setter Method Size Limitations 13-15
Setter Methods That Take Additional Input 13-15

Adding Entries to an Existing Type Map 15-10
Creating a New Type Map 15-11
Materializing Object Types not Specified in the Type Map 15-11
Understanding the SQLData Interface 15-12
Reading and Writing Data with a SQLData Implementation 15-13
Understanding the ORAData Interface 15-16
Reading and Writing Data with a ORAData Implementation 15-17
Additional Uses for ORAData 15-20
The Deprecated CustomDatum Interface 15-20
Object-Type Inheritance 15-21
Creating Subtypes 15-21
Implementing Customized Classes for Subtypes 15-22
Use of ORAData for Type Inheritance Hierarchy 15-22
Use of SQLData for Type Inheritance Hierarchy 15-25
JPublisher Utility 15-27
Retrieving Subtype Objects 15-27
Creating Subtype Objects 15-29
Sending Subtype Objects 15-30
Accessing Subtype Data Fields 15-30
Inheritance Meta Data Methods 15-31
Using JPublisher to Create Custom Object Classes 15-32
JPublisher Functionality 15-32
JPublisher Type Mappings 15-33
Describing an Object Type 15-35
Functionality for Getting Object Meta Data 15-35
Steps for Retrieving Object Meta Data 15-36
16 Working with LOBs and BFILEs
Oracle Extensions for LOBs and BFILEs 16-1
Working with BLOBs and CLOBs 16-2
Getting and Passing BLOB and CLOB Locators 16-2

Creating Collections 18-2
Creating Multilevel Collection Types 18-3
Overview of Collection Functionality 18-3
Array Getter and Setter Methods 18-3
ARRAY Descriptors and ARRAY Class Functionality 18-4
ARRAY Performance Extension Methods 18-5
Accessing oracle.sql.ARRAY Elements as Arrays of Java Primitive Types 18-5
ARRAY Automatic Element Buffering 18-6
ARRAY Automatic Indexing 18-6
Creating and Using Arrays 18-7
Creating ARRAY Objects and Descriptors 18-7
Retrieving an Array and Its Elements 18-10
Retrieving the Array 18-10
Data Retrieval Methods 18-11
Comparing the Data Retrieval Methods 18-12
Retrieving Elements of a Structured Object Array According to a Type Map 18-12
Retrieving a Subset of Array Elements 18-13
Retrieving Array Elements into an oracle.sql.Datum Array 18-13
Accessing Multilevel Collection Elements 18-14
Passing Arrays to Statement Objects 18-15
Using a Type Map to Map Array Elements 18-16
Custom Collection Classes with JPublisher 18-18
19 Result Set
Overview 19-1
Result Set Functionality and Result Set Categories Supported in JDBC 2.0 19-1
Oracle JDBC Implementation Overview for Result Set Enhancements 19-3
Creating Scrollable or Updatable Result Sets 19-5
Specifying Result Set Scrollability and Updatability 19-5
Result Set Limitations and Downgrade Rules 19-6
Positioning and Processing in Scrollable Result Sets 19-8

JDBCRowSet 20-8
WebRowSet 20-9
FilteredRowSet 20-11
JoinRowSet 20-13
21 Globalization Support
Providing Globalization Support 21-1
NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property 21-2
Part V Performance Enhancements
22 Statement Caching
About Statement Caching 22-1
Basics of Statement Caching 22-1
Implicit Statement Caching 22-2
Explicit Statement Caching 22-2
xii
Using Statement Caching 22-3
Enabling and Disabling Statement Caching 22-4
Physically Closing a Cached Statement 22-5
Using Implicit Statement Caching 22-5
Using Explicit Statement Caching 22-6
23 Implicit Connection Caching
The Implicit Connection Cache 23-2
Using the Connection Cache 23-3
Turning Caching On 23-3
Opening a Connection 23-4
Setting Connection Cache Name 23-4
Setting Connection Cache Properties 23-5
Closing A Connection 23-5
Implicit Connection Cache Example 23-5
Connection Attributes 23-6
Getting Connections 23-6

24 Run-Time Connection Load Balancing
Overview 24-1
Run-Time Connection Load Balancing 24-1
Enabling Run-Time Connection Load Balancing 24-2
25 Performance Extensions
Update Batching 25-1
Overview of Update Batching Models 25-2
Oracle Update Batching 25-3
Standard Update Batching 25-8
Premature Batch Flush 25-13
Additional Oracle Performance Extensions 25-14
Oracle Row Prefetching 25-15
Setting the Oracle Prefetch Value 25-15
Oracle Row-Prefetching Limitations 25-17
Defining Column Types 25-17
DatabaseMetaData TABLE_REMARKS Reporting 25-20
26 OCI Connection Pooling
OCI Driver Connection Pooling: Background 26-1
OCI Driver Connection Pooling and Shared Servers Compared 26-2
Defining an OCI Connection Pool 26-2
Connecting to an OCI Connection Pool 26-6
Sample Code for OCI Connection Pooling 26-7
Statement Handling and Caching 26-9
JNDI and the OCI Connection Pool 26-10
Part VI High Availability
27 Fast Connection Failover
Introduction 27-1
Using Fast Connection Failover 27-2
Fast Connection Failover Prerequisites 27-2
Configuring ONS For Fast Connection Failover 27-2

XAException Classes and Methods 29-13
Mapping between Oracle Errors and XA Errors 29-14
XA Error Handling 29-14
Oracle XA Optimizations 29-14
Implementing a Distributed Transaction 29-15
Summary of Imports for Oracle XA 29-15
Oracle XA Code Sample 29-15
Native-XA in Oracle JDBC Drivers 29-19
OCI Native XA 29-20
Thin Native XA 29-21
Part VIII Manageability
30 End-To-End Metrics Support
Introduction 30-1
JDBC API For End-To-End Metrics 30-2
Part IX Appendixes
A Reference Information
Valid SQL-JDBC Data Type Mappings A-1
Supported SQL and PL/SQL Data Types A-3
Embedded JDBC escape sequence Syntax A-6
Time and Date Literals A-7
xv
Scalar Functions A-8
LIKE Escape Characters A-9
Outer Joins A-9
Function Call Syntax A-9
JDBC Escape Syntax to Oracle SQL Syntax Example A-10
Oracle JDBC Notes and Limitations A-10
CursorName A-11
JDBC Outer Join Escapes A-11
PL/SQL TABLE, BOOLEAN, and RECORD Types A-11

Using statement.cancel D-3
Using JDBC with Firewalls D-3
Basic Debugging Procedures D-4
Oracle Net Tracing to Trap Network Events D-4
xvi
Client-Side Tracing D-4
TRACE_LEVEL_CLIENT D-4
TRACE_DIRECTORY_CLIENT D-5
TRACE_FILE_CLIENT D-5
TRACE_UNIQUE_CLIENT D-5
Server-Side Tracing D-6
TRACE_LEVEL_SERVER D-6
TRACE_DIRECTORY_SERVER D-6
TRACE_FILE_SERVER D-6
Third Party Debugging Tools D-6
Index
xvii
xviii
List of Tables
1–1 Feature Differences Between JDBC OCI and JDBC Thin Drivers 1-5
1–2 Feature List 1-10
3–1 Import Statements for JDBC Driver 3-2
4–1 JDBC 3.0 Feature Support 4-3
4–2 Key Areas of JDBC 3.0 Functionality 4-3
4–3 BLOB Method Equivalents 4-8
4–4 CLOB Method Equivalents 4-8
5–1 Oracle Data Type Classes 5-5
5–2 Key Interfaces and Classes of the oracle.jdbc Package 5-17
5–3 PL/SQL Types and Corresponding JDBC Types 5-29
5–4 Arguments of the setPlsqlIndexTable Method 5-30

30–1 Maximum Lengths for End-to-End Metrics 30-1
A–1 Valid SQL Data Type-Java Class Mappings A-1
A–2 Support for SQL Data Types A-3
A–3 Support for ANSI-92 SQL Data Types A-4
A–4 Support for SQL User-Defined Types A-4
A–5 Support for PL/SQL Data Types A-5
B–1 Mapping of SQL Data Types to Java Classes that Represent SQL Data Types B-3
xix
Preface
This preface introduces you to the Oracle Database JDBC Developer's Guide and Reference
discussing the intended audience, structure, and conventions of this document. A list
of related Oracle documents is also provided.
Audience
The Oracle Database JDBC Developer's Guide and Reference is intended for developers of
Java Database Connectivity (JDBC)-based applications and applets. This book can be
read by anyone with an interest in JDBC programming, but assumes at least some
prior knowledge of the following:
■ Java
■ Oracle PL/SQL
■ Oracle databases
Documentation Accessibility
Our goal is to make Oracle products, services, and supporting documentation
accessible to all users, including users that are disabled. To that end, our
documentation includes features that make information available to users of assistive
technology. This documentation is available in HTML format, and contains markup to
facilitate access by the disabled community. Accessibility standards will continue to
evolve over time, and Oracle is actively engaged with other market-leading
technology vendors to address technical obstacles so that our documentation can be
accessible to all of our customers. For more information, visit the Oracle Accessibility
Program Web site at http://www.oracle.com/accessibility/.

The following OC4J documents, for Oracle Application Server releases, are also
available from the Oracle Java Platform group:
■ Oracle Application Server Containers for J2EE User's Guide
This book provides some overview and general information for OC4J; primer
chapters for servlets, JSP pages, and EJBs; and general configuration and
deployment instructions.
■ Oracle Application Server Containers for J2EE Support for JavaServer Pages Developer's
Guide
This book provides information for JSP developers who want to run their pages in
OC4J. It includes a general overview of JSP standards and programming
considerations, as well as discussion of Oracle value-added features and steps for
getting started in the OC4J environment.
■ Oracle Application Server Containers for J2EE JSP Tag Libraries and Utilities Reference
This book provides conceptual information and detailed syntax and usage
information for tag libraries, JavaBeans, and other Java utilities provided with
OC4J.
■ Oracle Application Server Containers for J2EE Servlet Developer's Guide
This book provides information for servlet developers regarding use of servlets
and the servlet container in OC4J. It also documents relevant OC4J configuration
files.
■ Oracle Application Server Containers for J2EE Services Guide
This book provides information about basic Java services supplied with OC4J,
such as JTA, JNDI, and the Oracle Application Server Java Object Cache.
■ Oracle Application Server Containers for J2EE Enterprise JavaBeans Developer's Guide
xxi
This book provides information about the EJB implementation and EJB container
in OC4J.
The following documents are from the Oracle Server Technologies group:
■ Oracle Database Advanced Application Developer's Guide
■ Oracle Database PL/SQL Packages and Types Reference

■ jdbc-interest discussion group for JDBC
To subscribe, send an e-mail to [email protected] with the following
line in the body of the message:
subscribe jdbc-interest yourlastname yourfirstname
xxii
We recommend that you request only the daily digest of the posted e-mails. To do
this add the following line to the message body as well:
set jdbc-interest digest
Conventions
This section describes the conventions used in the text and code examples of this
documentation set. It describes:
■ Conventions in Text
■ Conventions in Code Examples
■ Conventions for Windows Operating Systems
Conventions in Text
We use various conventions in text to help you more quickly identify special terms.
The following table describes those conventions and provides examples of their use.
Convention Meaning Example
Bold Bold typeface indicates terms that are
defined in the text or terms that appear in a
glossary, or both.
When you specify this clause, you create an
index-organized table.
Italics Italic typeface indicates book titles or
emphasis.
Oracle Database Concepts
Ensure that the recovery catalog and target
database do not reside on the same disk.
UPPERCASE
monospace

names, packages and classes, user names
and roles, program units, and parameter
values.
Note: Some programmatic elements use a
mixture of UPPERCASE and lowercase.
Enter these elements as shown.
Enter sqlplus to start SQL*Plus.
The password is specified in the orapwd file.
Back up the datafiles and control files in the
/disk1/oracle/dbs directory.
The department_id, department_name, and
location_id columns are in the
hr.departments table.
Set the QUERY_REWRITE_ENABLED initialization
parameter to true.
Connect as oe user.
The JRepUtil class implements these methods.
lowercase
italic
monospace
(fixed-width)
font
Lowercase italic monospace font represents
placeholders or variables.
You can specify the parallel_clause.
Run old_release
.SQL where old_release
refers to the release you installed prior to
upgrading.
xxiii

CREATE TABLE AS subquery;
SELECT col1, col2, , coln FROM
employees;
.
.
.
Vertical ellipsis points indicate that we
have omitted several lines of code not
directly related to the example.
SQL> SELECT NAME FROM V$DATAFILE;
NAME

/fsl/dbs/tbs_01.dbf
/fs1/dbs/tbs_02.dbf
.
.
.
/fsl/dbs/tbs_09.dbf
9 rows selected.
Other notation You must enter symbols other than
brackets, braces, vertical bars, and ellipsis
points as shown.
acctbal NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
Italics
Italicized text indicates placeholders or
variables for which you must supply
particular values.
CONNECT SYSTEM/system_password
DB_NAME = database_name

Choose Start > How to start a program. To start the Database Configuration Assistant,
choose Start > Programs > Oracle -
HOME_NAME > Configuration and Migration
Tools > Database Configuration Assistant.
File and directory
names
File and directory names are not case
sensitive. The following special characters
are not allowed: left angle bracket (<), right
angle bracket (>), colon (:), double
quotation marks ("), slash (/), pipe (|), and
dash (-). The special character backslash (\)
is treated as an element separator, even
when it appears in quotes. If the file name
begins with \\, then Windows assumes it
uses the Universal Naming Convention.
c:\winnt"\"system32 is the same as
C:\WINNT\SYSTEM32
C:\> Represents the Windows command
prompt of the current hard disk drive. The
escape character in a command prompt is
the caret (^). Your prompt reflects the
subdirectory in which you are working.
Referred to as the command prompt in this
manual.
C:\oracle\oradata>
Special characters The backslash (\) special character is
sometimes required as an escape character
for the double quotation mark (") special
character at the Windows command

This release complies with Optimal
Flexible Architecture (OFA) guidelines. All
subdirectories are not under a top level
ORACLE_HOME directory. There is a top
level directory called ORACLE_BASE that
by default is C:\oracle. If you install the
latest Oracle release on a computer with no
other Oracle software installed, then the
default setting for the first Oracle home
directory is C:\oracle\orann, where nn
is the latest release number. The Oracle
home directory is located directly under
ORACLE_BASE.
All directory path examples in this guide
follow OFA conventions.
Refer to Oracle Database Platform Guide for
Microsoft Windows for additional
information about OFA compliances and
for information about installing Oracle
products in non-OFA compliant
directories.
Go to the
ORACLE_BASE\ORACLE_HOME\rdbms\admin
directory.
Convention Meaning Example