Oracle Database JDBC Developer's Guide And Reference

Oracle DatabaseJDBC Developer’s Guide and Reference10g Release 2 (10.2)B14355-04March 2010This book describes how to use the Oracle JDBC drivers todevelop powerful Java database applications.

Oracle Database JDBC Developer's Guide and Reference, 10g Release 2 (10.2)B14355-04Copyright 1999, 2010, Oracle and/or its affiliates. All rights reserved.Primary Author:Venkatasubramaniam Iyer, Elizabeth Hanes Perry, Brian Wright, Thomas PfaeffleContributing Author:Brian MartinContributor: Kuassi Mensah, Magdi Morsi, Ron Peterson, Ekkehard Rohwedder, Ashok Shivarudraiah,Catherine Wong, Ed Shirk, Tong Zhou, Longxing Deng, Jean de Lavarene, Rosie Chen, Sunil Kunisetty, JoyceYang, Mehul Bastawala, Luxi Chidambaran, Srinath Krishnaswamy, Rajkumar Irudayaraj, Scott Urman,Jerry Schwarz, Steve Ding, Soulaiman Htite, Douglas Surber, Anthony Lai, Paul Lo, Prabha Krishna, EllenSiegal, Susan Kraft, Sheryl Maring, Angie LongThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this software or related documentation is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, the following notice is applicable:U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical datadelivered to U.S. Government customers are "commercial computer software" or "commercial technical data"pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. Assuch, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions andlicense terms set forth in the applicable Government contract, and, to the extent applicable by the terms ofthe Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer SoftwareLicense (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.This software is developed for general use in a variety of information management applications. It is notdeveloped or intended for use in any inherently dangerous applications, including applications which maycreate a risk of personal injury. If you use this software in dangerous applications, then you shall beresponsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe useof this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use ofthis software in dangerous applications.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarksof their respective owners.This software and documentation may provide access to or information on content, products, and servicesfrom third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim allwarranties of any kind with respect to third-party content, products, and services. Oracle Corporation andits affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use ofthird-party content, products, or services.

ContentsPreface . xixAudience. xixDocumentation Accessibility . xixRelated Documents . xxConventions . xxiiPart I1OverviewIntroducing JDBCOverview of JDBC. 1-1Overview of the Oracle JDBC Drivers . 1-1Common Features of Oracle JDBC Drivers . 1-2JDBC Thin Driver . 1-3JDBC OCI Driver . 1-3JDBC Server-Side Thin Driver. 1-3JDBC Server-Side Internal Driver . 1-4Choosing the Appropriate Driver. 1-4Feature Differences Between JDBC OCI and Thin Drivers. 1-5Overview of Application and Applet Functionality . 1-5Applet Basics. 1-5Oracle Extensions . 1-6Server-Side Basics . 1-6Environments and Support . 1-7Supported JDK and JDBC Versions. 1-7JNI and Java Environments . 1-7JDBC and IDEs. 1-7Changes At This Release . 1-7New Features . 1-7Desupported Features . 1-9Interface Changes. 1-9Feature List . 1-102Getting StartedCompatibilities for Oracle JDBC Drivers. 2-1Verifying a JDBC Client Installation. 2-1iii

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 .3Basic FeaturesBasic 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 acle JDBCJDBC Standards SupportIntroduction.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 java.sql.Connection 44-44-54-54-54-64-64-64-64-7

Sample Code . 4-7JDBC 3.0 LOB Interface Methods. 4-8Result Set Holdability . 4-85Oracle ExtensionsIntroduction to Oracle Extensions . 5-1Support Features of the Oracle Extensions . 5-1Support for Oracle Data Types . 5-2Support for Oracle Objects. 5-2Support for Schema Naming . 5-3DML Returning . 5-4Accessing PL/SQL Index-by Tables. 5-4Oracle JDBC Packages. 5-4Package oracle.sql . 5-4Package oracle.jdbc . 5-10Oracle Character Data Types Support . 5-10SQL CHAR Data Types . 5-10SQL NCHAR Data Types. 5-10Class oracle.sql.CHAR. 5-11Additional Oracle Type Extensions . 5-13Oracle ROWID Type. 5-14Oracle REF CURSOR Type Category . 5-15Oracle BINARY FLOAT and BINARY DOUBLE Types . 5-16The oracle.jdbc Package . 5-17Interface oracle.jdbc.OracleConnection . 5-18Interface oracle.jdbc.OracleStatement. 5-19Interface oracle.jdbc.OraclePreparedStatement . 5-20Interface oracle.jdbc.OracleCallableStatement . 5-21Interface oracle.jdbc.OracleResultSet . 5-23Interface oracle.jdbc.OracleResultSetMetaData. 5-24Class oracle.jdbc.OracleTypes. 5-24Method getJavaSqlConnection. 5-25DML Returning . 5-26Oracle-Specific APIs. 5-27Running DML Returning Statements. 5-27Example of DML Returning . 5-28Limitations of DML Returning. 5-29Accessing PL/SQL Index-by Tables . 5-29Overview . 5-29Binding IN Parameters . 5-30Receiving OUT Parameters. 5-31Type Mappings. 5-336Features Specific to JDBC ThinIntroduction to JDBC Thin Client. 6-1Additional Features Supported . 6-1v

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 -86-86-86-96-9Features Specific to JDBC OCIOCI Connection Pooling. 7-1Transparent Application Failover . 7-1OCI Native XA . 7-1OCI Instant Client. 7-1Overview of Instant Client. 7-2Benefits of Instant Client . 7-2JDBC OCI Instant Client Installation Process . 7-3Usage of Instant Client . 7-4Patching Instant Client Shared Libraries . 7-4Regeneration of Data Shared Library and ZIP files . 7-5Database Connection Names for OCI Instant Client . 7-5Environment Variables for OCI Instant Client . 7-7Instant Client Light (English) . 7-8Globalization Settings. 7-9Operation. 7-9Installation. 7-108Server-Side Internal DriverIntroduction.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 IIIviConnection and Security8-18-18-38-48-48-58-6

9Data Sources and URLsData Sources . 9-1Overview of Oracle Data Source Support for JNDI . 9-1Features and Properties of Data Sources . 9-2Creating a Data Source Instance and Connecting . 9-6Creating a Data Source Instance, Registering with JNDI, and Connecting. 9-6Supported Connection Properties . 9-7Using Roles for SYS Login . 9-9Configuring Database Remote Login. 9-10Bequeath Connection and SYS Logon. 9-11Properties for Oracle Performance Extensions . 9-11Logging and Tracing. 9-12Database URLs and Database Specifiers. 9-1310JDBC Client-Side Security FeaturesSupport 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 .11SSL SupportOverview of SSL.Java Version of SSL .SSL in JDBC .Non-JSSE Related Properties .JSSE Related Properties .Enabling SSL 211-211-411-5Proxy AuthenticationNeed for Proxy Authentication. 12-1Creating Proxy Connections. 12-2Caching Proxy Connections . 12-3Part IV13Data Access and ManipulationAccessing and Manipulating Oracle DataData Type Mappings.Table of Mappings .Notes Regarding Mappings.Data Conversion Considerations .13-113-113-313-3vii

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 .

Oracle Database JDBC Developer's Guide and Reference 10g Release 2 (10.2) B14355-04 March 2010 This book describes how to use the Oracle JDBC drivers to develop powerful Java database applications. Oracle Database JDBC Developer's Guide and Reference, 10g Release 2 (10.2)