1/54

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 2 (11.2)

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 Result Set Object
  • Processing the Result Set Object
  • Closing the Result Set and Statement Objects
  • Making Changes to the Database
  • Committing Changes
    • Changing Commit Behavior
  • 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
  • Wrapper Pattern Support
  • SQLXML Type
  • Enhanced Exception Hierarchy and SQLException
  • The RowId Data Type
  • LOB Creation
  • National Language Character Set Support

4 Oracle Extensions

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

5 Features Specific to JDBC Thin

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

6 Features Specific to JDBC OCI Driver

• OCI Connection Pooling
• Client Result Cache
  • Benefits of Client Result Cache
  • Usage Guidelines in JDBC
    • RESULT_CACHE_MODE Parameter
    • Table Annotations
    • SQL Hints
• 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

• Overview of the Server-Side Internal Driver
• Connecting to the Database
• Session and Transaction Context
• Testing JDBC on the Server
• Loading an Application into the Server
  • Using the Loadjava Utility
  • Using the JVM Command-Line

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 Windows to Use 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
• Limitations of 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 of Java Streams
• 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
  • 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
  • Reading and Writing Data with a SQLData Implementation
  • Understanding the ORAData Interface
  • Reading and Writing Data with a ORAData Implementation
  • Additional Uses for ORAData
• 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

• The LOB Data Types
• Oracle SecureFiles
• Data Interface for LOBs
  • Streamlined Mechanism
  • Input
  • Output
  • CallableSatement and IN OUT Parameter
  • Size Limitations
• LOB Locator Interface
• Working With Temporary LOBs
• Opening Persistent LOBs with the Open and Close Methods
• Working with BFILEs

15 Using Oracle Object References

• Oracle Extensions for Object References
• 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

• Oracle JDBC Implementation Overview for Result Set Support
• Resultset Limitations and Downgrade Rules
• Avoiding Update Conflicts
• Fetch Size
  • Setting the Fetch Size
  • Presetting the Fetch Direction
• Refetching Rows
• Viewing Database Changes Made Internally and Externally
  • 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 of JDBC RowSets
  • 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 Result Set 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
• Result Set Caching
  • Server-side Cache
  • Client Result 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 of 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 Nonbatched Statements
  • Premature Batch Flush
• Additional Oracle Performance Extensions
  • Prefetching LOB Data
  • Oracle Row-Prefetching Limitations
  • Defining Column Types
  • Reporting DatabaseMetaData TABLE_REMARKS

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 of Oracle Advanced Queuing
• Making Changes to the Database
• AQ Asynchronous Event Notification
• Creating Messages
• Example: Creating a Message and Setting a Payload
• Enqueuing Messages
• Dequeuing Messages
• Examples: Enqueuing and Dequeuing

26 Database Change Notification

• Creating a Registration
• Associating a Query with a Registration
• Notifying Database Change Events
• Deleting a Registration

Part VI High Availability

27 Fast Connection Failover

• Overview of Fast Connection Failover
• Using Fast Connection Failover
  • Fast Connection Failover Prerequisites
  • Configuring ONS for Fast Connection Failover
    • 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 of Transparent Application Failover
• Failover Type Events
• TAF Callbacks
• Java TAF Callback Interface

Part VII Transaction Management

29 Distributed Transactions

• Overview of Distributed Transactions
  • 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 Database Administration

31 Diagnosability in JDBC

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

32 JDBC DMS Metrics

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

Part IX Appendixes

A JDBC Reference Information

• Valid SQL-JDBC Data Type Mappings
• Supported SQL and PL/SQL Data Types
• Embedded JDBC Escape Syntax
  • Time and Date Literals
    • Date Literals
    • Time Literals
    • Timestamp 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
  • Executing DDL Statements
  • Binding Named Parameters

B Oracle RAC Fast Application Notification

• Overview of Oracle RAC Fast Application Notification
• Installing and Configuring Oracle RAC Fast Application Notification
  • Configuration of ONS
    • Overview of ONS Configuration File
    • Configuring Client-Side ONS
• Using Oracle RAC Fast Application Notification
• Implementing a Connection Cache

C 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

D 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

E 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
  • Frequent Abrupt Disconnection from Server
• Basic Debugging Procedures
  • Oracle Net Tracing to Trap Network Events
    • Client-Side Tracing
    • Server-Side Tracing
  • Third Party Debugging Tools

Index