Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Part I Overview

1 Introducing JDBC

• Overview of JDBC
• 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
  • Feature Differences Between JDBC OCI and Thin Drivers
• Overview of Application and Applet Functionality
  • Applet Basics
  • Oracle Extensions
• Server-Side Basics
• Environments and Support
  • Supported JDK and JDBC Versions
  • JNI and Java Environments
  • JDBC and IDEs
• Changes At This Release
  • New Features
  • Desupported Features
  • Interface Changes
• Feature List

2 Getting Started

• Compatibilities for Oracle JDBC Drivers
• Verifying a JDBC Client Installation
  • Checking Installed Directories and Files
  • Checking the Environment Variables
  • Ensuring that the Java Code Can Be Compiled and Run
  • Determining the Version of the JDBC Driver
  • Testing JDBC and the Database Connection

3 Basic Features

• Basic Steps in JDBC
  • Importing Packages
  • Opening a Connection to a Database
  • Creating a Statement Object
  • Running a Query and Retrieving a ResultSet Object
  • Processing the ResultSet Object
  • Closing the ResultSet and Statement Objects
  • Making Changes to the Database
  • Committing Changes
  • Closing the Connection
• Sample: Connecting, Querying, and Processing the Results
• Stored Procedure Calls in JDBC Programs
  • PL/SQL Stored Procedures
  • Java Stored Procedures
• Processing SQL Exceptions

Part II Oracle JDBC

4 JDBC Standards Support

• Introduction
• JDBC 2.0 Support: JDK 1.2.x and Later Versions
  • Data Type Support
  • Standard Feature Support
  • Extended Feature Support
  • Standard versus Oracle Performance Enhancement APIs
• 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
• Retrieval of Auto-Generated Keys
  • java.sql.DatabaseMetaData
  • java.sql.Statement
  • java.sql.Connection
  • Sample Code
• JDBC 3.0 LOB Interface Methods
• Result Set Holdability

5 Oracle Extensions

• Introduction to Oracle Extensions
• Support Features of the Oracle Extensions
  • Support for Oracle Data Types
  • Support for Oracle Objects
  • Support for Schema Naming
  • DML Returning
  • Accessing PL/SQL Index-by Tables
• Oracle JDBC Packages
  • Package oracle.sql
  • Package oracle.jdbc
• Oracle Character Data Types Support
  • SQL CHAR Data Types
  • SQL NCHAR Data Types
  • Class oracle.sql.CHAR
• Additional Oracle Type Extensions
  • Oracle ROWID Type
  • Oracle REF CURSOR Type Category
  • Oracle BINARY_FLOAT and BINARY_DOUBLE Types
  • The oracle.jdbc Package
    • 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
• DML Returning
  • Oracle-Specific APIs
  • Running DML Returning Statements
  • Example of DML Returning
  • Limitations of DML Returning
• Accessing PL/SQL Index-by Tables
  • Overview
  • Binding IN Parameters
  • Receiving OUT Parameters
  • Type Mappings

6 Features Specific to JDBC Thin

• Introduction to JDBC Thin Client
• Additional Features Supported
  • Support for Applets
  • Default Support for Native XA
• 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
    • CODE, HEIGHT, and WIDTH
    • CODEBASE
    • ARCHIVE

7 Features Specific to JDBC OCI

• OCI Connection Pooling
• Transparent Application Failover
• OCI Native XA
• OCI Instant Client
  • Overview of Instant Client
  • Benefits of Instant Client
  • JDBC OCI Instant Client Installation Process
  • Usage of Instant Client
  • Patching Instant Client Shared Libraries
  • Regeneration of Data Shared Library and ZIP files
  • Database Connection Names for OCI Instant Client
  • Environment Variables for OCI Instant Client
• Instant Client Light (English)
  • Globalization Settings
  • Operation
  • Installation

8 Server-Side Internal Driver

• Introduction
• Connecting to the Database
• Exception-Handling Extensions
• Session and Transaction Context
• Testing JDBC on the Server
• Loading an Application into the Server
• Server-Side Character Set Conversion of oracle.sql.CHAR Data

Part III Connection and Security

9 Data Sources and URLs

