Skip Headers

Oracle® Database JDBC Developer's Guide and Reference
10g Release 1 (10.1)

Part Number B10979-01
Go to Documentation Home
Go to Book List
Book List
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to next page
View PDF


List of Tables

Title and Copyright Information

Send Us Your Comments


Intended Audience
Documentation Accessibility
Related Documents

1 Overview

What is JDBC?
Overview of the Oracle JDBC Drivers
Common Features of Oracle JDBC Drivers
JDBC Thin Driver
JDBC Server-Side Thin Driver
About Permission for the Server-Side Thin Driver
JDBC Server-Side Internal Driver
Choosing the Appropriate Driver
Overview of Application and Applet Functionality
Applet Basics
Applets and Security
Applets and Firewalls
Packaging and Deploying Applets
Oracle Extensions
Server-Side Basics
Session and Transaction Context
Connecting to the Database
Environments and Support
Supported JDK and JDBC Versions
Backward Compatibility
Forward Compatibility
JNI and Java Environments
Changes At This Release
New Features
Deprecated Features
Desupported Features
Interface Changes

2 Getting Started

Compatibilities for Oracle JDBC Drivers
Backward Compatibility
Forward Compatibility
Verifying a JDBC Client Installation
Check Installed Directories and Files
Check the Environment Variables
JDBC Thin Driver
Make Sure You Can Compile and Run Java
Determine the Version of the JDBC Driver
Testing JDBC and the Database Connection: JdbcCheckup

3 Datasources and URLs

A Brief Overview of Oracle Datasource Support for JNDI
Datasource Features and Properties
DataSource Interface and Oracle Implementation
DataSource Properties
Creating a Datasource Instance and Connecting (without JNDI)
Creating a Datasource Instance, Registering with JNDI, and Connecting
Initialize Connection Properties
Register the Datasource
Open a Connection
Logging and Tracing
Database URLs and Database Specifiers
Database Specifiers
Thin-style Service Name Syntax
TNSNames Alias Syntax

4 Basic Features

First Steps in JDBC
Importing Packages
Opening a Connection to a Database
Specifying a Database URL, User Name, and Password
Specifying a Database URL That Includes User Name and Password
Supported Connection Properties
Using Roles for Sys Logon
Bequeath Connection and Sys Logon
Remote Connection
Properties for Oracle Performance Extensions
Creating a Statement Object
Executing a Query and Returning a Result Set Object
Processing the Result Set
Closing the Result Set and Statement Objects
Making Changes to the Database
Committing Changes
Closing the Connection
Sample: Connecting, Querying, and Processing the Results
Datatype Mappings
Table of Mappings
Notes Regarding Mappings
Regarding User-Defined Types
Regarding NUMBER Types
Java Streams in JDBC
Streaming LONG or LONG RAW Columns
LONG RAW Data Conversions
LONG Data Conversions
Streaming Example for LONG RAW Data
Avoiding Streaming for LONG or LONG RAW
Streaming CHAR, VARCHAR, or RAW Columns
Data Streaming and Multiple Columns
Streaming Example with Multiple Columns
Bypassing Streaming Data Columns
Streaming LOBs and External Files
Streaming BLOBs and CLOBs
Streaming BFILEs
Closing a Stream
Notes and Precautions on Streams
Streaming Data Precautions
Using Streams to Avoid Limits on setBytes() and setString()
Streaming and Row Prefetching
Stored Procedure Calls in JDBC Programs
PL/SQL Stored Procedures
Java Stored Procedures
Processing SQL Exceptions
Retrieving Error Information
Printing the Stack Trace

5 JDBC Standards Support

