Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New

• New Features for Release 1 (11.1)
• Desupported Features
• Interface Changes

Part I Overview

1 Introducing JDBC

• Overview of Oracle JDBC Drivers
  • Common Features of Oracle JDBC Drivers
  • Choosing the Appropriate Driver
  • Feature Differences Between JDBC OCI and Thin Drivers
• Environments and Support
  • Supported JDK and JDBC Versions
  • JNI and Java Environments
  • JDBC and IDEs
• Feature List

2 Getting Started

• Version Compatibility for Oracle JDBC Drivers
• Verification of a JDBC Client Installation
  • Check the Installed Directories and Files
  • Check the Environment Variables
  • Ensure that the Java Code Can Be Compiled and Run
  • Determine the Version of the JDBC Driver
  • Test JDBC and the Database Connection
• 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

3 JDBC Standards Support

• Support for JDBC 2.0 Standard
  • Data Type Support
  • Standard Feature Support
  • Extended Feature Support
  • Standard versus Oracle Performance Enhancement APIs
• Support for JDBC 3.0 Standard
  • Transaction Savepoints
    • Creating a Savepoint
    • Rolling Back to a Savepoint
    • Releasing a Savepoint
    • Checking Savepoint Support
    • Savepoint Notes
  • Retrieval of Auto-Generated Keys
    • java.sql.Statement
    • Sample Code
    • Limitations
  • JDBC 3.0 LOB Interface Methods
  • Result Set Holdability
• Support for JDBC 4.0 Standard

4 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

5 Oracle Extensions

• Introduction to Oracle Extensions
• Features of the Oracle Extensions
  • Database Management Using JDBC
  • 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
  • Oracle SYS.ANYTYPE and SYS.ANYDATA 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 OCI Driver

• 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

7 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

Part III Connection and Security

8 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
• Database URLs and Database Specifiers

9 JDBC Client-Side Security Features

• Support for Oracle Advanced Security
• Support for Login Authentication
• Support for Strong Authentication
• Support for OS Authentication
  • Configuration Steps for Linux
  • Configuration Steps for Windows
  • JDBC Code Using OS 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
• Support for SSL
  • Managing Certificates and Wallets
  • Keys and certificates containers
• Support for Kerberos
  • Configuring Oracle Database to Use Kerberos
  • Code Example
• Support for RADIUS
  • Configuring Oracle Database to Use RADIUS
  • Code Example
• Secure External Password Store

10 Proxy Authentication

• About Proxy Authentication
• Types of Proxy Connections
• Creating Proxy Connections
• Closing a Proxy Session
• Caching Proxy Connections

Part IV Data Access and Manipulation

11 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 Data Binding
    • Method setFixedCHAR for Binding CHAR Data into WHERE Clauses
• Using Result Set Metadata Extensions
• Using SQL CALL and CALL INTO Statements

12 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

13 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 Metadata Methods
• Using JPublisher to Create Custom Object Classes
  • JPublisher Functionality
  • JPublisher Type Mappings
• Describing an Object Type
  • Functionality for Getting Object Metadata
  • Steps for Retrieving Object Metadata

14 Working with LOBs and BFILEs

• Oracle Extensions for LOBs and BFILEs
• Working with BLOBs, CLOBs and NCLOBs
  • Getting and Passing BLOB, CLOB, and NCLOB Locators
    • Retrieving BLOB, CLOB, and NCLOB Locators
    • Passing BLOB, CLOB and NCLOB Locators
  • Reading and Writing BLOB, CLOB and NCLOB Data
  • Creating and Populating a BLOB, CLOB or NCLOB Column
  • Accessing and Manipulating BLOB, CLOB, or NCLOB Data
• 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
• Oracle SecureFiles

15 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

16 Working with Oracle Collections

• Oracle Extensions for Collections
  • Choices in Materializing Collections
  • Creating Collections
  • Creating Multilevel Collection Types
• Overview of Collection 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
  • 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

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

18 JDBC RowSets

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

19 Globalization Support

• Providing Globalization Support
• NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property in JDK 1.5
• New Methods for National Character Set Type Data in JDK 1.6

Part V Performance and Scalability

20 Statement and Resultset Caching

• About Statement Caching
  • Basics of Statement Caching
  • Implicit Statement Caching
  • Explicit Statement Caching
• Using Statement Caching
  • Enabling and Disabling Statement Caching
  • Closing a Cached Statement
  • Using Implicit Statement Caching
  • Using Explicit Statement Caching
• Reusing Statements Objects
  • Using a Pooled Statement
  • Closing a Pooled Statement
• Resultset Caching
  • Server-side Cache
  • Client Query Cache

21 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
• Advanced Topics
  • Attribute Weights And Connection Matching
  • Connection Cache Callbacks
  • Use Cases for TimeToLiveTimeout and AbandonedConnectionTimeout

22 Run-Time Connection Load Balancing

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

23 Performance Extensions

• Update Batching
  • Overview of Update Batching Models
  • 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
    • Error Reporting in Oracle Update Batching
  • Standard Update Batching
    • Limitations in the Oracle Implementation of Standard Batching
    • Adding Operations to the Batch
    • Processing 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 Limitations
  • Defining Column Types
  • DatabaseMetaData TABLE_REMARKS Reporting

24 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

25 Oracle Advanced Queuing

• Functionality and Framework
• AQ Asynchronous Event Notification
• Creating Messages
• Enqueuing Messages
• Dequeuing Messages
• Examples: Enqueuing and Dequeuing

Part VI High Availability

26 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

27 Transparent Application Failover

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

Part VII Transaction Management

28 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

29 Database Management

• Database Startup and Shutdown
• Database Change Notification

30 Diagnosability in JDBC

• Logging
  • Enabling and Using JDBC Logging
    • Configuring the CLASSPATH Environment Variable
    • Enabling Logging
    • Configuring Logging
    • Using Loggers
    • An Example
  • Performance, Scalability, and Security Issues
• Diagnosability Management

31 JDBC DMS Metrics

• Overview
• Determining the Type of Metric to be Generated
• Generating the SQLText Metric
• Accessing DMS Metrics Using JMX

Part IX Appendixes

A Reference Information

• Valid SQL-JDBC Data Type Mappings
• Supported SQL and PL/SQL Data Types
• Embedded SQL92 Syntax
  • 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
  • CursorName
  • SQL92 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