• Data Sources
  • Overview of Oracle Data Source Support for JNDI
  • Features and Properties of Data Sources
  • Creating a Data Source Instance and Connecting
  • Creating a Data Source Instance, Registering with JNDI, and Connecting
  • Supported Connection Properties
  • Using Roles for SYS Login
  • Configuring Database Remote Login
  • Bequeath Connection and SYS Logon
  • Properties for Oracle Performance Extensions
  • Logging and Tracing
• Database URLs and Database Specifiers

10 JDBC Client-Side Security Features

• Support for Oracle Advanced Security
  • JDBC OCI Driver Support for Oracle Advanced Security
  • JDBC Thin Driver Support for Oracle Advanced Security
• Support for Login Authentication
• Support for Data Encryption and Integrity
  • JDBC OCI Driver Support for Encryption and Integrity
  • JDBC Thin Driver Support for Encryption and Integrity
  • Setting Encryption and Integrity Parameters in Java
• Secure External Password Store

11 SSL Support

• Overview of SSL
• Java Version of SSL
• SSL in JDBC
  • Non-JSSE Related Properties
  • JSSE Related Properties
  • Enabling SSL

12 Proxy Authentication

• Need for Proxy Authentication
• Creating Proxy Connections
• Caching Proxy Connections

Part IV Data Access and Manipulation

13 Accessing and Manipulating Oracle Data

• Data Type Mappings
  • Table of Mappings
  • Notes Regarding Mappings
• 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
  • Summary of getObject and getOracleObject Return Types
  • Other getXXX Methods
    • Return Types of getXXX Methods
    • Special Notes about getXXX Methods
  • Data Types For Returned Objects from getObject and getXXX
  • The setObject and setOracleObject Methods
  • 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

14 Java Streams in JDBC

• Overview
• 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
• Streaming LOBs and External Files
• Data Streaming and Multiple Columns
• Closing a Stream
• Notes and Precautions on Streams
  • Streaming Data Precautions
  • Using Streams to Avoid Limits on setBytes and setString
  • Streaming and Row Prefetching

15 Working with Oracle Object Types

• Mapping Oracle Objects
• Using the Default STRUCT Class for Oracle Objects
  • STRUCT Class Functionality
  • Retrieving STRUCT Objects and Attributes
  • Creating STRUCT Objects and Descriptors
  • 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 Type Map 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 Map
  • 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
    • Use of ORAData for Type Inheritance Hierarchy
    • Use of SQLData for Type Inheritance Hierarchy
    • JPublisher Utility
  • 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

16 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
  • Creating and Populating a BLOB or CLOB Column
  • Accessing and Manipulating BLOB and CLOB Data
  • Additional BLOB and CLOB Features
• Data Interface for LOBs
• 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

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

18 Working with Oracle Collections

• Oracle Extensions for Collections
  • Choices in Materializing Collections
  • Creating Collections
  • Creating Multilevel Collection Types
• Overview of Collection 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
    • 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 Multilevel Collection Elements
  • Passing Arrays to Statement Objects
• Using a Type Map to Map Array Elements
• Custom Collection Classes with JPublisher

19 Result Set

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

20 JDBC RowSets

• Overview
  • RowSet Properties
  • Events and Event Listeners
  • Command Parameters and Command Execution
  • Traversing RowSets
• CachedRowSet
• JDBCRowSet
• WebRowSet
• FilteredRowSet
• JoinRowSet

21 Globalization Support

• Providing Globalization Support
• NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property

Part V Performance Enhancements

22 Statement Caching

• About Statement Caching
  • Basics of Statement Caching
  • Implicit Statement Caching
  • Explicit Statement Caching
• Using Statement Caching
  • Enabling and Disabling Statement Caching
  • Physically Closing a Cached Statement
  • Using Implicit Statement Caching
  • Using Explicit Statement Caching

23 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
  • Setting Connection Attributes
  • Checking Attributes of a Returned Connection
  • Connection Attribute Example
• Connection Cache Properties
  • Limit Properties
  • TIMEOUT Properties
  • Other Properties
  • Connection Property Example