JDBC 2.0 Support: JDK 1.2.x and Higher Versions
Datatype Support
Standard Feature Support
Extended Feature Support
Standard versus Oracle Performance Enhancement APIs
Migration from JDK 1.1.x
JDBC 3.0 Support: JDK 1.4 and Previous Releases
Overview of Supported JDBC 3.0 Features
Unsupported JDBC 3.0 Features
Transaction Savepoints
Creating a Savepoint
Rolling back to a Savepoint
Releasing a Savepoint
Checking Savepoint Support
Savepoint Notes
Savepoint Interfaces
Pre-JDK1.4 Savepoint Support
JDBC 3.0 LOB Interface Methods

6 Statement Caching

About Statement Caching
Basics of Statement Caching
Implicit Statement Caching
Explicit Statement Caching
Using Statement Caching
Enabling and Disabling Statement Caching
Enabling and Disabling Implicit Statement Caching
Enabling and Disabling Explicit Statement Caching
Checking for Statement Creation Status
Physically Closing a Cached Statement
Using Implicit Statement Caching
Allocating a Statement for Implicit Caching
Disabling Implicit Statement Caching for a Particular Statement
Implicitly Caching a Statement
Retrieving an Implicitly Cached Statement
Using Explicit Statement Caching
Allocating a Statement for Explicit Caching
Explicitly Caching a Statement
Retrieving an Explicitly Cached Statement

7 Implicit Connection Caching

The Implicit Connection Cache
Using the Connection Cache
Turning Caching On
Opening a Connection
Setting Connection Cache Name
Setting Connection Cache Properties
Closing A Connection
Implicit Connection Cache Example
Connection Attributes
Getting Connections
Attribute Matching Rules
Setting Connection Attributes
Checking a Returned Connection's Attributes
Connection Attribute Example
Connection Cache Properties
Limit Properties
Timeout Properties
Other Properties
Connection Property Example
Connection Cache Manager API
Example Of ConnectionCacheManager Use
Advanced Topics
Attribute Weights And Connection Matching
Connection Cache Callbacks

8 Fast Connection Failover

What Can Fast Connection Failover Do?
Using Fast Connection Failover
Fast Connection Failover Prerequisites
Configuring ONS For Fast Connection Failover
ONS Configuration File
Client-side ONS Configuration
Server-side ONS Configuration Using racgons
Enabling Fast Connection Failover
Querying Fast Connection Failover Status
Understanding Fast Connection Failover
What The Application Sees
What's Happening
Comparison of Fast Connection Failover and TAF

9 Distributed Transactions

Distributed Transaction Components and Scenarios
Distributed Transaction Concepts
Switching Between Global and Local Transactions
Mode Restrictions On Operations
Oracle XA Packages
XA Components
XA Datasource Interface and Oracle Implementation
XA Connection Interface and Oracle Implementation
XA Resource Interface and Oracle Implementation
XA Resource Method Functionality and Input Parameters
Roll back
Check for same RM
XA ID Interface and Oracle Implementation
Error Handling and Optimizations
XA Exception Classes and Methods
Mapping between Oracle Errors and XA Errors
XA Error Handling
Oracle XA Optimizations
Implementing a Distributed Transaction
Summary of Imports for Oracle XA
Oracle XA Code Sample

10 Oracle Extensions

Introduction to Oracle Extensions
Support Features of the Oracle Extensions
Support for Oracle Datatypes
Support for Oracle Objects
Support for Schema Naming
OCI Extensions
Oracle JDBC Packages and Classes
Package oracle.sql
Classes of the oracle.sql Package
General oracle.sql.* Datatype Support
Overview of Class oracle.sql.STRUCT
Overview of Class oracle.sql.REF
Overview of Class oracle.sql.ARRAY
Overview of Classes oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.BFILE
Classes oracle.sql.DATE, oracle.sql.NUMBER, and oracle.sql.RAW
Classes oracle.sql.TIMESTAMP, oracle.sql.TIMESTAMPTZ, and oracle.sql.TIMESTAMPLTZ
Overview of Class oracle.sql.ROWID
Class oracle.sql.OPAQUE
Package oracle.jdbc
Interface oracle.jdbc.OracleConnection
Interface oracle.jdbc.OracleStatement
Interface oracle.jdbc.OraclePreparedStatement
Interface oracle.jdbc.OracleCallableStatement
Interface oracle.jdbc.OracleResultSet
Interface oracle.jdbc.OracleResultSetMetaData
Class oracle.jdbc.OracleTypes
Method getJavaSqlConnection()
Oracle Character Datatypes Support
SQL CHAR Datatypes
SQL NCHAR Datatypes
Class oracle.sql.CHAR
oracle.sql.CHAR Objects and Character Sets
Constructing an oracle.sql.CHAR Object
oracle.sql.CHAR Conversion Methods
Additional Oracle Type Extensions
Oracle ROWID Type
Example: ROWID
Oracle REF CURSOR Type Category
Example: Accessing REF CURSOR Data

