Go to main content
1/50
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
Scripting on this page enhances content navigation, but does not change the content in any way.