Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Structure

Conventions

Related Documents

Documentation Accessibility

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 the Oracle Application Server

JDBC and IDEs

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

6 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

7 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

8 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

9 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

10 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

11 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

New Statement Methods

New Database Meta Data Methods

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 Statement Caching

About Statement Caching

Basics of Statement Caching

Implicit Statement Caching

Explicit Statement Caching

Comparing Implicit and 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

14 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

15 Distributed Transactions

Overview

Distributed Transaction Components and Scenarios

Distributed Transaction Concepts

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 JDBC OCI Extensions

OCI Driver Connection Pooling

OCI Driver Connection Pooling: Background

OCI Driver Connection Pooling and MTS 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

17 Java Transaction API

Transaction Overview

Global and Local Transactions

Demarcating Transactions

Enlisting Resources

Two-Phase Commit

JTA Limitations

JTA Summary

Environment Initialization

Methods for Enlisting Database Resources

Summary of Single-Phase and Two-Phase Commit

Binding Transactional Objects in the Namespace

JTA Client-Side Demarcation

JTA Server-Side Database Enlistment

Configuring Two-Phase Commit Engine

Creating DataSource Objects Dynamically

JDBC Restrictions

18 Advanced Topics

JDBC and Globalization Support

How JDBC Drivers Perform Globalization Support Conversions

Globalization Support and Object Types

CHAR and VARCHAR2 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 Sample Applications

Basic Samples

Listing Names from the EMP Table--Employee.java

Inserting Names into the EMP Table--InsertExample.java

Samples of PL/SQL in JDBC

Calling PL/SQL Stored Procedures--PLSQLExample.java

Executing Procedures in PL/SQL Blocks--PLSQL.java

Accessing a PL/SQL Index-by Table from JDBC--PLSQLIndexTab.java

Intermediate Samples

Streams--StreamExample.java

Multithreading--JdbcMTSample.java

Samples for JDBC 2.0 Types

BLOBs and CLOBs--LobExample.java

Weakly Typed Objects--PersonObject.java

Weakly Typed Object References--StudentRef.java

Weakly Typed Arrays--ArrayExample.java

Samples for Oracle Type Extensions

REF CURSORs--RefCursorExample.java

BFILEs--FileExample.java

Samples for Custom Object Classes

SQLData Implementation--SQLDataExample.java

ORAData Implementation--ORADataExample.java

JDBC 2.0 Result Set Enhancement Samples

Positioning in a Result Set--ResultSet2.java

Inserting and Deleting Rows in a Result Set--ResultSet3.java

Updating Rows in a Result Set--ResultSet4.java

Scroll-Sensitive Result Set--ResultSet5.java

Refetching Rows in a Result Set--ResultSet6.java

Performance Enhancement Samples

Standard Update Batching--BatchUpdates.java

Oracle Update Batching with Implicit Execution--SetExecuteBatch.java

Oracle Update Batching with Explicit Execution--SendBatch.java

Oracle Row Prefetching Specified in Connection--RowPrefetch_connection.java

Oracle Row Prefetching Specified in Statement--RowPrefetch_statement.java

Oracle Column Type Definitions--DefineColumnType.java

Implicit Statement Caching--StmtCache1.java

Explicit Statement Caching--StmtCache2.java

Samples for Connection Pooling and Distributed Transactions

Data Source without JNDI--DataSource.java

Data Source with JNDI--DataSourceJNDI.java

Pooled Connection--PooledConnection.java

OCI Connection Pool--OCIConnectionPool.java

Middle-Tier Authentication--NtierAuth.java

JDBC OCI Application Failover Callbacks--OCIFailOver.java

Oracle Connection Cache (dynamic)--CCache1.java

Oracle Connection Cache ("fixed with no wait")--CCache2.java

XA with Suspend and Resume--XA2.java

XA with Two-Phase Commit Operation--XA4.java

HeteroRM XA--XA6.java

Sample Applet

HTML Page--JdbcApplet.htm

Applet Code--JdbcApplet.java

JDBC versus SQLJ Sample Code

SQL Program to Create Tables and Objects

JDBC Version of the Sample Code

SQLJ Version of the Sample Code

21 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