11 Accessing and Manipulating Oracle Data

Data Conversion Considerations
Standard Types Versus Oracle Types
Converting SQL NULL Data
Testing for NULLs
Result Set and Statement Extensions
Comparison of Oracle get and set Methods to Standard JDBC
Standard getObject() Method
Oracle getOracleObject() Method
Example: Using getOracleObject() with a ResultSet
Example: Using getOracleObject() in a Callable Statement
Summary of getObject() and getOracleObject() Return Types
Other getXXX() Methods
Return Types and Input Parameter Types of getXXX() Methods
Special Notes about getXXX() Methods
Casting Your get Method Return Values
Example: Casting Return Values
Standard setObject() and Oracle setOracleObject() Methods
Example: Using setObject() and setOracleObject() in a Prepared Statement
Other setXXX() Methods
Input Parameter Types of setXXX() Methods
Setter Method Size Limitations
Setter Methods That Take Additional Input
Method setFixedCHAR() for Binding CHAR Data into WHERE Clauses
Using Result Set Meta Data Extensions

12 Globalization Support

Providing Globalization Support
NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property
JDBC Methods Dependent On Conversion

13 Working with Oracle Object Types

Mapping Oracle Objects
Using the Default STRUCT Class for Oracle Objects
STRUCT Class Functionality
Standard java.sql.Struct Methods
Oracle oracle.sql.STRUCT Class Methods
STRUCT Descriptors
Creating STRUCT Objects and Descriptors
Steps in Creating StructDescriptor and STRUCT Objects
Using StructDescriptor Methods
Serializable STRUCT Descriptors
Retrieving STRUCT Objects and Attributes
Retrieving an Oracle Object as an oracle.sql.STRUCT Object
Retrieving an Oracle Object as a java.sql.Struct Object
Retrieving Attributes as oracle.sql Types
Retrieving Attributes as Standard Java Types
Binding STRUCT Objects into Statements
STRUCT Automatic Attribute Buffering
Creating and Using Custom Object Classes for Oracle Objects
Relative Advantages of ORAData versus SQLData
Understanding Type Maps for SQLData Implementations
Creating a Type Map Object and Defining Mappings for a SQLData Implementation
Adding Entries to an Existing Type Map
Creating a New Type Map
Materializing Object Types not Specified in the Type File
Understanding the SQLData Interface
Understanding the SQLInput and SQLOutput Interfaces
Implementing readSQL() and writeSQL() Methods
Reading and Writing Data with a SQLData Implementation
Reading SQLData Objects from a Result Set
Retrieving SQLData Objects from a Callable Statement OUT Parameter
Passing SQLData Objects to a Callable Statement as an IN Parameter
Writing Data to an Oracle Object Using a SQLData Implementation
Understanding the ORAData Interface
Understanding ORAData Features
Retrieving and Inserting Object Data
Reading and Writing Data with a ORAData Implementation
Reading Data from an Oracle Object Using a ORAData Implementation
Writing Data to an Oracle Object Using a ORAData Implementation
Additional Uses for ORAData
The Deprecated CustomDatum Interface
Object-Type Inheritance
Creating Subtypes
Implementing Customized Classes for Subtypes
Use of ORAData for Type Inheritance Hierarchy
Use of SQLData for Type Inheritance Hierarchy
JPublisher Utility
Retrieving Subtype Objects
Using Default Mapping
Using SQLData Mapping
Using ORAData Mapping
Creating Subtype Objects
Sending Subtype Objects
Accessing Subtype Data Fields
Subtype Data Fields from the getAttribute() Method
Subtype Data Fields from the getOracleAttribute() Method
Inheritance Meta Data Methods
Using JPublisher to Create Custom Object Classes
JPublisher Functionality
JPublisher Type Mappings
Categories of SQL Types
Type-Mapping Modes
Mapping the Oracle object type to Java
Mapping Attribute Types to Java
Summary of SQL Type Categories and Mapping Settings
Describing an Object Type
Functionality for Getting Object Meta Data
Steps for Retrieving Object Meta Data

