Oracle9i JDBC Developer's Guide and Reference

732 Pages·2001·5.07 MB·English

by  Sanko M., Wright B., Pfaeffle T.

Oracle9i
JDBC Developer’s Guide and Reference
Release 1 (9.0.1)
June 2001
Part No. A90211-01
Oracle9i JDBC Developer’s Guide and Reference, Release 1 (9.0.1)
Part No. A90211-01
Copyright © 1999, 2001 Oracle Corporation. All rights reserved.
Primary Author: Mike Sanko, Brian Wright, Thomas Pfaeffle
Contributors: Magdi Morsi, Ron Peterson, Ekkehard Rohwedder, Ashok Shivarudraiah, Catherine
Wong, Ed Shirk, Sunil Kunisetty, Joyce Yang, Mehul Bastawala, Luxi Chidambaran, Srinath
Krishnaswamy, Rajkumar Irudayaraj, Scott Urman, Jerry Schwarz, Steve Ding, Soulaiman Htite, Douglas
Surber, Anthony Lai, Paul Lo, Prabha Krishna, Ellen Barnes, Susan Kraft, Sheryl Maring, Angie Long
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and JDeveloper, Oracle Net, Oracle Objects, Oracle9i, Oracle8i, Oracle8,
Oracle7, PL/SQL, SQL*Net, and SQL*Plus are trademarks or registered trademarks of Oracle
Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................. xix
Preface.......................................................................................................................................................... xxi
Intended Audience.............................................................................................................................. xxi
Structure............................................................................................................................................... xxii
Conventions........................................................................................................................................ xxiv
Related Documents............................................................................................................................ xxiv
Documentation Accessibility.......................................................................................................... xxvii
1 Overview
Introduction......................................................................................................................................... 1-2
What is JDBC?............................................................................................................................... 1-2
JDBC versus SQLJ......................................................................................................................... 1-2
Overview of the Oracle JDBC Drivers............................................................................................ 1-5
Common Features of Oracle JDBC Drivers.............................................................................. 1-6
JDBC Thin Driver.......................................................................................................................... 1-7
JDBC OCI Driver.......................................................................................................................... 1-8
JDBC Server-Side Thin Driver.................................................................................................... 1-9
JDBC Server-Side Internal Driver............................................................................................ 1-10
Choosing the Appropriate Driver............................................................................................ 1-10
Overview of Application and Applet Functionality.................................................................. 1-12
Application Basics...................................................................................................................... 1-12
Applet Basics............................................................................................................................... 1-12
Oracle Extensions....................................................................................................................... 1-13
iii
Package oracle.jdbc..................................................................................................................... 1-13
Server-Side Basics............................................................................................................................. 1-15
Session and Transaction Context.............................................................................................. 1-15
Connecting to the Database....................................................................................................... 1-15
Environments and Support............................................................................................................. 1-16
Supported JDK and JDBC Versions......................................................................................... 1-16
JNI and Java Environments....................................................................................................... 1-16
JDBC and the Oracle Application Server................................................................................ 1-17
JDBC and IDEs............................................................................................................................ 1-17
2 Getting Started
Requirements and Compatibilities for Oracle JDBC Drivers.................................................... 2-2
Verifying a JDBC Client Installation.............................................................................................. 2-5
Check Installed Directories and Files......................................................................................... 2-5
Check the Environment Variables.............................................................................................. 2-6
Make Sure You Can Compile and Run Java............................................................................. 2-7
Determine the Version of the JDBC Driver............................................................................... 2-8
Testing JDBC and the Database Connection: JdbcCheckup................................................... 2-8
3 Basic Features
First Steps in JDBC............................................................................................................................. 3-2
Import Packages............................................................................................................................ 3-2
Register the JDBC Drivers........................................................................................................... 3-3
Open a Connection to a Database.............................................................................................. 3-3
Create a Statement Object.......................................................................................................... 3-11
Execute a Query and Return a Result Set Object................................................................... 3-11
Process the Result Set................................................................................................................. 3-11
Close the Result Set and Statement Objects............................................................................ 3-12
Make Changes to the Database................................................................................................. 3-12
Commit Changes........................................................................................................................ 3-13
Close the Connection.................................................................................................................. 3-14
Sample: Connecting, Querying, and Processing the Results................................................... 3-15
Datatype Mappings.......................................................................................................................... 3-16
Table of Mappings...................................................................................................................... 3-16
Notes Regarding Mappings...................................................................................................... 3-18
iv
Java Streams in JDBC....................................................................................................................... 3-19
Streaming LONG or LONG RAW Columns.......................................................................... 3-19
Streaming CHAR, VARCHAR, or RAW Columns................................................................ 3-24
Data Streaming and Multiple Columns.................................................................................. 3-25
Streaming LOBs and External Files......................................................................................... 3-27
Closing a Stream......................................................................................................................... 3-28
Notes and Precautions on Streams.......................................................................................... 3-28
Stored Procedure Calls in JDBC Programs.................................................................................. 3-31
PL/SQL Stored Procedures....................................................................................................... 3-31
Java Stored Procedures.............................................................................................................. 3-32
Processing SQL Exceptions............................................................................................................. 3-33
Retrieving Error Information.................................................................................................... 3-33
Printing the Stack Trace............................................................................................................. 3-34
4 Overview of JDBC 2.0 Support
Introduction......................................................................................................................................... 4-2
JDBC 2.0 Support: JDK 1.2.x versus JDK 1.1.x.............................................................................. 4-3
Datatype Support......................................................................................................................... 4-3
Standard Feature Support........................................................................................................... 4-4
Extended Feature Support.......................................................................................................... 4-5
Standard versus Oracle Performance Enhancement APIs..................................................... 4-5
Migration from JDK 1.1.x to JDK 1.2.x...................................................................................... 4-5
Overview of JDBC 2.0 Features........................................................................................................ 4-7
5 Overview of Oracle Extensions
Introduction to Oracle Extensions................................................................................................... 5-2
Support Features of the Oracle Extensions.................................................................................... 5-3
Support for Oracle Datatypes..................................................................................................... 5-3
Support for Oracle Objects.......................................................................................................... 5-4
Support for Schema Naming...................................................................................................... 5-5
OCI Extensions.............................................................................................................................. 5-6
Oracle JDBC Packages and Classes................................................................................................. 5-7
Package oracle.sql......................................................................................................................... 5-7
Package oracle.jdbc..................................................................................................................... 5-16
Package oracle.jdbc2 (for JDK 1.1.x only)............................................................................... 5-26
v
Oracle Character Datatypes Support............................................................................................. 5-28
SQL CHAR Datatypes................................................................................................................ 5-28
SQL NCHAR Datatypes............................................................................................................ 5-28
Class oracle.sql.CHAR............................................................................................................... 5-29
Additional Oracle Type Extensions............................................................................................... 5-33
Oracle ROWID Type.................................................................................................................. 5-33
Oracle REF CURSOR Type Category....................................................................................... 5-34
Support for Oracle Extensions in 8.0.x and 7.3.x JDBC Drivers........................................... 5-36
6 Accessing and Manipulating Oracle Data
Data Conversion Considerations..................................................................................................... 6-2
Standard Types versus Oracle Types......................................................................................... 6-2
Converting SQL NULL Data....................................................................................................... 6-2
Result Set and Statement Extensions.............................................................................................. 6-3
Comparison of Oracle get and set Methods to Standard JDBC................................................. 6-4
Standard getObject() Method...................................................................................................... 6-4
Oracle getOracleObject() Method............................................................................................... 6-4
Summary of getObject() and getOracleObject() Return Types.............................................. 6-6
Other getXXX() Methods............................................................................................................. 6-7
Casting Your get Method Return Values................................................................................ 6-10
Standard setObject() and Oracle setOracleObject() Methods............................................... 6-11
Other setXXX() Methods............................................................................................................ 6-12
Limitations of the Oracle 8.0.x and 7.3.x JDBC Drivers........................................................ 6-18
Using Result Set Meta Data Extensions....................................................................................... 6-19
7 Working with LOBs and BFILEs
Oracle Extensions for LOBs and BFILEs........................................................................................ 7-2
Working with BLOBs and CLOBs................................................................................................... 7-3
Getting and Passing BLOB and CLOB Locators...................................................................... 7-3
Reading and Writing BLOB and CLOB Data........................................................................... 7-6
Creating and Populating a BLOB or CLOB Column............................................................. 7-10
Accessing and Manipulating BLOB and CLOB Data............................................................ 7-12
Additional BLOB and CLOB Features..................................................................................... 7-13
Working With Temporary LOBs..................................................................................................... 7-18
Using Open and Close With LOBs................................................................................................ 7-19
vi
Working with BFILEs....................................................................................................................... 7-20
Getting and Passing BFILE Locators....................................................................................... 7-20
Reading BFILE Data................................................................................................................... 7-22
Creating and Populating a BFILE Column............................................................................. 7-23
Accessing and Manipulating BFILE Data............................................................................... 7-25
Additional BFILE Features........................................................................................................ 7-26
8 Working with Oracle Object Types
Mapping Oracle Objects................................................................................................................... 8-2
Using the Default STRUCT Class for Oracle Objects................................................................. 8-3
STRUCT Class Functionality...................................................................................................... 8-3
Creating STRUCT Objects and Descriptors.............................................................................. 8-4
Retrieving STRUCT Objects and Attributes............................................................................. 8-7
Binding STRUCT Objects into Statements................................................................................ 8-8
STRUCT Automatic Attribute Buffering................................................................................... 8-9
Creating and Using Custom Object Classes for Oracle Objects............................................. 8-10
Relative Advantages of ORAData versus SQLData.............................................................. 8-11
Understanding Type Maps for SQLData Implementations................................................. 8-11
Creating a Type Map Object and Defining Mappings for a SQLData Implementation.. 8-12
Understanding the SQLData Interface.................................................................................... 8-15
Reading and Writing Data with a SQLData Implementation.............................................. 8-17
Understanding the ORAData Interface................................................................................... 8-21
Reading and Writing Data with a ORAData Implementation............................................. 8-23
Additional Uses for ORAData.................................................................................................. 8-26
The Deprecated CustomDatum Interface............................................................................... 8-28
Object-Type Inheritance.................................................................................................................. 8-29
Creating Subtypes...................................................................................................................... 8-29
Implementing Customized Classes for Subtypes.................................................................. 8-30
Retrieving Subtype Objects....................................................................................................... 8-37
Creating Subtype Objects.......................................................................................................... 8-40
Sending Subtype Objects........................................................................................................... 8-41
Accessing Subtype Data Fields................................................................................................. 8-41
Inheritance Meta Data Methods............................................................................................... 8-43
Using JPublisher to Create Custom Object Classes................................................................... 8-45
JPublisher Functionality............................................................................................................ 8-45
vii
JPublisher Type Mappings........................................................................................................ 8-45
Describing an Object Type.............................................................................................................. 8-49
Functionality for Getting Object Meta Data............................................................................ 8-49
Steps for Retrieving Object Meta Data..................................................................................... 8-50
SQLJ Object Types............................................................................................................................ 8-52
Creating a SQLJ Object Type in SQL Representation............................................................ 8-53
Inserting an Instance of a SQLJ Object Type........................................................................... 8-59
Retrieving Instances of a SQLJ Object Type............................................................................ 8-60
Meta Data Methods for SQLJ Object Types............................................................................ 8-61
SQLJ Object Types and Custom Object Types Compared.................................................... 8-62
9 Working with Oracle Object References
Oracle Extensions for Object References....................................................................................... 9-2
Overview of Object Reference Functionality................................................................................ 9-4
Object Reference Getter and Setter Methods............................................................................ 9-4
Key REF Class Methods............................................................................................................... 9-4
Retrieving and Passing an Object Reference................................................................................ 9-6
Retrieving an Object Reference from a Result Set.................................................................... 9-6
Retrieving an Object Reference from a Callable Statement.................................................... 9-7
Passing an Object Reference to a Prepared Statement............................................................ 9-8
Accessing and Updating Object Values through an Object Reference.................................... 9-9
Custom Reference Classes with JPublisher................................................................................. 9-10
10 Working with Oracle Collections
Oracle Extensions for Collections (Arrays).................................................................................. 10-2
Choices in Materializing Collections....................................................................................... 10-2
Creating Collections................................................................................................................... 10-3
Creating Multi-Level Collection Types................................................................................... 10-4
Overview of Collection (Array) Functionality............................................................................ 10-5
Array Getter and Setter Methods............................................................................................. 10-5
ARRAY Descriptors and ARRAY Class Functionality.......................................................... 10-6
ARRAY Performance Extension Methods.................................................................................... 10-8
Accessing oracle.sql.ARRAY Elements as Arrays of Java Primitive Types....................... 10-8
ARRAY Automatic Element Buffering.................................................................................... 10-9
ARRAY Automatic Indexing.................................................................................................... 10-9
viii
Creating and Using Arrays............................................................................................................ 10-11
Creating ARRAY Objects and Descriptors........................................................................... 10-11
Retrieving an Array and Its Elements................................................................................... 10-15
Passing Arrays to Statement Objects..................................................................................... 10-22
Using a Type Map to Map Array Elements................................................................................ 10-25
Custom Collection Classes with JPublisher.............................................................................. 10-27
11 Result Set Enhancements
Overview............................................................................................................................................ 11-2
Result Set Functionality and Result Set Categories Supported in JDBC 2.0...................... 11-2
Oracle JDBC Implementation Overview for Result Set Enhancements............................. 11-5
Creating Scrollable or Updatable Result Sets............................................................................. 11-8
Specifying Result Set Scrollability and Updatability............................................................ 11-8
Result Set Limitations and Downgrade Rules...................................................................... 11-10
Positioning and Processing in Scrollable Result Sets............................................................. 11-13
Positioning in a Scrollable Result Set..................................................................................... 11-13
Processing a Scrollable Result Set.......................................................................................... 11-15
Updating Result Sets...................................................................................................................... 11-18
Performing a DELETE Operation in a Result Set................................................................ 11-18
Performing an UPDATE Operation in a Result Set............................................................. 11-19
Performing an INSERT Operation in a Result Set............................................................... 11-21
Update Conflicts....................................................................................................................... 11-23
Fetch Size.......................................................................................................................................... 11-24
Setting the Fetch Size............................................................................................................... 11-24
Use of Standard Fetch Size versus Oracle Row-Prefetch Setting...................................... 11-25
Refetching Rows............................................................................................................................. 11-26
Seeing Database Changes Made Internally and Externally................................................... 11-27
Seeing Internal Changes.......................................................................................................... 11-27
Seeing External Changes......................................................................................................... 11-28
Visibility versus Detection of External Changes.................................................................. 11-29
Summary of Visibility of Internal and External Changes................................................... 11-30
Oracle Implementation of Scroll-Sensitive Result Sets....................................................... 11-30
Summary of New Methods for Result Set Enhancements..................................................... 11-32
Modified Connection Methods............................................................................................... 11-32
New Result Set Methods......................................................................................................... 11-32
ix
New Statement Methods......................................................................................................... 11-35
New Database Meta Data Methods....................................................................................... 11-35
12 Performance Extensions
Update Batching................................................................................................................................ 12-2
Overview of Update Batching Models.................................................................................... 12-2
Oracle Update Batching............................................................................................................. 12-4
Standard Update Batching...................................................................................................... 12-10
Premature Batch Flush............................................................................................................. 12-18
Additional Oracle Performance Extensions............................................................................... 12-20
Oracle Row Prefetching........................................................................................................... 12-20
Defining Column Types........................................................................................................... 12-23
DatabaseMetaData TABLE_REMARKS Reporting............................................................. 12-26
13 Statement Caching
About Statement Caching............................................................................................................... 13-2
Basics of Statement Caching...................................................................................................... 13-2
Implicit Statement Caching....................................................................................................... 13-2
Explicit Statement Caching....................................................................................................... 13-4
Comparing Implicit and Explicit Statement Caching............................................................ 13-4
Using Statement Caching................................................................................................................ 13-6
Enabling and Disabling Statement Caching........................................................................... 13-6
Checking for Statement Creation Status.................................................................................. 13-7
Physically Closing a Cached Statement................................................................................... 13-7
Using Implicit Statement Caching............................................................................................ 13-8
Using Explicit Statement Caching............................................................................................ 13-9
14 Connection Pooling and Caching
Data Sources....................................................................................................................................... 14-2
A Brief Overview of Oracle Data Source Support for JNDI................................................. 14-2
Data Source Features and Properties....................................................................................... 14-3
Creating a Data Source Instance and Connecting (without JNDI)...................................... 14-7
Creating a Data Source Instance, Registering with JNDI, and Connecting....................... 14-8
Logging and Tracing................................................................................................................ 14-11
x