JDBC Developer's Guide - Oracle
Oracle DatabaseJDBC Developer's Guide12c Release 2 (12.2)E85756-01May 2017
Oracle Database JDBC Developer's Guide, 12c Release 2 (12.2)E85756-01Copyright 1999, 2017, Oracle and/or its affiliates. All rights reserved.Primary Author:Tulika DasContributing Authors:Thomas PfaeffleBrian Martin, Venkatasubramaniam Iyer, Elizabeth Hanes Perry, Brian Wright,Contributors: Kuassi Mensah, Douglas Surber, Paul Lo, Ed Shirk, Tong Zhou, Jean de Lavarene, RajkumarIrudayaraj, Ashok Shivarudraiah, Angela Barone, Rosie Chen, Sunil Kunisetty, Joyce Yang, MehulBastawala, Luxi Chidambaran, Vidya Nayak, Srinath Krishnaswamy, Swati Rao, Pankaj Chand, AmanManglik, Longxing Deng, Magdi Morsi, Ron Peterson, Ekkehard Rohwedder, Catherine Wong, Scott Urman,Jerry Schwarz, Steve Ding, Soulaiman Htite, Anthony Lai, Prabha Krishna, Ellen Siegal, Susan Kraft, SherylMaringThis 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.Reverse engineering, disassembly, or decompilation of this software, unless required by law forinteroperability, is prohibited.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 is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are"commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agencyspecific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of theprograms, including any operating system, integrated software, any programs installed on the hardware,and/or documentation, shall be subject to license terms and license restrictions applicable to the programs.No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron,the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced MicroDevices. UNIX is a registered trademark of The Open Group.This software or hardware and documentation may provide access to or information about content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.
ContentsPrefaceAudiencexxivDocumentation AccessibilityxxivRelated DocumentsxxivConventionsxxvChanges in This Release for Oracle Database JDBC Developer'sGuideChanges in Oracle Database 12c Release 2 (12.2)Part I1OverviewIntroducing JDBC1.1Overview of Oracle JDBC Drivers1-11.2Choosing the Appropriate Driver1-31.3Feature Differences Between JDBC OCI and Thin Drivers1-41.4Environments and Support1-41.52xxx1.4.1Supported JDK and JDBC Versions1-51.4.2JNI and Java Environments1-51.4.3JDBC and IDEs1-5Feature List1-5Getting Started2.1Version Compatibility for Oracle JDBC Drivers2-12.2Verifying a JDBC Client Installation2-22.2.1Checking the Installed Directories and Files2-22.2.2Checking the Environment Variables2-32.2.3Ensuring that the Java Code Can Be Compiled and Run2-52.2.4Determining the Version of the JDBC Driver2-5iii
2.2.52.3Testing the JDBC and Database ConnectionBasic Steps in JDBC2-72.3.1Importing Packages2-82.3.2Opening a Connection to a Database2-82.3.3Creating a Statement Object2-92.3.4Running a Query and Retrieving a Result Set Object2-102.3.5Processing the Result Set Object2-102.3.6Closing the Result Set and Statement Objects2-112.3.7Making Changes to the Database2-112.3.8About Committing Changes2-132.3.8.12.3.9Changing Commit BehaviorClosing the Connection2-142-152.4Sample: Connecting, Querying, and Processing the Results2-152.5Support for Invisible Columns2-162.6Support for Implicit Results2-182.7Support for Deprioritization of Database Nodes2-202.8Stored Procedure Calls in JDBC Programs2-212.9Part II32-52.8.1PL/SQL Stored Procedures2-212.8.2Java Stored Procedures2-21About Processing SQL Exceptions2-22Oracle JDBCJDBC Standards Support3.13.2Support for JDBC 2.0 Standard3-13.1.1Data Type Support3-23.1.2Standard Feature Support3-23.1.3Extended Feature Support3-23.1.4Standard versus Oracle Performance Enhancement APIs3-2Support for JDBC 3.0 Standard3.2.1Overview of Transaction Savepoints3-23-33.2.1.1About Creating a Savepoint3-33.2.1.2About Rolling Back to a Savepoint3-43.2.1.3About Releasing a Savepoint3-43.2.1.4About Checking Savepoint Support3-43.2.1.5Savepoint Notes3-43.2.2Retrieval of Auto-Generated Keys3-43.2.2.1java.sql.Statement3-53.2.2.2Sample Code3-53.2.2.3Limitations of Auto-Generated Keys3-5iv
3.33.43.543.2.3JDBC 3.0 LOB Interface Methods3-63.2.4Result Set Holdability3-6Support for JDBC 4.0 Standard3-63.3.1Wrapper Pattern Support3-73.3.2SQLXML Type3-83.3.3Enhanced Exception Hierarchy and SQLException3-103.3.4The RowId Data Type3-103.3.5LOB Creation3-113.3.6National Language Character Set Support3-12Support for JDBC 4.1 Standard3-123.4.1setClientInfo Method3-123.4.2getObject Method3-13Support for JDBC 4.2 Standard3-14Oracle Extensions4.1Overview of Oracle Extensions4-14.2Features of the Oracle Extensions4-14.34.44.54.2.1Database Management Using JDBC4-24.2.2Support for Oracle Data Types4-24.2.3Support for Oracle Objects4-34.2.4Support for Schema Naming4-44.2.5DML Returning4-44.2.6About Accessing PL/SQL Associative Arrays4-5Oracle JDBC Packages4-54.3.1Package oracle.sql4-54.3.2Package oracle.jdbc4-10Oracle Character Data Types Support4-104.4.1SQL CHAR Data Types4-104.4.2SQL NCHAR Data Types4-104.4.3Class oracle.sql.CHAR4-11Additional Oracle Type Extensions4-144.5.1Oracle ROWID Type4-144.5.2Oracle REF CURSOR Type Category4-154.5.3Oracle BINARY FLOAT and BINARY DOUBLE Types4-174.5.4Oracle SYS.ANYTYPE and SYS.ANYDATA Types4-184.5.5The oracle.jdbc Package4-204.5.5.1Interface oracle.jdbc.OracleConnection4-224.5.5.2Interface oracle.jdbc.OracleStatement4-234.5.5.3Interface rface oracle.jdbc.OracleCallableStatement4-24v
4.64.754.5.5.5Interface oracle.jdbc.OracleResultSet4-244.5.5.6Interface s oracle.jdbc.OracleTypes4-25DML Returning4.6.1Oracle-Specific APIs4-274.6.2About Running DML Returning Statements4-284.6.3Example of DML Returning4-284.6.4Limitations of DML Returning4-29Accessing PL/SQL Associative Arrays4-304.7.1Overview of PL/SQL Associative Arrays4-304.7.2Binding IN Parameters in PL/SQL Associative Arrays4-314.7.3Receiving OUT Parameters in PL/SQL Associative Arrays4-324.7.4Type Mappings in PL/SQL Associative Arrays4-33Features Specific to JDBC Thin5.1Overview of JDBC Thin Client5-15.2Additional Features Supported5-15.35.2.1Default Support for Native XA5-15.2.2Support for Transaction Guard5-25.2.3Support for Application Continuity5-25.2.4Support for Applets5-2JDBC in Applets5-35.3.1About Connecting to the Database Through the Applet5-35.3.2Connecting to a Database on a Different Host Than the Web Server5-45.3.2.1Using the Oracle Connection Manager5-45.3.2.2Using Signed Applets5-75.3.364-27Overview of Using Applets with Firewalls5-75.3.3.1Configuring a Firewall for Applets that use the JDBC Thin Driver5-85.3.3.2Writing a URL to Connect Through a Firewall5-85.3.4Packaging Applets5.3.5Overview of Specifying an Applet in an HTML Page5-95-105.3.5.1CODE, HEIGHT, and ures Specific to JDBC OCI Driver6.1OCI Connection Pooling6-16.2Client Result Cache6-16.2.1Benefits of Client Result Cache6-2vi
6.2.26.2.2.1RESULT CACHE MODE Parameter6-36.2.2.2Table Annotations6-36.2.2.3SQL Hints6-3Transparent Application Failover6-46.4OCI Native XA6-56.5OCI Instant Client6-56.5.1Overview of Instant Client6-56.5.2OCI Instant Client Shared Libraries6-56.5.3Benefits of Instant Client6-66.5.4JDBC OCI Instant Client Installation Process6-66.5.5Usage of Instant Client6-86.5.6About Patching Instant Client Shared Libraries6-86.5.7Regeneration of Data Shared Library and ZIP files6-96.5.8Database Connection Names for OCI Instant Client6-96.5.9Environment Variables for OCI Instant ClientAbout Instant Client Light (English)6-126-126.6.1Data Shared Library for Instant Client Light (English)6-136.6.2Globalization Settings6-136.6.3Operation6-146.6.4Installing Instant Client Light (English)6-14Server-Side Internal Driver7.1Overview of the Server-Side Internal Driver7-17.2Connecting to the Database7-17.3About Session and Transaction Context7-37.4Testing JDBC on the Server7-47.5Loading an Application into the Server7-4Part III86-26.36.67Usage Guidelines in JDBC7.5.1Using the Loadjava Utility7-47.5.2Using the JVM Command Line7-6Connection and SecurityData Sources and URLs8.1About Data Sources8-18.1.1Overview of Oracle Data Source Support for JNDI8-18.1.2Features and Properties of Data Sources8-28.1.3Creating a Data Source Instance and Connecting8-5vii
8.1.4Creating a Data Source Instance, Registering with JNDI, andConnecting8-68.1.5Supported Connection Properties8-78.1.6About Using Roles for SYS Login8-78.1.7Configuring Database Remote Login8-78.1.8Using Bequeath Connection and SYS Logon8-98.1.9Setting Properties for Oracle Performance Extensions8-98.1.108.29Support for Network Data CompressionDatabase URLs and Database Specifiers8-108-118.2.1Support for Internet Protocol Version 68-118.2.2Database Specifiers8-128.2.3Thin-style Service Name Syntax8-138.2.4Support for Delay in Connection Retries8-148.2.5TNSNames Alias Syntax8-148.2.6LDAP Syntax8-15JDBC Client-Side Security Features9.1Support for Oracle Advanced Security9-29.1.1Overview of Oracle Advanced Security9-29.1.2JDBC OCI Driver Support for Oracle Advanced Security9-39.1.3JDBC Thin Driver Support for Oracle Advanced Security9-49.2Support for Login Authentication9-49.3Support for Strong Authentication9-59.4Support for Data Encryption and Integrity9-59.59.69.79.4.1Overview of JDBC Support for Data Encryption and Integrity9-59.4.2JDBC OCI Driver Support for Encryption and Integrity9-69.4.3JDBC Thin Driver Support for Encryption and Integrity9-79.4.4Setting Encryption and Integrity Parameters in Java9-8Support for SSL9-109.5.1Overview of JDBC Support for SSL9-109.5.2About Managing Certificates and Wallets9-129.5.3About Keys and certificates containers9-129.5.4Database Connectivity Over SSL Version 1.2 Using JDBC Thin Driver9-13Support for Kerberos9-149.6.1Overview of JDBC Support for Kerberos9-149.6.2Configuring Windows to Use Kerberos9-149.6.3Configuring Oracle Database to Use Kerberos9-159.6.4Code Example for Using Kerberos9-16Support for RADIUS9-209.7.1Overview of JDBC Support for RADIUS9-209.7.2Configuring Oracle Database to Use RADIUS9-20viii
9.7.39.8109-21About Secure External Password Store9-23Proxy Authentication10.1About Proxy Authentication10-110.2Types of Proxy Connections10-210.3Creating Proxy Connections10-310.4Closing a Proxy Session10-510.5Caching Proxy Connections10-510.6Limitations of Proxy Connections10-6Part IV11Code Example for Using RADIUSData Access and ManipulationAccessing and Manipulating Oracle Data11.1Data Type Mappings11-111.1.1Table of Mappings11-111.1.2Notes Regarding Mappings11-311.2Data Conversion Considerations11-411.2.1Standard Types Versus Oracle Types11-411.2.2About Converting SQL NULL Data11-511.2.3About Testing for NULLs11-511.3Result Set and Statement Extensions11-611.4Comparison of Oracle get and set Methods to Standard JDBC11-611.4.1Standard getObject Method11-711.4.2Oracle getOracleObject Method11-711.4.3Summary of getObject and getOracleObject Return Types11-811.4.4Other getXXX Methods11-1011.4.4.1Return Types of getXXX Methods11-1011.4.4.2Special Notes about getXXX Methods11-1111.4.5Data Types For Returned Objects from getObject and getXXX11-1111.4.6The setObject and setOracleObject Methods11-1211.4.7Other setXXX Methods11-1211.4.7.1Input Data Binding11-1311.4.7.2Method setFixedCHAR for Binding CHAR Data into WHEREClauses11-1411.5Using Result Set Metadata Extensions11-1511.6About Using SQL CALL and CALL INTO Statements11-16ix
1213Java Streams in JDBC12.1Overview of Java Streams12-112.2About Streaming LONG or LONG RAW Columns12-212.2.1Overview of Streaming LONG or LONG RAW Columns12-212.2.2LONG RAW Data Conversions12-312.2.3LONG Data Conversions12-312.2.4Examples:Streaming LONG RAW Data12-412.2.5About Avoiding Streaming for LONG or LONG RAW12-612.3About Streaming CHAR, VARCHAR, or RAW Columns12-712.4About Streaming LOBs and External Files12-712.5Relation Between Data Streaming and Multiple Columns12-812.6Closing a Stream12-1012.7Notes and Precautions on Streams12-1012.7.1About Streaming Data Precautions12-1012.7.2About Using Streams to Avoid Limits on setBytes and setString12-1112.7.3Relation Between Streaming and Row Prefetching12-11Working with Oracle Object Types13.1About Mapping Oracle Objects13-113.2About Using the Default STRUCT Class for Oracle Objects13-213.2.1Overview of Using the Struct Class13-313.2.2Retrieving STRUCT Objects and Attributes13-313.2.3About Creating STRUCT Objects13-413.2.4Binding STRUCT Objects into Statements13-413.2.5STRUCT Automatic Attribute Buffering13-413.3About Creating and Using Custom Object Classes for Oracle Objects13-513.3.1Overview of Creating and Using Custom Object Classes13-613.3.2Relative Advantages of OracleData versus SQLData13-613.3.3About Type Maps for SQLData Implementations13-713.3.4About Creating Type Map and Defining Mappings for a SQLDataImplementation13-713.3.4.1Overview of Creating a Type Map and Defining Mappings13-813.3.4.2Adding Entries to an Existing Type Map13-813.3.4.3Creating a New Type Map13-913.3.4.4About Materializing Object Types not Specified in the Type Map13-913.3.5About Reading and Writing Data with a SQLData Implementation13-1013.3.6About the OracleData Interface13-1213.3.7About Reading and Writing Data with an OracleData Implementation13-1413.3.8Additional Uses of OracleData13-1613.4Object-Type Inheritance13-17x
13.4.1About Creating Subtypes13-1713.4.2About Implementing Customized Classes for Subtypes13-181513-1913.4.2.2About UsingSQLData for Type Inheritance Hierarchy13-21About Retrieving Subtype Objects13-2313.4.4Creating Subtype Objects13-2613.4.5Sending Subtype Objects13-2613.4.6Accessing Subtype Data Fields13-2613.4.7Inheritance Metadata Methods13-28About Describing an Object Type13-2813.5.1Functionality for Getting Object Metadata13-2813.5.2Retrieving Object Metadata13-29Working with LOBs and BFILEs14.1The LOB Data Types14-114.2Oracle SecureFiles14-214.3Data Interface for LOBs14-314.3.1Streamlined CallableSatement and IN OUT Parameter14-614.3.5Size Limitations14-714.4LOB Locator Interface14-714.5About Working With Temporary LOBs14-914.6About Opening Persistent LOBs with the Open and Close Methods14-1014.7About Working with BFILEs14-11Using Oracle Object References15.1Oracle Extensions for Object References15-115.2Retrieving and Passing an Object Reference15-215.2.1Retrieving an Object Reference from a Result Set15-215.2.2Retrieving an Object Reference from a Callable Statement15-315.2.3Passing an Object Reference to a Prepared Statement15-315.316About Using OracleData for Type Inheritance Hierarchy13.4.313.51413.4.2.1Accessing and Updating Object Values Through an Object Reference15-4Working with Oracle Collections16.1Oracle Extensions for Collections16-116.1.1Overview of Oracle Collections16-116.1.2Choices in Materializing Collections16-2xi
16.1.3Creating Collections16-216.1.4Creating Multilevel Collection Types16-316.2Overview of Collection Functionality16-316.3ARRAY Performance Extension Methods16-416.3.1About Accessing oracle.sql.ARRAY Elements as Arrays of JavaPrimitive Types16-516.3.2ARRAY Automatic Element Buffering16-516.3.3ARRAY Automatic Indexing16-516.4Creating and Using Arrays16.4.1Creating ARRAY Objects16-616.4.2Retrieving an Array and Its Elements16-816.4.2.1About Retrieving the Array16-816.4.2.2Data Retrieval Methods16-816.4.2.3Comparing the Data Retrieval Methods16-916.4.2.4Retrieving Elements of a Structured Object Array According to aType Map16-1016.4.2.5Retrieving a Subset of Array Elements16-1016.4.2.6Retrieving Array Elements into an oracle.sql.Datum Array16-1116.4.2.7About Accessing Multilevel Collection Elements16-1216.4.316.5171816-6Passing Arrays to Statement ObjectsUsing a Type Map to Map Array Elements16-1316-14Result Set17.1Oracle JDBC Implementation Overview for Result Set Support17-117.2Resultset Limitations and Downgrade Rules17-217.3About Avoiding Update Conflicts17-417.4Row Fetch Size17-417.4.1Setting the Fetch Size17-517.4.2Presetting the Fetch Direction17-517.5About Refetching Rows17-517.6About Viewing Database Changes Made Internally and Externally17-617.6.1Visibility versus Detection of External Changes17-717.6.2Summary of Visibility of Internal and External Changes17-717.6.3Oracle Implementation of Scroll-Sensitive Result Sets17-8JDBC RowSets18.1Overview of JDBC RowSets18-118.1.1RowSet Properties18-218.1.2Events and Event Listeners18-318.1.3Command Parameters and Command Execution18-4xii
18.1.419About CachedRowSet18-618.3About JdbcRowSet18-918.4About WebRowSet18-1018.5About FilteredRowSet18-1218.6About JoinRowSet18-14Globalization Support19.1About Providing Globalization Support19-119.2NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property19-319.3New Methods for National Character Set Type Data in JDK 619-5Performance and ScalabilityStatement and Result Set Caching20.1About Statement Caching20-120.1.1Basics of Statement Caching20-220.1.2Implicit Statement Caching20-220.1.3Explicit Statement Caching20-320.2About Using Statement Caching20-420.2.1About Enabling and Disabling Statement Caching20-420.2.2About Closing a Cached Statement20-620.2.3About Using Implicit Statement Caching20-720.2.3.120.2.420.3Methods Used in Statement Allocation and Implicit StatementCachingAbout Using Explicit Statement Caching20.2.4.1Methods Used to Retrieve Explicitly Cached StatementsAbout Reusing Statements Objects20-820-1020-1120-1120.3.1About Using a Pooled Statement20-1120.3.2About Closing a Pooled Statement20-1220.42118-518.2Part V20About Traversing RowSetsAbout Result Set Caching20-1220.4.1Server-Side Result Set Cache20-1320.4.2Client Result
Changes in This Release for Oracle Database JDBC Developer's Guide Changes in Oracle Database 12c Release 2 (12.2) xxx Part I Overview 1 Introducing JDBC 1.1 Overview of Oracle JDBC Drivers 1-1 1.2 Choosing the Appropriate Driver 1-3 1.3 Feature Differences Between JDBC OCI and Thin Drivers 1-4 1.4 Environments and Support 1-4 1.4.1 Supported JDK and JDBC Versions 1-5 1.4.2 JNI and Java .
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)
Although JDBC was designed specifically to provide a Java interface to relational databases, you may find that you need to write Java code to access non-relational databases as well. JDBC Architecture Java application calls the JDBC library. JDBC loads a driver which talks to the database In general, to process any SQL statement with JDBC, you .
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
The JDBC API provides a programming interface for data access of relational databases from the Java programming language. WebSphere Application Server V7 supports the following JDBC APIs: JDBC 4.0 (New in V7) JDBC 3.0 JDBC 2.1 and Optional Package API (2.0) In the following sec
Introduction to J2EE APIs (Servlet, JSP, EJB, JMS, JavaMail, JSF, JNDI) Introduction to Containers Tomcat as a Web Container Introduction of JDBC JDBC Architecture Data types in JDBC Processing Queries Database Exception Handling Discuss types of drivers JDBC Introduction and Need for JDBC
xii Oracle Workflow Developer's Guide Audience for This Guide Welcome to the Oracle Workflow Developer's Guide. This guide assumes you have a working knowledge of the following: The principles and customary practices of your business area. Oracle Workflow Oracle Workflow Developer's Guide. Workflow.
three main factors used for determining the premium rates under a life insurance plan are mortality, expense and interest. The premium rates are revised if there are any significant changes in any of these factors. Mortality (deaths in a particular area) When deciding upon the pricing strategy the average rate of mortality is one of the main considerations. In a country like South Africa .