14 Working with LOBs and BFILEs

Oracle Extensions for LOBs and BFILEs
Working with BLOBs and CLOBs
Getting and Passing BLOB and CLOB Locators
Retrieving BLOB and CLOB Locators
Passing BLOB and CLOB Locators
Reading and Writing BLOB and CLOB Data
Example: Reading BLOB Data
Example: Reading CLOB Data
Example: Writing BLOB Data
Example: Writing CLOB Data
Creating and Populating a BLOB or CLOB Column
Creating a BLOB or CLOB Column in a New Table
Populating a BLOB or CLOB Column in a New Table
Accessing and Manipulating BLOB and CLOB Data
Additional BLOB and CLOB Features
Additional BLOB Methods
Additional CLOB Methods
Creating Empty LOBs
Shortcuts For Inserting and Retrieving CLOB Data
Working With Temporary LOBs
Creating Temporary NCLOBs
Using Open and Close With LOBs
Working with BFILEs
Getting and Passing BFILE Locators
Retrieving BFILE Locators
Passing BFILE Locators
Reading BFILE Data
Example: Reading BFILE Data
Creating and Populating a BFILE Column
Creating a BFILE Column in a New Table
Populating a BFILE Column
Accessing and Manipulating BFILE Data
Additional BFILE Features

15 Using Oracle Object References

Oracle Extensions for Object References
Overview of Object Reference Functionality
Object Reference Getter and Setter Methods
Result Set and Callable Statement Getter Methods
Prepared and Callable Statement Setter Methods
Key REF Class Methods
Retrieving and Passing an Object Reference
Retrieving an Object Reference from a Result Set
Retrieving an Object Reference from a Callable Statement
Passing an Object Reference to a Prepared Statement
Accessing and Updating Object Values through an Object Reference
Custom Reference Classes with JPublisher

16 Working with Oracle Collections

Oracle Extensions for Collections (Arrays)
Choices in Materializing Collections
Creating Collections
Creating Multi-Level Collection Types
Overview of Collection (Array) Functionality
Array Getter and Setter Methods
Result Set and Callable Statement Getter Methods
Prepared and Callable Statement Setter Methods
ARRAY Descriptors and ARRAY Class Functionality
ARRAY Descriptors
ARRAY Class Methods
ARRAY Performance Extension Methods
Accessing oracle.sql.ARRAY Elements as Arrays of Java Primitive Types
ARRAY Automatic Element Buffering
ARRAY Automatic Indexing
Creating and Using Arrays
Creating ARRAY Objects and Descriptors
Steps in Creating ArrayDescriptor and ARRAY Objects
Creating Multi-Level Collections
Using ArrayDescriptor Methods
Serializable ARRAY Descriptors
Retrieving an Array and Its Elements
Retrieving the Array
Data Retrieval Methods
Comparing the Data Retrieval Methods
Retrieving Elements of a Structured Object Array According to a Type Map
Retrieving a Subset of Array Elements
Retrieving Array Elements into an oracle.sql.Datum Array
Accessing Multi-Level Collection Elements
Passing Arrays to Statement Objects
Passing an Array to a Prepared Statement
Passing an Array to a Callable Statement
Using a Type Map to Map Array Elements
Custom Collection Classes with JPublisher