• Connection Cache Manager API
  • createCache
  • disableCache
  • enableCache
  • existsCache
  • getCacheNameList
  • getCacheProperties
  • getNumberOfActiveConnections
  • getNumberOfAvailableConnections
  • isFatalConnectionError
  • purgeCache
  • refreshCache
  • reinitializeCache
  • removeCache
  • setConnectionPoolDataSource
  • Example Of ConnectionCacheManager Use
• Advanced Topics
  • Attribute Weights And Connection Matching
  • Connection Cache Callbacks
  • Use Cases for TimeToLiveTimeout and AbandonedConnectionTimeout

24 Run-Time Connection Load Balancing

• Overview
• Run-Time Connection Load Balancing
• Enabling Run-Time Connection Load Balancing

25 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
    • Setting the Oracle Prefetch Value
    • Oracle Row-Prefetching Limitations
  • Defining Column Types
  • DatabaseMetaData TABLE_REMARKS Reporting

26 OCI Connection Pooling

• OCI Driver Connection Pooling: Background
• OCI Driver Connection Pooling and Shared Servers Compared
• Defining an OCI Connection Pool
• Connecting to an OCI Connection Pool
• Sample Code for OCI Connection Pooling
• Statement Handling and Caching
• JNDI and the OCI Connection Pool

Part VI High Availability

27 Fast Connection Failover

• Introduction
• 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
    • Remote ONS Subscription
  • Enabling Fast Connection Failover
  • Querying Fast Connection Failover Status
• Understanding Fast Connection Failover
  • What The Application Sees
  • How It Works
• Comparison of Fast Connection Failover and TAF

28 Transparent Application Failover

• Overview
• Failover Type Events
• TAF Callbacks
• Java TAF Callback Interface

Part VII Transaction Management

29 Distributed Transactions

• Overview
  • Distributed Transaction Components and Scenarios
  • Distributed Transaction Concepts
  • Switching Between Global and Local Transactions
  • Oracle XA Packages
• XA Components
  • XADatasource Interface and Oracle Implementation
  • XAConnection Interface and Oracle Implementation
  • XAResource Interface and Oracle Implementation
  • OracleXAResource Method Functionality and Input Parameters
  • Xid Interface and Oracle Implementation
• Error Handling and Optimizations
  • XAException 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
• Native-XA in Oracle JDBC Drivers
  • OCI Native XA
  • Thin Native XA

Part VIII Manageability

30 End-To-End Metrics Support

• Introduction
• JDBC API For End-To-End Metrics

Part IX Appendixes

A Reference Information

• Valid SQL-JDBC Data Type Mappings
• Supported SQL and PL/SQL Data Types
• Embedded JDBC escape sequence Syntax
  • Time and Date Literals
  • Scalar Functions
  • LIKE Escape Characters
  • Outer Joins
  • Function Call Syntax
  • JDBC Escape Syntax to Oracle SQL Syntax Example
• Oracle JDBC Notes and Limitations
  • CursorName
  • JDBC Outer Join Escapes
  • PL/SQL TABLE, BOOLEAN, and RECORD Types
  • IEEE 754 Floating Point Compliance
  • Catalog Arguments to DatabaseMetaData Calls
  • SQLWarning Class
  • Binding Named Parameters

B Coding Tips

• JDBC and Multithreading
• Performance Optimization
  • Disabling Auto-Commit Mode
  • Standard Fetch Size and Oracle Row Prefetching
  • Standard and Oracle Update Batching
  • Statement Caching
  • Mapping Between Built-in SQL and Java Types
• Transaction Isolation Levels and Access Modes

C JDBC Error Messages

• General Structure of JDBC Error Messages
• General JDBC Messages
  • JDBC Messages Sorted by ORA Number
  • JDBC Messages Sorted in Alphabetic Order
• Native XA Messages
  • Native XA Messages Sorted by ORA Number
  • Native XA Messages Sorted in Alphabetic Order
• TTC Messages
  • TTC Messages Sorted by ORA Number
  • TTC Messages Sorted in Alphabetic Order

D Troubleshooting

• 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
  • Using statement.cancel
  • Using JDBC with Firewalls
• Basic Debugging Procedures
  • Oracle Net Tracing to Trap Network Events
    • Client-Side Tracing
    • Server-Side Tracing
  • Third Party Debugging Tools

Index