Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Organization

Related Documentation

Conventions

1 Overview

Introduction

What is JDBC?

JDBC versus SQLJ

Overview of the Oracle JDBC Drivers

Common Features of Oracle JDBC Drivers

JDBC Thin Driver

JDBC OCI Driver

JDBC Server-Side Thin Driver

JDBC Server-Side Internal Driver

Choosing the Appropriate Driver

Overview of Application and Applet Functionality

Application Basics

Applet Basics

Oracle Extensions

Package oracle.jdbc

Server-Side Basics

Session and Transaction Context

Connecting to the Database

Environments and Support

Supported JDK and JDBC Versions

JNI and Java Environments

JDBC and IDEs

Changes At This Release

2 Getting Started

Requirements and Compatibilities for Oracle JDBC Drivers

Verifying a JDBC Client Installation

Check Installed Directories and Files

Check the Environment Variables

Make Sure You Can Compile and Run Java

Determine the Version of the JDBC Driver

Testing JDBC and the Database Connection: JdbcCheckup

3 Basic Features

First Steps in JDBC

Import Packages

Register the JDBC Drivers

Open a Connection to a Database

Create a Statement Object

Execute a Query and Return a Result Set Object

Process the Result Set

Close the Result Set and Statement Objects

Make Changes to the Database

Commit Changes

Close the Connection

Sample: Connecting, Querying, and Processing the Results

Datatype Mappings

Table of Mappings

Notes Regarding Mappings

Java Streams in JDBC

Streaming LONG or LONG RAW Columns

Streaming CHAR, VARCHAR, or RAW Columns

Data Streaming and Multiple Columns

Streaming LOBs and External Files

Closing a Stream

Notes and Precautions on Streams

Stored Procedure Calls in JDBC Programs

PL/SQL Stored Procedures

Java Stored Procedures

Processing SQL Exceptions

Retrieving Error Information

Printing the Stack Trace

4 Overview of JDBC 2.0 Support

Introduction

JDBC 2.0 Support: JDK 1.2.x versus JDK 1.1.x

Datatype Support

Standard Feature Support

Extended Feature Support

Standard versus Oracle Performance Enhancement APIs

Migration from JDK 1.1.x to JDK 1.2.x

Overview of JDBC 2.0 Features

5 Overview of Supported JDBC 3.0 Features

Introduction

JDBC 3.0 Support: JDK 1.4 and Previous Releases

Overview of Supported 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

6 Overview of 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

Package oracle.jdbc

Package oracle.jdbc2 (for JDK 1.1.x only)

Oracle Character Datatypes Support

SQL CHAR Datatypes

SQL NCHAR Datatypes

Class oracle.sql.CHAR

Additional Oracle Type Extensions

Oracle ROWID Type

Oracle REF CURSOR Type Category

Support for Oracle Extensions in 8.0.x and 7.3.x JDBC Drivers

7 Accessing and Manipulating Oracle Data

Data Conversion Considerations

Standard Types versus Oracle Types

Converting SQL NULL Data

Result Set and Statement Extensions

Comparison of Oracle get and set Methods to Standard JDBC

Standard getObject() Method

Oracle getOracleObject() Method

Summary of getObject() and getOracleObject() Return Types

Other getXXX() Methods

Casting Your get Method Return Values

Standard setObject() and Oracle setOracleObject() Methods

Other setXXX() Methods

Limitations of the Oracle 8.0.x and 7.3.x JDBC Drivers

Using Result Set Meta Data Extensions

8 Working with LOBs and BFILEs

Oracle Extensions for LOBs and BFILEs

Working with BLOBs and CLOBs

Getting and Passing BLOB and CLOB Locators

Reading and Writing BLOB and CLOB Data

Creating and Populating a BLOB or CLOB Column

Accessing and Manipulating BLOB and CLOB Data

Additional BLOB and CLOB Features

Working With Temporary LOBs

Using Open and Close With LOBs

Working with BFILEs

Getting and Passing BFILE Locators

Reading BFILE Data

Creating and Populating a BFILE Column

Accessing and Manipulating BFILE Data

Additional BFILE Features

9 Working with Oracle Object Types

Mapping Oracle Objects

Using the Default STRUCT Class for Oracle Objects

STRUCT Class Functionality

Creating STRUCT Objects and Descriptors

Retrieving STRUCT Objects and Attributes

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

Understanding the SQLData Interface

Reading and Writing Data with a SQLData Implementation

Understanding the ORAData Interface

Reading and Writing Data with a ORAData Implementation

Additional Uses for ORAData

The Deprecated CustomDatum Interface

Object-Type Inheritance

Creating Subtypes

Implementing Customized Classes for Subtypes

Retrieving Subtype Objects

Creating Subtype Objects

Sending Subtype Objects

Accessing Subtype Data Fields

Inheritance Meta Data Methods

Using JPublisher to Create Custom Object Classes

JPublisher Functionality

JPublisher Type Mappings

Describing an Object Type

Functionality for Getting Object Meta Data

Steps for Retrieving Object Meta Data

SQLJ Object Types

Creating a SQLJ Object Type in SQL Representation

Inserting an Instance of a SQLJ Object Type