17 Result Set Enhancements

Result Set Functionality and Result Set Categories Supported in JDBC 2.0
Scrollability, Positioning, and Sensitivity
Result Set Types for Scrollability and Sensitivity
Concurrency Types for Updatability
Summary of Result Set Categories
Oracle JDBC Implementation Overview for Result Set Enhancements
Oracle JDBC Implementation for Result Set Scrollability
Oracle JDBC Implementation for Result Set Updatability
Implementing a Custom Client-Side Cache for Scrollability
Creating Scrollable or Updatable Result Sets
Specifying Result Set Scrollability and Updatability
Result Set Limitations and Downgrade Rules
Result Set Limitations
Result Set Downgrade Rules
Verifying Result Set Type and Concurrency Type
Positioning and Processing in Scrollable Result Sets
Positioning in a Scrollable Result Set
Methods for Moving to a New Position
Methods for Checking the Current Position
Processing a Scrollable Result Set
Backward versus Forward Processing
Presetting the Fetch Direction
Updating Result Sets
Performing a DELETE Operation in a Result Set
Performing an UPDATE Operation in a Result Set
Performing an INSERT Operation in a Result Set
Update Conflicts
Fetch Size
Setting the Fetch Size
Use of Standard Fetch Size versus Oracle Row-Prefetch Setting
Refetching Rows
Seeing Database Changes Made Internally and Externally
Seeing Internal Changes
Seeing External Changes
Visibility versus Detection of External Changes
Summary of Visibility of Internal and External Changes
Oracle Implementation of Scroll-Sensitive Result Sets
Summary of New Methods for Result Set Enhancements
Modified Connection Methods
New Result Set Methods
Statement Methods
Database Meta Data Methods

18 Row Set

Row Set Setup and Configuration
Runtime Properties for Row Set
Row Set Listener
Traversing Through the Rows
Cached Row Set
CachedRowSet Constraints
JDBC Row Set

19 JDBC OCI Extensions

OCI Driver Connection Pooling
OCI Driver Connection Pooling: Background
OCI Driver Connection Pooling and Shared Servers Compared
Defining an OCI Connection Pool
Importing the oracle.jdbc.pool and oracle.jdbc.oci Packages
Creating an OCI Connection Pool
Setting the OCI Connection Pool Parameters
Checking the OCI Connection Pool Status
Connecting to an OCI Connection Pool
Statement Handling and Caching
JNDI and the OCI Connection Pool
OCI Driver Transparent Application Failover
Failover Type Events
TAF Callbacks
Java TAF Callback Interface
Handling the FO_ERROR Event
Handling the FO_ABORT Event
Configuration and Installation
Exception Handling
HeteroRM XA Code Example
Accessing PL/SQL Index-by Tables
Binding IN Parameters
Receiving OUT Parameters
Registering the OUT Parameters
Accessing the OUT Parameter Values

20 OCI Instant Client

Benefits of Instant Client
JDBC OCI Instant Client Installation Process
When to Use Instant Client
Patching Instant Client Shared Libraries
Regeneration of Data Shared Library
Database Connection Names for OCI Instant Client
Environment Variables for OCI Instant Client

21 End-To-End Metrics Support

JDBC API For End-To-End Metrics

22 Performance Extensions