Retrieving Instances of a SQLJ Object Type

Meta Data Methods for SQLJ Object Types

SQLJ Object Types and Custom Object Types Compared

10 Working with Oracle Object References

Oracle Extensions for Object References

Overview of Object Reference Functionality

Object Reference Getter and 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

11 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

ARRAY Descriptors and ARRAY Class Functionality

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

Retrieving an Array and Its Elements

Passing Arrays to Statement Objects

Using a Type Map to Map Array Elements

Custom Collection Classes with JPublisher

12 Performance Extensions

Update Batching

Overview of Update Batching Models

Oracle Update Batching

Standard Update Batching

Premature Batch Flush

Additional Oracle Performance Extensions

Oracle Row Prefetching

Defining Column Types

DatabaseMetaData TABLE_REMARKS Reporting

13 Result Set Enhancements

Overview

Result Set Functionality and Result Set Categories Supported in JDBC 2.0

Oracle JDBC Implementation Overview for Result Set Enhancements

Creating Scrollable or Updatable Result Sets

Specifying Result Set Scrollability and Updatability

Result Set Limitations and Downgrade Rules

Positioning and Processing in Scrollable Result Sets

Positioning in a Scrollable Result Set

Processing a Scrollable Result Set

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

14 Statement Caching

About Statement Caching

Basics of Statement Caching

Implicit Statement Caching

Explicit Statement Caching

Using Statement Caching

Enabling and Disabling Statement Caching

Checking for Statement Creation Status

Physically Closing a Cached Statement

Using Implicit Statement Caching

Using Explicit Statement Caching

15 Distributed Transactions

Overview

Distributed Transaction Components and Scenarios

Distributed Transaction Concepts

Switching Between Global and Local Transactions

Oracle XA Packages

XA Components

XA Data Source Interface and Oracle Implementation

XA Connection Interface and Oracle Implementation

XA Resource Interface and Oracle Implementation

XA Resource Method Functionality and Input Parameters

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

16 Connection Pooling and Caching

Data Sources

A Brief Overview of Oracle Data Source Support for JNDI

Data Source Features and Properties

Creating a Data Source Instance and Connecting (without JNDI)

Creating a Data Source Instance, Registering with JNDI, and Connecting

Logging and Tracing

Connection Pooling

Connection Pooling Concepts

Connection Pool Data Source Interface and Oracle Implementation

Pooled Connection Interface and Oracle Implementation

Creating a Connection Pool Data Source and Connecting

Connection Caching

Overview of Connection Caching

Typical Steps in Using a Connection Cache

Oracle Connection Cache Specification: OracleConnectionCache Interface

Oracle Connection Cache Implementation: OracleConnectionCacheImpl Class

Oracle Connection Event Listener: OracleConnectionEventListener Class

17 JDBC OCI Extensions

OCI Driver Connection Pooling

OCI Driver Connection Pooling: Background

OCI Driver Connection Pooling and Shared Servers Compared

Stateless Sessions Compared to Stateful Sessions

Defining an OCI Connection Pool

Connecting to an OCI Connection Pool

Statement Handling and Caching

JNDI and the OCI Connection Pool

Middle-Tier Authentication Through Proxy Connections

OCI Driver Transparent Application Failover

Failover Type Events

TAF Callbacks

Java TAF Callback Interface

OCI HeteroRM XA

Configuration and Installation

Exception Handling

HeteroRM XA Code Example

Accessing PL/SQL Index-by Tables

Overview

Binding IN Parameters

Receiving OUT Parameters

18 Advanced Topics

JDBC and Globalization Support

How JDBC Drivers Perform Globalization Support Conversions

Globalization Support and Object Types

SQL CHAR Data Size Restrictions with the Thin Driver

JDBC Client-Side Security Features

JDBC Support for Oracle Advanced Security

JDBC Support for Login Authentication

JDBC Support for Data Encryption and Integrity

JDBC in Applets

Connecting to the Database through the Applet

Connecting to a Database on a Different Host Than the Web Server

Using Applets with Firewalls

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

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

Server-Side Character Set Conversion of oracle.sql.CHAR Data

19 Coding Tips and Troubleshooting

JDBC and Multithreading

Performance Optimization

Disabling Auto-Commit Mode

Standard Fetch Size and Oracle Row Prefetching

Standard and Oracle Update Batching

Common Problems

Space Padding 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

Third Party Debugging Tools

Transaction Isolation Levels and Access Modes

20 Reference Information

Valid SQL-JDBC Datatype Mappings

Supported SQL and PL/SQL Datatypes

Embedded SQL92 Syntax

Time and Date Literals

Scalar Functions

LIKE Escape Characters

Outer Joins

Function Call Syntax

SQL92 to SQL Syntax Example

Oracle JDBC Notes and Limitations

CursorName

SQL92 Outer Join Escapes

PL/SQL TABLE, BOOLEAN, and RECORD Types

IEEE 754 Floating Point Compliance

Catalog Arguments to DatabaseMetaData Calls

SQLWarning Class

Bind by Name

Related Information

Oracle JDBC Drivers and SQLJ

Java Technology

A Row Set

Introduction

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

B 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

Index