Update Batching
Overview of Update Batching Models
Oracle Model versus Standard Model
Types of Statements Supported
Oracle Update Batching
Oracle Update Batching Characteristics and Limitations
Setting the Connection Batch Value
Setting the Statement Batch Value
Checking the Batch Value
Overriding the Batch Value
Committing the Changes in Oracle Batching
Update Counts in Oracle Batching
Standard Update Batching
Limitations in the Oracle Implementation of Standard Batching
Adding Operations to the Batch
Executing the Batch
Committing the Changes in the Oracle Implementation of Standard Batching
Clearing the Batch
Update Counts in the Oracle Implementation of Standard Batching
Error Handling in the Oracle Implementation of Standard Batching
Intermixing Batched Statements and Non-Batched Statements
Premature Batch Flush
Additional Oracle Performance Extensions
Oracle Row Prefetching
Setting the Oracle Prefetch Value
Oracle Row-Prefetching Limitations
Defining Column Types
DatabaseMetaData TABLE_REMARKS Reporting
Considerations for getProcedures() and getProcedureColumns() Methods

23 Advanced Topics

JDBC Client-Side Security Features
JDBC Support for Oracle Advanced Security
OCI Driver Support for Oracle Advanced Security
Thin Driver Support for Oracle Advanced Security
JDBC Support for Login Authentication
JDBC Support for Data Encryption and Integrity
OCI Driver Support for Encryption and Integrity
Thin Driver Support for Encryption and Integrity
Setting Encryption and Integrity Parameters in Java
JDBC in Applets
Connecting to the Database through the Applet
Connecting to a Database on a Different Host Than the Web Server
Using the Oracle Connection Manager
Using Signed Applets
Using Applets with Firewalls
Configuring a Firewall for Applets that use the JDBC Thin Driver
Writing a URL to Connect through a Firewall
Packaging Applets
Specifying an Applet in an HTML Page
JDBC in the Server: the Server-Side Internal Driver
Connecting to the Database with the Server-Side Internal Driver
Connecting with the OracleDriver Class defaultConnection() Method
Connecting with the OracleDataSource.getConnection() Method
Exception-Handling Extensions for the Server-Side Internal Driver
Session and Transaction Context for the Server-Side Internal Driver
Testing JDBC on the Server
Loading an Application into the Server
Loading Class Files into the Server
Loading Source Files into the Server
Server-Side Character Set Conversion of oracle.sql.CHAR Data

24 Reference Information

Valid SQL-JDBC Datatype Mappings
Supported SQL and PL/SQL Datatypes
Embedded SQL92 Syntax
Disabling Escape Processing
Time and Date Literals
Date Literals
Time Literals
Timestamp Literals
Scalar Functions
LIKE Escape Characters
Outer Joins
Function Call Syntax
SQL92 to SQL Syntax Example
Oracle JDBC Notes and Limitations
SQL92 Outer Join Escapes
IEEE 754 Floating Point Compliance
Catalog Arguments to DatabaseMetaData Calls
SQLWarning Class
Binding Named Parameters
Retaining Bound Values

25 Proxy Authentication

Middle-Tier Authentication Through Proxy Connections

26 Coding Tips and Troubleshooting

JDBC and Multithreading
Performance Optimization
Disabling Auto-Commit Mode
Example: Disabling AutoCommit
Standard Fetch Size and Oracle Row Prefetching
Standard and Oracle Update Batching
Mapping Between Built-in SQL and Java Types
Common Problems
Memory Consumption for CHAR Columns Defined as OUT or IN/OUT Variables
Memory Leaks and Running Out of Cursors
Boolean Parameters in PL/SQL Stored Procedures
Opening More Than 16 OCI Connections for a Process
Basic Debugging Procedures
Oracle Net Tracing to Trap Network Events
Client-Side Tracing
Server-Side Tracing
Third Party Debugging Tools
Transaction Isolation Levels and Access Modes

A JDBC Error Messages

General Structure of JDBC Error Messages
General JDBC Messages
JDBC Messages Sorted by ORA Number
JDBC Messages Sorted Alphabetically
HeteroRM XA Messages
HeteroRM XA Messages Sorted by ORA Number
HeteroRM XA Messages Sorted Alphabetically
TTC Messages
TTC Messages Sorted by ORA Number
TTC Messages Sorted Alphabetically