Oracle JDBC FAQ

Updated April 15, 2014

This document contains answers to the most frequently asked questions about Oracle's JDBC drivers. Note that these address specific technical questions only and are used to document solutions to frequent customer questions as well as any known asproblems. The server docs (including JDBC doc) are also available online.

Note that this FAQ contains information about supported versions of the Oracle JDBC drivers. Please refer to the below link for more details on the supported version.  



Back to Top

Whats New?

Questions related to latest version of Oracle Database 12cR1 have been added and all the other references to older versions such as 10g, 9i, 8i have been removed. Below is the section which highlights the key JDBC features of 12c. Please refer to  Java Performance, Scalability, Availability, Manageability & Security with Oracle database 12 for more detailed information

  • Support for Latest Java Standards:  Oracle JDBC and UCP now support JSE 7 with JDBC 4.1 specification. 

  • Support for Multitenant Container Support:The Oracle database 12c introduces a new multitenant architecture consisting of a root infrastructure called Container Database(CDB) which contains exclusively Oracle provided metadata, then a set of pluggable databases(PDBs), which are full fledged databases containing customers, applications data and metadata. Oracle JDBC and UCP furnish the Multitenant Datasource for Java by allowing sharing a single pool of connections across multiple PDBs i.e., tenants.  

  • Support for New SQL DataTypes: Java pplications may leverage new data types including: 32K VARCHAR, NVARCHAR, and RAW, invisible/hidden columns, implicit results, auto-increment or IDENTITY columns, PL/SQL packaged types and AS parameters, larger row count data type, and XStream enhancements. 

  • Improved Performance and Scalability:New performance and scalability enhancements include: New JDBC memory managemen, Database Resident Connection Pool (DRCP), very large network buffers(SDU), and RuntimeConnections Load Balancing across geographies (Global Data Services). 

  • Transaction Guard and Application Continuity for Java: When a database outage occurs, four problems confront applications:(1) hangs, (2) errors, (3) determining the outcome of in-flight work and (4) the resubmission of in-flight work.  Oracle database 12c handles these outages better throughTransaction Guard for a reliable outcome of in-flight work and Application Continuity for capturing and replaying in-flight transactions.

  • Better Manageability, Ease of use:Oracle database 12c furnishes row count per iteration for array DML,monitoring and tracing database operations, intelligent client connectivity and faster dead connection detection. 

  • Advanced Security:With Oracle database 12c, JDBC now supports SHA-2 hashing algorithms(including:SHA-256, SHA-384,and SHA-512) to generate secure message digests. Overall,Java applications can use the following hashing algorithms:MD5, SHA1, SHA-256, SHA-384 or SHA-512. 

  • Easier Applications Migration: Solves the problem for migrating Java applications built against non Oracle RDBMS.  Oracle 12c provides a framework for translating foreign SQL syntax into Oracle SQL syntax before being submitted to the Oracle RDBMS, SQL engine for compilation and execution. 


 Back to Top


JDBC in General

What is JDBC?

The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases—SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC API provides a call-level API for SQL-based database access.

JDBC technology allows you to use the Java programming language to exploit "Write Once, Run Anywhere" capabilities for applications that require access to enterprise data. With a JDBC technology-enabled driver, you can connect all corporate data even in a heterogeneous environment.

—From Sun's JDBC page.

Back to Top
Where can I learn more about JDBC?

A good place to start is Sun's JDBC page.

There are lots of books on JDBC. A good place to start is JDBC API Tutorial and Reference, Third Edition .

You can find out more about Oracle's JDBC drivers in particular from OTN's SQLJ & JDBC pages, from Oracle's online documentation, JDBC Sample Code, and from books such as the highly recommended Expert Oracle JDBC Programming .

Back to Top
Where can I learn more about Java?

A good place to start is Oracle's Java site.

There are lots of books on Java. A few of the more popular ones are:

Back to Top

Release Specific Questions

Which version of JDBC drivers are supported ?

Please refer to the below table for the three version of supported JDBC drivers.  For more details, please refer to Oracle database  roadmap

Release
GA Date Premier Support Ends
Extended Support Ends
Sustaining Support Ends
12.1 Jun 2013 Jul 2018 Jul 2021 Indefinite
11.2 Sep 2009 Jan 2015 Jan 2018 Indefinite
11.1 Aug 2007 Aug 2012 Aug 2015 Indefinite

Back to Top


Which version of JDBC drivers support which version of Oracle database?

Please refer to the below table for the Oracle database versions supported by JDBC drivers.  Best Practice that we recommend is, JDBC driver version should always be either same as or higher than the Oracle database version being used in order to leverage the latest capabilities of the JDBC driver.

Interoperability Matrix Database 12.1.0.x Database 11.2.0.x Database 11.1.0.x

Database 10.2.0.x

JDBC 12.1.0.x
Yes
Yes
Yes
Yes
JDBC 11.2.0.x
Yes
Yes
Yes
Yes
JDBC 11.1.0.x
Yes
Yes
Yes
Yes
JDBC 10.2.0.x
Yes
Yes
Yes
Yes

Back to Top
 


What are the various supported Oracle database version vs JDBC compliant versions vs JDK version supported?

Below is the table which mentions the supported Oracle database versions, equivalent  supported JDK versions, JDBC version compliant to in each of the releases and also mentions the JDBC jar file names which need to be used for specific releases.
 
Oracle Database version
JDK Version supported
JDBC specification compliance
JDBC Jar files specific to the release
12.1 or 12cR1 JDK 7 & JDK 6 JDBC 4.1 in the JDK 7 drivers
JDBC 4.0 in the JDK 6 drivers
ojdbc7.jar for JDK 7
ojdbc6.jar for JDK 6
11.2 or 11gR2 JDK 6 & JDK 5
JDK 7 supported in 11.2.0.3 and 11.2.0.4
JDBC 4.0 in the JDK 6 drivers
JDBC 3.0 in the JDK 5 drivers
ojdbc6.jar for JDK 7
ojdbc6.jar for JDK 6
ojdbc5.jar for JDK 5
11.1 or 11gR1 JDK 6 & JDK 5 JDBC 4.0 in the JDK 6 drivers       JDBC 3.0 in the JDK 5 drivers ojdbc6.jar for JDK 6
ojdbc5.jar for JDK 5

Back to Top
 


What about the JDBC version which are not listed in the above table? 

If it is not listed in the above table then please check with your support channel to check if you are still on the support contract for the older versions. 

Back to Top


 Where can I get the JDBC jar files?

Please download the required JDBC jar files from the Oracle Technology Network SQLJ & JDBC Download Page.

Back to Top


What are the different JAR files on the download page for? 

Please refer to the below table to know more details about these jar files

JAR file name
Description
ojdbc7.jar All the classes to support basic functionality for the Thin and OCI drivers when using JDK 7 using JDBC 4.1 specification. Additional jar files are required when you use some features.
ojdbc7_g.jar Same as ojdbc7.jar except compiled with the -g option to include debugging information and with java.util.logging calls included.
ojdbc7dms.jar Same as ojdbc7.jar except includes code to support Oracle Dynamic Monitoring Service (DMS). Also includes some JDBC logging support. This file can only be used when dms.jar is also in the classpath. The dms.jar file is not shipped as part of the RDBMS product. It is only available as part of the Oracle Application Server product.
ojdbc7dms_g.jar Same as ojdbc7dms.jar except compiled with the -g option to include debugging information and with full JDBC logging support.
ojdbc6.jar All the classes to support basic functionality for the Thin and OCI drivers when using JDK 6 and compliant to JDBC 4.0. Additional jar files are required when you use some features.
ojdbc6_g.jar Same as ojdbc6.jar except compiled with the -g option to include debugging information and with java.util.logging calls included.
ojdbc6dms.jar Same as ojdbc6.jar except includes code to support Oracle Dynamic Monitoring Service (DMS). Also includes some JDBC logging support. This file can only be used when dms.jar is also in the classpath. The dms.jar file is not shipped as part of the RDBMS product. It is only available as part of the Oracle Application Server product.
ojdbc6dms_g.jar Same as ojdbc6dms.jar except compiled with the -g option to include debugging information and with full JDBC logging support.
ojdbc5.jar All the classes to support basic functionality for the Thin and OCI drivers when using JDK 5. Additional jar files are required when you use some features.
ojdbc5_g.jar Same as ojdbc5.jar except compiled with the -g option to include debugging information and with java.util.logging calls included.
ojdbc5dms.jar Same as ojdbc5.jar except includes code to support Oracle Dynamic Monitoring Service (DMS). Also includes some JDBC logging support. This file can only be used when dms.jar is also in the classpath. The dms.jar file is not shipped as part of the RDBMS product. It is only available as part of the Oracle Application Server product.
ojdbc5dms_g.jar Same as ojdbc5dms.jar except compiled with the -g option to include debugging information and with full JDBC logging support.
libocijdbc<major_version_id>.so (Solaris) Native library for the JDBC OCI driver. Use 11 for JDBC 11.x.x version and 12 for JDBC 12.x.x for <major_version_id>.  This file should be locatable via your LD_LIBRARY_PATH setting
ocijdbc<major_version_id>.dll (Windows) Similar to above, except on Windows platforms. This file should be locatable via your %PATH% setting.
libheteroxa<major_version_id>.so (Solaris) Auxiliary native library for the JDBC OCI driver, for the native-XA (or Hetero-RM) feature. This library is only available for JDBC 9.x.x, 10.x.x, and 11.x.x drivers. When using this feature, this library should be locatable via your LD_LIBRARY_PATH setting, along with libocijdbc<major_version_id>.so.
heteroxa<major_version_id>.dll (Windows) Similar to above, except on Windows platforms. When using OCI native-XA, this file should be locatable via the %PATH% setting, along with ocijdbc<major_version_id>.dll.
orai18n.jar Contains the configuration information to support all Oracle character sets in Advanced Data Types (objects). If the database character set is one other than UCS2,ASCII, ISO_LATIN_1, UTF8 and AL32UTF8 and the application uses ADTs, then you must include this file in your classpath. 
xbd6.jar Classes to support standard JDBC 4.x java.sql.SQLXML interface(Java SE 6 & Java SE 7)

Back to Top


Are Oracle's JDBC jar files identical on all platforms?

Yes. The actual .jar files are identical on all platforms. The only difference is the .so (or .dll) file that is called by the OCI client. The .jar files are identical and can be swapped around between platforms if you want.

Back to Top

Oracle JDBC in General

What JDBC drivers does Oracle provide?

Oracle provides four different types of JDBC drivers, for use in different deployment scenarios.  While all Oracle JDBC drivers are similar, some features apply only to JDBC OCI drivers and some apply only to the JDBC Thin driver.

  • JDBC Thin client-side driver: This is a JDBC Type 4 driver that uses Java to connect directly to Oracle. It implements Oracle's SQL*Net Net8 and TTC adapters using its own TCP/IP based Java socket implementation. The JDBC Thin driver does not require Oracle client software to be installed, but does require the server to be configured with a TCP/IP listener.

    Because it is written entirely in Java, this driver is platform-independent. The JDBC Thin driver can be downloaded into any browser as part of a Java application. (Note that if running in a client browser, that browser must allow the applet to open a Java socket connection back to the server.)

  • JDBC Thin server-side driver: This is another JDBC Type 4 driver that uses Java to connect directly to Oracle. This driver is used internally within the Oracle database. This driver offers the same functionality as the client-side JDBC Thin driver (above), but runs inside an Oracle database and is used to access remote databases.

    Because it is written entirely in Java, this driver is platform-independent. There is no difference in your code between using the Thin driver from a client application or from inside a server.

  • JDBC OCI client-side driver: This is a JDBC Type 2 driver that uses Java native methods to call entrypoints in an underlying C library. That C library, called OCI (Oracle Call Interface), interacts with an Oracle database. The JDBC OCI driver requires an Oracle client installation of the same version as the driver.

    The use of native methods makes the JDBC OCI driver platform specific. Oracle supports Solaris, Windows, and many other platforms. This means that the Oracle JDBC OCI driver is not appropriate for Java applets, because it depends on a C library.

     This JDBC OCI driver is available for install with the OCI Instant Client feature, which does not require a complete Oracle client-installation. Please refer to Oracle Call Interface for more information.

  • JDBC Server-Side Internal driver: This is another JDBC Type 2 driver that uses Java native methods to call entrypoints in an underlying C library. That C library is part of the Oracle server process and communicates directly with the internal SQL engine inside calls and thus avoiding any network traffic. This allows your Java code running in the server to access the underlying database in the fastest possible manner. It can only be used to access the same database. The use of native methods makes the JDBC Server-Side Internal driver platform specific. This server-side internal driver is fully consistent with the client-side drivers and supports the same features and extensions. 
  • Back to Top
    Which driver should I use?

    Use the Thin driver unless there is a reason to use something else. If you are writing an applet, you must use the Thin driver.

    If you are using a non-TCP/IP network you must use the OCI driver.

    If you are running in the Oracle server, then you should use the Server Internal Driver unless you need to connect to another Oracle database server or to open a second session on the same server. In either of these cases you should use the Server Thin Driver.

    Generally the Thin driver is the best choice.

    Back to Top
    What about the files for the Server Thin Driver and the Server Internal Driver?

    Both of these drivers run only in the Oracle Server Java VM and their classes are installed as part of installing the VM. There are no separate classes files available or needed for these drivers.

    Back to Top
    Can I load one of the classes files into the Oracle Server Java VM?

    No. you should not do this. The necessary class files are installed as part of the database installation.


Back to Top
Can third party vendors distribute Oracle's JDBC drivers along with their own software? 
If you are a third party software company (and Oracle partner) then please check out Oracle's licensing terms spelled out at Oracle Licensing Agreement Please contact your local Oracle sales rep for more details.
    Back to Top
    orai18n.jar is too big. How can I make it smaller?


    If you wish to reduce the size of orai18n.jar, proceed as follows.

    The file orai18n.jar contains many important character set and globalization support files. Instead of extracting only the character set glb files that your application uses, follow this three-step process:

    1. Unpack orai18n.jar into a temporary directory
    2. Delete all files in your temporary directory EXCEPT few important files as explained below
    3. The character set glb files that your application uses AND the following 18 class files:
      oracle/i18n/util/ClassLoaderChooser.class, oracle/i18n/util/ConverterArchive.class, oracle/i18n/util/GDKMessage.class oracle/i18n/util/GDKOracleMetaData.class oracle/i18n/util/OraClassLoader.class oracle/i18n/util/OraResourceBundle.class oracle/i18n/util/message/Messages.class oracle/i18n/text/converter/CharacterConverter12Byte.class oracle/i18n/text/converter/CharacterConverterOGS.class oracle/i18n/text/converter/CharacterConverter1Byte.class oracle/i18n/text/converter/CharacterConverterGB18030.class oracle/i18n/text/converter/CharacterConverterJAEUC.class oracle/i18n/text/converter/CharacterConverterLC.class oracle/i18n/text/converter/CharacterConverterLCFixed.class oracle/i18n/text/converter/CharacterConverterZHTEUC.class oracle/i18n/text/converter/CharacterConverter2ByteFixed.class oracle/i18n/text/converter/CharacterConverterSJIS.class oracle/i18n/text/converter/CharacterConverterShift.class

      The character set glb files are located in oracle/i18n/data/ and named in the format, lx20 <OracleCharacterSetId>.glb; where <OracleCharacterSetId> is the hexadecimal representation of the Oracle character set ID. The decimal representation of this ID can be found by the SQL function, NLS_CHARSET_ID. For example, if your application connect to a JA16SJIS database, the following SQL would return 832 in decimal representation.

      select NLS_CHARASET_ID('ja16sjis') from DUAL;

      Manually convert this decimal value to hexadecimal value and get 340. Therefore, you should save oracle/i18n/data/lx20340.glb in your temporary directory together with the 15 class file above.

      In your temporary directory, ensure you have the same directory structure as the original package. All glb files and the 15 class files are distributed in

      oracle/i18n/data/ ( lx20 <OracleCharacterSetId>.glb files)
      oracle/i18n/util/ (3 class files)
      oracle/i18n/util/message/ (1 class files)
      oracle/i18n/text/converter/ (11 class files)
    4. Create a new jar file from the temporary directory using a different file name other than orai18n.jar and add the new jar file to your CLASSPATH.
    Back to Top
    What threads do the Oracle JDBC drivers create?

    The drivers create a number of different threads, but only on an as-needed basis. They do not create the threads unless your code makes use of the feature that depends on the thread. All of these threads are daemon threads.

    • Statement timeout thread:. This thread is created if you execute any statement with a timeout. Only one thread is created no matter how many statements or connections. This thread lasts the lifetime of the VM.
    • Implicit Connection Cache timeout thread:. Used to enforce timeouts on the connection cache, and is enabled when at least one timeout property on the connection cache is enabled. There is one thread per connection cache. It lasts the lifetime of that connection cache.
    • Fast Connection Failover Event handler thread: Listener thread to receive HA events from RAC. Started only when FCF is enabled. There is one thread per connection cache. It lasts the lifetime of that connection cache.
    • FCF worker thread Processes UP/DOWN events: Started only when the events are being processed, and terminates after it is done its job.
    • Runtime Load Balancing Event Handler thread: This is the listener thread that receives RLB events from RAC. Started only when FCF is enabled. There is one thread per connection cache. It lasts the lifetime of that connection cache.
    • RLB Gravitate connection cache thread: This is started only when RLB is enabled, and when there is a need to gravitate connections in the cache to healthy instances. The thread terminates after it has finished its job.
    • OracleConnectionCacheImpl timeout thread:Deprecated. Started only when this old cache is used, to enforce timeouts on the old cache. There is one thread per connection cache. It lasts the lifetime of that connection cache.
    Back to Top
    What permissions do the Oracle JDBC drivers require?

    When your application is run with a SecurityManager enabled (which it should in production) certain operations are priviliged. In order to do those operations the code must be granted the appropriate permissions.

    As usual, it depends. It depends on what you are trying to do. Beginning with 10.2 the drivers do the right thing when the SecurityManager is enabled. This requires quite a long list of permissions. In a secure environment you only want to grant the necessary permissions, so which permissions you grant depends on what your application is asking the drivers to do.

    The way to find out what permissions to grant is to look at the file ojdbc.policy in the demo directory. This is a generic security policy file that you can use to grant the drivers all the necessary permissions. In most cases you will want to comment out many of the permissions since your app doesn't use the features that requires those permissions.

    This file depends on a number of system properties. To use this file you will have to define those properties using the -D option to the java command.

    Some of the permissions need only be granted to the JDBC driver code. The operations that require those permissions are enclosed in a doPriviliged block. Other permissions must also be granted to the code that calls the drivers. Those operations are not enclosed in doPriviliged blocks. Which is which was chosen so as to make it as difficult as possible to use the drivers to cause mischief. One noteworthy example is that the calling code needs the open socket permission when using the thin driver to open a connection. This is to prevent rogue code from using the drivers for a denial of service attack, among other reasons.

    Back to Top
    I just installed 11.1 and my code won't compile or throws IllegalAccessError, What should be done?

    The classes in the package oracle.jdbc.driver are desupported in 11.1. You should use the types in the package oracle.jdbc instead. Typically all that is required is to replace oracle.jdbc.driver with oracle.jdbc. If that doesn't work then your code is using classes or methods that were never intended for public use. 

    Back to Top 
    oracle.jdbc.driver is desupported, yet the package is still in the jar files?

    Customer use of the package is desupported. We did not remove the package or the classes it contains. This package is the core implementation code for the driver. We didn't renamed the packaged.

    What we did do is change the visibility of the classes and methods in the package. Most of the classes are now default (package) visible instead of public visible. Many of the internal implementation methods used to be public visible. Most are now more restricted. These are the changes that would cause source to give compile errors or compiled code to throw runtime errors.

    Back to Top
    I don't use oracle.jdbc.driver but I'm still having problems with 11.1. Are there any other changes?

    Yes.The class oracle.jdbc.pool.OracleConnectionCacheImpl has also been desupported.  Use the Oracle Implicit Connection Cache instead. It is documented in the Oracle JDBC Developer's Guide.

    Back to Top

    Installation

    How do I install the Thin driver?

    Put the jar files in a convenient location and include the appropriate jar files in your classpath. See What are the different JAR files on the download page for? to determine which files you need.

    Back to Top
    How do I install the OCI driver?

    The JDBC OCI driver generally requires an Oracle client-installation of the same version the driver. Starting from 10.1.0, the JDBC OCI driver is available for install with the OCI Instant Client feature, which does not require a complete Oracle client-installation. Please refer to the documentation on OCI Instant Client install.

    Back to Top
    How do I install the Server-Side Internal driver or the Thin in the server driver?

    You don't. These two drivers are installed as part of the database installation. If the database was installed with Java support, these two drivers are already installed and available. See Can I load one of the classes files into the Oracle Server Java VM?

    Back to Top
    Where can I get the files for my platform?

    Remember that Java is write once, run anywhere. The jar files are the same for all platforms. The shared library for the OCI driver is part of the Oracle client installation for each platform. You get it wherever you get the rest of the Oracle client install. See Are Oracle's JDBC zip and jar files identical on all platforms?

    As of 10.2, orai18n.jar is no longer provided as part of JDBC. It is still available on the JDBC download page or separately as part of the Oracle Globalization Kit. This file is also platform independent, so you can use it on all platforms.

    Back to Top

    DriverManager and DataSources

    What is the difference between the DriverManager and a DataSource?

    The first version of JDBC specified using the class java.sql.DriverManager to create Connections. This turned out to be insufficiently flexible and later versions of the JDBC spec define an additional way to create Connections using DataSources. We recommend that you use DataSources.

    To get a Connection using the DriverManager in JSE 1.5, first, you register the OracleDriver:

    DriverManager.registerDriver(new OracleDriver());

    You only have to register the driver once. You do not have to register the driver in JSE 6. JSE 6 has a mechanism that automatically registers the driver. In fact you should not register the driver although it doesn't do any serious harm.

    In JSE 1.5 after registering the driver or as your first step when using JSE 6 call getConnection to create a new connection. There are three getConnection methods:

    getConnection(String url) getConnection(String url, Properties info) getConnection(string url, String user, String password)

    each of which returns a connection.

    DataSources provide a more flexible way to create Connections. Once you have a DataSource, getting a connection from a DataSource is just as easy as using the DriverManager. DataSources were designed to be used with JNDI, but you don't have to use JNDI to use DataSources. DataSources can do things other than just create new connections. In particular, a DataSource can implement a connection cache. DataSources are now the preferred way to create a Connection.

    The simplest way to get a connection from a DataSource is as follows:

    ds = new oracle.jdbc.pool.OracleDataSource(); ds.setURL(myURL); conn = ds.getConnection(user, password);Back to Top
    Which connection cache should I use, OracleConnectionCacheImpl, the Implicit connection cache, or the Universal Connection Pool (UCP)?

    You should use the new Universal Connection Pool. This new connection caching mechanism is driver, protocol, and database independent. It supports non-JDBC connections and JDBC connections to databases other than Oracle. When using Oracle JDBC it provides advanced Oracle features including:

    • connection attributes to stripe and reuse connections
    • a connection cache manager per VM to manage one or more connection caches
    • abandoned connection timeout to reclaim idle checked out connections etc.
    • Runtime Connection Load Balancing to allocate work to the best performing instances

    The Oracle Implicit Connection Cache is still supported, but we encourage you to transition to the UCP in the near future. Note that the old connection cache, OracleConnectionCacheImpl was desupported in 11.1. See this question.

    Back to Top
    What is JDBC OCI Connection Pooling?

    JDBC OCIConnectionPool is for pooling multiple stateful sessions with few underlying physical connections to database. The connection is bound to the session only for duration of call. The pool element is the underlying physical connection. The application sessions can migrate (internally) to any underlying available physical connection.

    Each physical connection from pool has an additional internal session to server. Hence you can see more sessions on server.

    Back to Top
    What is connection striping?

    This is feature of the Implicit connection cache that enables retrieval of connections from the cache that are striped or labeled. Connection striping allows efficient reuse of cached connections, since applications don't have to reinitialize state every time. Connections are striped by setting attributes (name/value pairs) that are user defined.

    Back to Top
    What is UCP?

    UCP is the Universal Connection Pool. It is a single connection pool that handles all kinds of connections, JDBC, JCA, LDAP, and others. It supports non-Oracle JDBC drivers and JDBC connections to non-Oracle databases. When used for Oracle JDBC connections to Oracle databases it provides all the features of the old JDBC Implicit Connection Cache, and more. UCP is widely used in Oracle products. You should use it too. UCP is the focus for all future connection pooling development.

    UCP is not included in the JDBC jar files. It is in its own separate jar as it is used in many places without the Oracle JDBC drivers. You can download the UCP jar from from OTN.

    Back to Top
    What about the Implicit Connection Cache?

    The Implicit Connection Cache is still supported in 11.2. All of the features it provides, and more, are available in the UCP. We encourage you to use UCP for all new development and transition existing code to UCP as possible. While we don't intend to desupport the Implicit Connection Cache for sometime, we will be rewriting it so that it is just a wrapper around the UCP.

    Back to Top

    Connections

    How do I open a connection to a database?

    Once you have registered the driver (necessary only in JSE 1.5), you can open a connection to the database with the static getConnection method of the java.sql.DriverManager class. The type of the return value is java.sql.Connection. If you have created a DataSource you can get a connection by calling its getConnection method.

    Back to Top
    What are the various forms of getConnection for?

    DriverManager defines three different forms of the getConnection method:

    getConnection(String)
    All of the information needed to describe the desired connection is encoded into the URL String parameter.
    getConnection(String, Properties)
    Some of the information is coded into the URL String parameter. The rest is passed as key value pairs in the Properties parameter. This is the most powerful and flexible of the three forms. There are properties that can be set using this form that cannot be set any other way.
    getConnection(String, String, String)
    This is a convenience method that passes the username and password as arguments rather than encoding them into the URL. It is frequently used in simple programs.

    DataSource defines two getConnection methods:

    getConnection()
    This method returns a connection created using the URL, username, and password used to create the DataSource. This is the most commonly used form of getConnection in large applications.
    getConnection(String, String)
    This method returns a connection created using the URL used to create the DataSource, but with the username and password provided as arguments by the caller. You'll know when you need to use this one.
    Back to Top
    What is the form of a URL?

    The general form of a URL is

    jdbc:oracle:<drivertype>:<username/password>@<database>

    The <drivertype> is one of

    • thin
    • oci
    • kprb

    The <username/password> is either empty or of the form

    <username>/<password>

    Note that a URL like

    has an empty username and password whereas this URL

    jdbc:oracle:thin:@mydatabase

    does not specify a username and password. When using this form the username and password must be provided some other way.

    Back to Top
    What is the form of the <database> description?

    The <database> description somewhat depends on the driver type. If the driver type is kprb, then the <database> description is empty. If the driver type is oci and you wish to use a bequeath connection, then the <database> is empty. Otherwise ( thin or oci driver and not bequeath) the database description is one of the following:

    • //<host>:<port>/<service>
    • <host>:<port>:<SID>
    • <TNSName>

    The following URL connects user scott with password tiger to a database with service orcl (Important: see more on services) through port 1521 of host myhost, using the Thin driver.

    jdbc:oracle:thin:scott/tiger@//myhost:1521/orcl

    This URL connects to the same database using the the OCI driver and the SID inst1 without specifying the username or password.

    jdbc:oracle:oci:@myhost:1521:inst1

    This URL connects to the database named GL in the tnsnames.ora file using the Thin driver and with no username or password specified. The username and password must be specifed elsewhere.

    jdbc:oracle:thin:@GL

    Support for using TNSNAMES entries with the Thin driver is new in release 10.2.0.1.0. In order for this to work you must have configured the file tnsnames.ora correctly

    Back to Top
    How do I use the Properties argument?

    In addition to the URL, use an object of the standard Java Properties class as input. For example:

    java.util.Properties info = new java.util.Properties(); info.put ("user", "scott"); info.put ("password","tiger"); info.put ("defaultRowPrefetch","15"); getConnection ("jdbc:oracle:oci:@",info);

    All of the supported properties are defined in the JavaDoc for oracle.jdbc.OracleConnection. There are constants that define the property names. The JavaDoc for each constant describes what the property does and how to use it.

    In pre-11.1 versions of the driver the properties are defined in the JavaDoc for oracle.jdbc.pool.OracleDataSource.setConnectionProperties and in the Oracle JDBC Developer's Guide.

    Back to Top

    Don't I have to register the class OracleDriver with the DriverManager?

    You are no longer required to register the OracleDriver class for connecting with the Server-Side Internal driver, although there is no harm in doing so. This is true whether you are using getConnection() or defaultConnection() to make the connection.

    If you are using ojdbc6.jar and JSE 6 or later, you don't have to register the driver at all no matter which driver you are using. As of JSE 6, the standard Java Service Provider Interface registers the drivers automatically. Just call DriverManager.getConnection and the runtime will find the driver and register it for you.

    Back to Top

    What username and password should I use when connecting to the Server Internal Driver?

    Any user name or password you include in the URL string is ignored in connecting to the server default connection. The DriverManager.getConnection() method returns a new Java Connection object every time you call it. Note that although the method is not creating a new database connection (only a single implicit connection is used), it is returning a new java.sql.Connection object.

    Again, when JDBC code is running inside the target server, the connection is an implicit data channel, not an explicit connection instance as from a client. It should never be closed.

    Back to Top
    Is there any limit on number of connections?

    No. As such JDBC drivers doesn't have any scalability restrictions by themselves.

    It may be it restricted by the number of 'processes' (in the init.ora file) on the server. However, now-a-days we do get questions that even when the number of processes is 30, we are not able to open more than 16 active JDBC-OCI connections when the JDK is running in the default (green) thread model. This is because the number of per-process file descriptor limit exceeded. It is important to note that depending on whether you are using OCI or THIN, or Green Vs Native, a JDBC sql connection can consume any where from 1-4 file descriptors. The solution is to increase the per-process file descriptor limit.

    It is also learnt that, if you are using tns_entry in your URL to open a JDBC-OCI connection instead of a full TNS description, then there could be some limitations. This is because of a bug in SqlNet, that fails in opening tnsname.ora file. The solution is to use full TNS description in the URL instead of the TNS entry.

    Back to Top
    I'm getting OutofMemory Error when I set a higher default prefetch value.

    The solution is to increase the startup size (-ms) and maximum size (-mx) of memory allocation pool. This should be less of a problem with the 11.1 and later drivers as they use less memory than the 10g drivers. There is a more detailed discussion of this issue in the "JDBC Memory Management" white paper on the JDBC OTN web page.

    Back to Top
    What is a service connect string?

    Oracle is replacing the SID mechanism for identifying databases with a new services approach. This has been available in the database since 8.1.7. JDBC supports services in the connect URL. We strongly encourage everyone to transition from SIDs to services as quickly as possible as SIDs will be cease to be supported in one of the next few releases of the database.

    The basic format of a service URL is:

    jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service> jdbc:oracle:oci:[<user>/<password>]@//<host>[:<port>]/<service>

    Examples:

    jdbc:oracle:thin:@//myserver.com/customer_db jdbc:oracle:oci:scott/tiger@//myserver.com:5521/customer_db

    For more info see the JDBC User Guide.

    Back to Top
    How do I connect as SYSDBA or SYSOPER?

    The only way to do this is to use the Properties object when connecting, rather than specifying the username and password as strings. Put the username into the "user" property, and the password into the "password" property. Then, put the mode into the "internal_logon" property. Something like the following:

    Properties props = new Properties(); props.put("user", "scott"); props.put("password", "tiger"); props.put("internal_logon", "sysoper"); Connection conn = DriverManager.getConnection (url, props);

    When connecting as SYSDBA or SYSOPER using the Thin driver, the RDBMS must be configured to use a password file. See "Creating and Maintaining a Password File" in the "Oracle Database Administrator's Guide".

    Back to Top
    What encryption methods are supported by the Oracle JDBC drivers?

    The JDBC OCI driver supports the same algorithms as the database server.

    In 11.1 and 11.2 the JDBC Thin driver supports:

    • RC4_40
    • RC4_56
    • RC4_128
    • RC4_256
    • DES40C
    • DES56C
    • 3DES112
    • 3DES168
    • SSL
    • AES256
    • AES192
    • AES128
    Back to Top
    How do I turn on encryption and checksumming with the JDBC Thin driver?

    Assuming that the server is properly configured, use the following connection properties:

    Properties props = new Properties(); props.put("oracle.net.encryption_types_client", "(3DES168)"); props.put("oracle.net.encryption_client", "REQUIRED"); props.put("oracle.net.crypto_checksum_types_client", "(MD5)"); props.put("oracle.net.crypto_checksum_client", "REQUIRED");Back to Top
    What is proxy authentication?

    Proxy authentication is the ability to connect as a user through another user. For example proxy authentication enables the middle tier to authentication once to the database using a 'generic' account and then establish lightweight session on behalf of actual users. See the JavaDoc for oracle.jdbc.OracleConnection.openProxySession.

    Back to Top
    Do Oracle JDBC drivers support SSL?

    Yes, but the support is driver-specific. SSL encryption has been supported in the JDBC-OCI driver since Oracle JDBC 9.2.x, and is supported in the THIN driver starting in the 10.2.

    Back to Top
    Do the Oracle JDBC drivers support LDAP in the connection URL?

    Yes. JDBC THIN driver supports both regular LDAP and LDAP over SSL in the connection URL, for example, when using Oracle Internet Directory as an LDAP provider. Please refer to both the Oracle JDBC Developer's Guide and the Oracle Net Services Administrator's Guide for detail.

    Back to Top
    How can I use JDBC to connect to a database server behind a firewall?

    Generally, it is recommended that Oracle Connection Manager be used to proxy your connections through the firewall. You open up a port designated to be used by the Oracle Connection Manager, and let it handle the rest. You should not directly open up any port that the database listener is using, like port 1521.

    Please refer to the Oracle Net Services Administrator's Guide for how to configure Oracle Connection Manager.

    Back to Top

    Statements, PreparedStatements, CallableStatements

    What is defineColumnType and when should I use it?

    defineColumnType is an Oracle JDBC extension that provides increased performance in some cases. In prior versions of Oracle JDBC, all of the drivers benefited from calls to defineColumnType but beginning in 10.1.0 the Thin driver no longer needs the information provided. The Thin driver achieves maximum performance without calls to defineColumnType. The OCI and Server-Side Internal drivers still get better performance when the application uses defineColumnType.

    If your code is used with both the Thin and OCI drivers, you can disable the defineColumnType method when using the Thin by setting the connection property disableDefineColumnType to "true". This will make defineColumnType a NOOP. Do not set this connection property or else set it to "false" when using the OCI or Server-Side Internal drivers.

    Define column type may also be used to change the type of the data. Or to limit the size of variable length data.

    There is a new variation of this with a 4th parameter for form_of_use.

    Back to Top
    Does defineColumnType force conversions on the Server?

    No for the Thin driver and yes for the OCI and Server-Side Internal drivers.

    Back to Top
    Do the Oracle JDBC drivers provided a Bind by Name facility?

    Yes. See the JavaDoc for oracle.jdbc.OraclePreparedStatement. Look for the setXXXAtName methods. Also, oracle.jdbc.OracleCallableStatement supports binding arguments to PL/SQL procedures by the formal argument names. Look at the JavaDoc for the oracle.jdbc.OracleCallableStatement.setXXX(String, ...) methods.

    It is very important to note that setXXX(String, XXX) binds using the formal parameter name of the called stored procedure. setXXXAtName(String, XXX) binds using the name of the Oracle style ( :foo) parameter in the SQL string being executed. These are very different and can give very different results.

    Back to Top
    For setXXX methods in PreparedStatement how do the Oracle JDBC drivers determine the data type?

    Generally, there is a fixed data type associated with each setXXX method that is the data type that most sensibly corresponds to the type of the argument.

    Back to Top
    What happens if the type of the target parameter is not the same as the type assumed by the setXXX method?

    The data is shipped to the server in the format for the assumed data type and the server attempts to convert it to the type of the target parameter. If no conversion is possible, the server signals an error and the driver throws a SQLException at execute time. Back to Top


    Why do not the drivers do the conversion on the client side?

    For SQL statements we could first go to the server to get the type information and then do the conversions, but that would involve extra round trips. The code is optimized for the common case where the JDBC programmer uses the most appropriate API for the column type. Back to Top


    For inserting into a column in a table what are the byte data types?

    For byte data, there are three Oracle SQL types: RAW, LONG RAW and BLOB. RAW data is of limited length, is stored directly in a column, and is transmitted to the server in inline packets. LONG RAW data has a much larger limit (2 Gigabytes), is stored via a special mechanism alongside the row and is transmitted to the server via a streaming callback mechanism. BLOB data is effectively unlimited in length, is stored separately from the table with only a lob locator stored in the table, and is transmitted to the server is separate operations before the locator is stored into a column in the table.

    Back to Top
    For inserting into a column in a table what are the character data types?

    For byte data, there are three Oracle SQL types: VARCHAR2, LONG and CLOB. VARCHAR2 data is of limited length, is stored directly in a column, and is transmitted to the server in inline packets. LONG data has a much larger limit (2 Gigibytes), is stored via a special mechanism alongside the row and is transmitted to the server via a streaming callback mechanism. CLOB data is effectively unlimited in length, is stored in separately from the table with only a lob locator stored in the table, and is transmitted to the server is separate operations before the locator is stored into a column in the table.

    Back to Top
    Why so many different mechanisms?

    Mostly to cover a very wide range of data sizes in a way that is optimum, but some of the reason is historical. Oracle has a strong commitment to maintaining backward compatibility. The LONG RAW and LONG column types have been deprecated since 9i but are still in use. Back to Top


    What is new in 10g R2?

    The implementations of the setBytes, setBinaryStream, setString, setCharacterStream and setAsciiStream API's have been changed to automatically switch to the most appropriate data type based on the data size, the type of the sql statement and the driver used. For example to insert a very large string into a clob column, the setString API of PreparedStatement may be used. Back to Top


    What are the sizes for the setString, setCharacterStream, and setAsciiStream?
    Form Stmt Driver Lower Limit Upper Limit Bind mechanism Note
    All All All 0 0 Null  
    All SQL Client 1 char 32766 chars Direct  
                 
    All SQL Client 32767 chars 2147483647 bytes Stream  
    All SQL Client 2147483648 bytes 2147483647 chars Temp Clob  
    CHAR   Server 1 char 65536 bytes Direct 1, 2
    NCHAR     1 char 4000 bytes Direct  
    NCHAR     4001 bytes 2147483647 chars Temp Clob  
    CHAR     65537 bytes 2147483647 bytes Stream  
          2147483647 bytes 2147483647 chars Temp Clob  
                 
    All PL/SQL All 1 char 32512 chars Direct  
    All PL/SQL All 32513 chars 2147483647 chars Temp Clob  
    Back to Top
    What are the size limit for binary data via setBytes and setBinaryStream?
    Stmt Driver Lower Limit Upper Limit Bind mechanism Note
    SQL Client 32767 bytes 2147483648 bytes Stream  
               
    All All 0 0 Null  
    SQL All 1 2000 bytes Direct  
    SQL All 2000 bytes 2147483647 bytes Stream 1, 2
               
    PL/SQL All 1 32512 bytes Direct  
    PL/SQL All 32513 bytes 2147483647 bytes Temp blob  

    Notes:

    1. The server side internal driver cannot convert data for BLOB parameters of SQL sstatments that is larger than 2000 bytes. This limitation does not exist for PL/SQL statements. This may be used as a work around in many cases by wrapping the SQL in PL/SQL. For example a Java string insert into blob_tab (blob_col) values (?)

      could be replaced with

      begin Insert into blob_tab (blob_col) values (? ); end;
    2. The Oracle specific method setBytesForBlob may be used as an alternate workaround. This will be available in the first patch set for 10gR2
    Back to Top
    What are the size limits for the proprietary methods setBytesForBlob, setStringForClob in oracle.jdbc.OraclePreparedStatement?
    API FORM Stmt Driver Lower Limit Upper Limit Bind mechanism Note
    setBytesForBlob n/a All All 0 0 Null  
        All Client 1 byte 2000 bytes Direct  
        All Client 2001 bytes 21474836487 bytes Temp blob 2
                   
    setStringForClob All All All 0 0 Null  
      All All Client 1 char 32766 chars Direct  
      All All Client 32767 chars 2147483647 chars Temp clob  
      All All Server 1 char 4000 bytes Direct  
      All All Server 4001 bytes 2147483647 chars Temp clob 1

    Notes:

    1. The Oracle specific method setStringForClob may be used as an alternate workaround. This will be available in the first patch set for 10gR2
    2. The Oracle specific method setBytesForBlob may be used as an alternate workaround. This will be available in the first patch set for 10gR2
    Back to Top
    Why is this so complicated?

    The various mechanisms have limits either in the database, the database interface layers or in the JDBC driver itself.

    Back to Top
    Does switching bind types force additional server side parsing of the statement?

    Yes.

    Back to Top
    What about CallableStatements and procedures with IN OUT parameters?

    It is a requirement that the data types of the IN and OUT parameter be the same. The automatic switching will cause conflicts unless user code also changes the type in registerOutParameter. A better approach is to not use IN OUT parameters where this can be an issue. This can be done by changing the original procedure, adding a wrapper procedure or PL/SQL block that uses separate IN and OUT parameters.

    Back to Top
    Will the selection of polymorphic PL/SQL procedures change when the bind type changes?

    Yes. Consider that this can be exploited in your PL/SQL code.

    Back to Top
    What about existing code?

    Existing code will continue to work correctly. There is one change. Previously if an input exceeded the size limits of the API used, there would be an SQLException thrown when the setXXX API was called. Now, the exception will occur at execute time if at all.

    Back to Top
    For certain cases the driver is creating temporary lobs. Does it track these and free them?

    Yes, they are freed after the next execution of the statement or when the statement is closed.

    Back to Top
    We are using a variable width character set such as UTF8. Does the driver correct for the actual byte length of a sequence of characters?

    Yes. Except for the decision to switch to Clob for the largest strings which is made assuming the max size.

    Back to Top
    Is it a good idea to use e.g. setString for a really huge string?

    It is probably not a good idea to create the really huge string in the first place. See your Java Virtual Machine vendors documentation for the effects that very large objects have on the Java memory management system.

    Back to Top
    LONG RAW and LONG column types are deprecated. Why are there new uses of the setXXXStream API's?

    The stream API's are not deprecated. They offer better performance for some operations than the LOB API's and will be retained.

    Back to Top
    The LOB API's are much more flexible, right?

    Absolutely! The LOB API's allow random access to any part of the LOB. Consider using these where appropriate.

    Back to Top
    Why can't I create a PreparedStatement that does select * from tab where id in (?, ?, ?, ...)?

    The problem is that the RDBMS does not support bind parameters for the elements in the IN clause. This is a limitation of the database, not the driver.

    The best way we have found to implement this kind of query is described on the Ask Tom web site.

    Back to Top

    ResultSets

    What does "Exhausted Resultset: prepare_for_new_get" mean?

    This error happens if you try to use a ResultSet after you close it. It also happens if you close the statement that created the ResultSet.

    ResultSet rset = stmt.executeQuery ("select ROWID from EMP"); ... rset.close (); // or stmt.close (); rset.getString (1);Back to Top
    Why do I have to close ResultSets?

    The original JDBC spec required that Connections, Statements, and ResultSets be closed when no longer reachable. This requires the use of finalizers. Finalizers have a huge performance penalty for all aspects of an application that run in a JVM with any finalizers. Sun strong discourages their use. Automatic closure would require the use of finalizers which would be bad for all customers whether they relied on automatic closure or not. This is not an acceptable tradeoff.

    To the best of our knowledge, no JDBC driver from any vendor implements or ever has implemented automatic closure for exactly the reason outlined above. This requirement has been removed from the spec although some remnants of that wording appear in a couple of places. It also appears in the JDBC Tutorial. The tutorial, while informative and helpful, is not definitive. It has not been updated in years. The JDBC 4.0 specification absolutely does not require automatic closure.

    ResultSets, Statemnents, and Connections all take up resources on both the client side and the server side. So long as those objects are open the associated resources are allocated. The resources are released only when the objects are closed. Failing to close ResultSets, Statements, and/or Connections will leak resources and impact the performance of your app.

    Closing a Connection closes all of the associated Statements. Closing a Statement closes all of the associated ResultSets. So, if you are through with a Connection you can just close it and all of the Statements and ResultSets will be closed. This is acceptable programming practice. Better practice is to explicitly close Statements and ResultSets in finally blocks. This makes your application more robust and less likely to leak resources as it evolves to meet changing requirements.

    PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); try { rs = ps.executeQuery(); while (rs.next()) { // process row } } finally { if (rs != null) rs.close(); } } finally { if (ps != null) ps.close(); }Back to Top

    Simple Data Types

    What is going on with DATE and TIMESTAMP?

    This section is on simple data types. :-)

    Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to java.sql.Timestamp. This made a certain amount of sense because the Oracle DATE SQL type contains both date and time information as does java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat problematic as java.sql.Date does not include time information. It was also the case that the RDBMS did not support the TIMESTAMP SQL type, so there was no problem with mapping DATE to Timestamp.

    In 9.2 TIMESTAMP support was added to the RDBMS. The difference between DATE and TIMESTAMP is that TIMESTAMP includes nanoseconds and DATE does not. So, beginning in 9.2, DATE is mapped to Date and TIMESTAMP is mapped to Timestamp. Unfortunately if you were relying on DATE values to contain time information, there is a problem.

    There are several ways to address this problem in the 9.2 through 10.2 drivers:

    • Alter your tables to use TIMESTAMP instead of DATE. This is probably rarely possible, but it is the best solution when it is.

    • Alter your application to use defineColumnType to define the columns as TIMESTAMP rather than DATE. There are problems with this because you really don't want to use defineColumnType unless you have to (see What is defineColumnType and when should I use it? ).

    • Alter you application to use getTimestamp rather than getObject. This is a good solution when possible, however many applications contain generic code that relies on getObject, so it isn't always possible.

    • Set the V8Compatible connection property. This tells the JDBC drivers to use the old mapping rather than the new one. You can set this flag either as a connection property or a system property. You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.

      java -Doracle.jdbc.V8Compatible="true" MyApp

    Oracle JDBC 11.1 fixes this problem. Beginning with this release the driver maps SQL DATE columns to java.sql.Timestamp by default. There is no need to set V8Compatible to get the correct mapping. V8Compatible is strongly deprecated. You should not use it at all. If you do set it to true it won't hurt anything, but you should stop using it.

    Although it was rarely used that way, V8Compatible existed not to fix the DATE to Date issue but to support compatibility with 8i databases. 8i (and older) databases did not support the TIMESTAMP type. Setting V8Compatible not only caused SQL DATE to be mapped to Timestamp when read from the database, it also caused all Timestamps to be converted to SQL DATE when written to the database. Since 8i is desupported, the 11.1 JDBC drivers do not support this compatibility mode. For this reason V8Compatible is desupported.

    As mentioned above, the 11.1 drivers by default convert SQL DATE to Timestamp when reading from the database. This always was the right thing to do and the change in 9i was a mistake. The 11.1 drivers have reverted to the correct behavior. Even if you didn't set V8Compatible in your application you shouldn't see any difference in behavior in most cases. You may notice a difference if you use getObject to read a DATE column. The result will be a Timestamp rather than a Date. Since Timestamp is a subclass of Date this generally isn't a problem. Where you might notice a difference is if you relied on the conversion from DATE to Date to truncate the time component or if you do toString on the value. Otherwise the change should be transparent.

    If for some reason your app is very sensitive to this change and you simply must have the 9i-10g behavior, there is a connection property you can set. Set mapDateToTimestamp to false and the driver will revert to the default 9i-10g behavior and map DATE to Date.

    Back to Top
    What is the longest value I can bind?
    Method Column Type Maximum length
    setBytes LONG 4k bytes
    setBytes LONG RAW 2G bytes
    setString LONG 32k chars ( SetBigStringTryClob="false")
    4k chars ( SetBigStringTryClob="true")
    setString CLOB 2G chars

    In 9.2, setString() on a LONG can insert up to 64k characters with the OCI driver, and 4k characters with the Thin driver. In 10.1.0 we changed the limit for both drivers to 32k characters. We understand that reducing the limit for OCI from 64k to 32k may be a problem to some customers. However, considering the substantial performance improvement that this change made possible, and that Oracle is strongly recommending our customers to migrate from LONG to CLOB, we decided that the architectural change is necessary.

    We recommend customers who need setString() to work over 32k characters to migrate from LONG to CLOB.

    Back to Top
    Why is the result of reading a TIMESTAMP WITH TIME ZONE different?

    The old behavior was incorrect. See bug 4322830.

    The old behavior was to construct a Timestamp that would print the same value as the database value. But since Timestamp is in the UTC time zone this would give a Timestamp value that was offset from the correct value. 8:00am January 1, 2007 UTC is not the same as 8:00am January 1, 2007 PST. They represent different points in time.

    If you read 8:00am January 1, 2007 PST in the database, the 9i and 10g drivers would construct a Timestamp with the value 8:00am January 1, 2007 UTC. This value would print "correctly", that is it would print as "8:00am January 1, 2007", but it obviously represented the wrong instant in time. The 11.1 drivers fix this bug.

    Back to Top



    Advanced Data Types (Objects)

    How should I create instances of ADTs?

    JDBC 4.0 introduced factory methods on the Connection interface for creating instances of ADTs. This is a far better api than using constructors. We strongly encourage you to use the factory methods as much as possible. We will deprecate use of the constructors very soon and would like to desupport them as soon as possible.

    Since the standard factory methods are introduced in JDBC 4.0, those methods are only available in the JSE 6 drivers (ojdbc6.jar). For creating Oracle proprietary types, the factory methods are defined in OracleConnection for both JSE 5 and JSE 6 (ojdbc5.jar and ojdbc6.jar). Again we strongly encourage you to use the factory methods.

    Back to Top



    Why isn't the standard factory method createArrayOf supported?

    The SQL standard array type is anonymous, that is the type "array of foo" does not have a name. Only the element type is named. In Oracle SQL the array type is named. In fact anonymous array types are not supported. So, the JDBC 4.0 standard factory method takes the element type as its argument and creates an instance of an anomyous array type. The Oracle JDBC drivers define an Oracle proprietary method, createArray, which takes the name of an array type and returns an instance of that named array type. This is required by the way Oracle SQL is defined. At present the Oracle database cannot support the JDBC 4.0 standard createArrayOf method.

    Back to Top



    BFILEs, BLOBs, CLOBs

    What is DBMS_LOB.ERASE doing?

    It's just "clearing" a segment of the clob. It does *not* shorten the CLOB. So the length of the CLOB is the same before and after the ERASE. You can use DBMS_LOB.TRIM to make a CLOB shorter.

    Back to Top



    Can I use oracle.sql.CLOB.putChars()?

    Yes, you can but you have to make sure that the position and length arguments are correct. You can also use the recommended OutputStream interface which in turn will call putChars for you.

    Back to Top
    OCI provides function to manipulate a CLOB CharSetId. What is the JDBC equivalent?

    In JDBC CLOBs are *always* in USC2, which is the Oracle character set corresponding to the Java "char" type. So there is no equivalent for the OCI CLOB CharSetId.

    Back to Top
    Is writing into BLOBS is slower than inserting LONG RAWs ?

    It depends. When writing small values, less that 10K, LONG RAWs are faster. When writing larger values, the difference disappears.

    Back to Top
    Why am I getting an ORA-03127 error when getting the LobLength in the Stream sample code?

    This is the correct behavior. LONG columns are not 'fetched' in-place (aka in-row). They are fetched out of place and exists in the pipe until you read them explicitly. In this case, we got the LobLocator (getBlob()) and then we are trying to get the length of this LOB before we read the LONG column. Since the pipe is not clear we are getting the above exception. The solution would be to complete reading the Long column before you do any operation on the BLOB.

    Back to Top
    When I get a CLOB from the database and then update it, sometimes my changes don't appear in the database. Why?

    Oracle LOBs use value semantics. When you update a LOB you must write the LOB back to the database to be sure of seeing the changes. For technical reasons, sometimes your changes are saved even though you do not write the LOB, but you cannot predict when that is the case, so you should always write the LOB.

    Back to Top

    REF types

    How can I pass an oracle.sql.REF between two different JDBC clients (EJBs, JDBC Clients, etc.)? As the REF class is not serializable?

    That used to be true, but no longer. REF is now serializable.

    The following note may still be valuable if you are using an older version of the Oracle JDBC drivers where REFs are not serializable.

    The important constituents of the REF class are the byte array that represent the object reference and the fully qualified name of the object type. You can use a class like the following "SomeREF" class to hold the bytes and type name from an object REF. This class is serializable. It can recreate the REF with its "toREF" method that needs a JDBC Connection as a parameter.

    public class SomeREF implements java.io.Serializable { String typeName; byte[] bytes; public SomeREF (oracle.sql.REF ref) throws SQLException { this.typeName = ref.getBaseTypeName (); this.bytes = ref.getBytes (); } public oracle.sql.REF toREF (Connection conn) throws SQLException { return new oracle.sql.REF (new oracle.sql.StructDescriptor (typeName,conn),conn, bytes); } }Back to Top
    How can I create a new REF?

    You can execute queries against a table that contains REF to Oracle8 Object types and the REF will be materialized as Java oracle.sql.REF objects by JDBC. JDBC does not support creating new REF from scratch. You have to go to the database and insert the new REF in SQL. Then you have to select the REF back and return it to the client.

    It is easier to do that with a PL/SQL block. For example if you have the following tables:

    create or replace type point as object (x number, y number); create table point_values_table of point; create table point_ref_table (p ref point); You can insert a new point value in point_values_table, a new ref to it in the point_ref_table and return the REF to the client with following code: oracle.jdbc.driver.OracleCallableStatement call = (oracle.jdbc.driver.OracleCallableStatement) conn.prepareCall ("declare x ref point; " + "begin insert into point_values_table p values (point(10, 20))" + " returning ref(p) into x; " + " ? := x; " + "end;"); call.registerOutParameter (1, oracle.jdbc.driver.OracleTypes.REF,"SCOTT.POINT"); call.execute (); oracle.sql.REF ref = (oracle.sql.REF)call.getObject (1);Back to Top

    OPAQUE types

    What are OPAQUE types?

    OPAQUE types have binary data and supporting methods which are defined in a server native code library. These are available only for Oracle internal use. Back to Top


    Row Sources

    When I set the scrollability attribute after execute, it does not have any effect on the scrollability of the RowSet. Why?

    A bean's properties can be classified as:

    • object creation properties
    • runtime properties
    Object creation properties should be set before the creation of the object since, they are the key properties for creating the object. The runtime properties can be set at anytime and they alter the behavior of the bean at runtime.

    Scrollability, user name, password are all object creation propertes. Auto-commit status, prefetch count et al, are all runtime properties. Typically the background object comes to life during execute/setCommand so all Statement creation attributes should be set before that. Since, a connetion object is required for creating a Statement URL, username, Password et al required for creating a should be set before setting the Command.

    Back to Top
    Can I serialize the RowSet object to a flat file even if the RowSet contains streams?

    Yes, the serializable streams allow you to serialize the stream Object on to any serializable media like a flat file, network connection, et al. This feature applies only to CachedRowSet. It is possible to create a CachedRowSet on one machine where the Jdbc drivers are present and then move it on to a remote client where only the rowset binaries are present and not the driver binaries. The remote client could alter the RowSet by insert, delete or update. Then send it back to the place where the Jdbc drivers & the RowSet binaries are present to synchronize the modified values into the database.

    Back to Top

    Thin Driver

    Can the Thin JDBC Driver be used to develop Java applications?

    Yes, the thin JDBC Driver can be used to develop both Java applets and Java applications. Since it is written completely in Java it is downloadable and therefore can be used with Java applets. It can also be used for Java applications but only if you are using TCP/IP. Unlike the JDBC OCI driver, the Thin JDBC driver only works with TCP/IP-based networks. Users who are running applications on non-TCP/IP networks are encouraged to use the JDBC OCI driver.

     

    Back to Top
    With which Browsers has the thin JDBC Driver been tested?

    The thin JDBC Driver has been tested with Netscape Navigator 4.06 and 4.5. It has also been tested with Internet Explorer 3.0. However, in order to use the thin JDBC Driver with Netscape Navigator 3.0, the web server and the database server need to be located on the same machine. This is because of security considerations associated with JDK 1.0.2 signed applets.

    The communication between an applet that uses the Thin JDBC driver and the Oracle database happens over Java TCP/IP sockets. The connection can only be made if the web browser where the applet is executing allows a sockets connection to be made. In a JDK1.0.2-based Web browser, such as Netscape 3.0, an applet can only open sockets to the host from which it was downloaded. Therefore, to avoid violating JDK 1.0.2. security considerations, the Oracle Database server must be physically located on the same machine as the web server.

    However, this restriction has been eliminated for JDK 1.1 signed applets in which case the web server and the Oracle database can be deployed on two separate machines. In a JDK 1.1.1 based web browser, such as Netscape 4.0, an applet can request socket connection privileges and, if the user grants them, the applet can connect to the database running on a different host from the web server host.

    Back to Top

    OCI Driver

    Known bug: JDBC OCI and SSL with Java green threads.

    The JDBC-OCI driver can use SSL depending on what threading model the Java installation is using. If your Java installation is configured to use "green threads", then SSL will not work. However, if your Java installation is configured to use "native threads", then SSL should work fine. This is a known bug.

    The JDBC-Thin driver cannot use SSL yet, but can use ANO encryption instead.

    Back to Top
    Does OCI Driver work Microsoft's Java VM ?

    In 8.1.6 SDK and before, no. We used to use NMI (Native Method Invocation) that is not supported by Microsoft. They have their own version called RNI (Raw Native Interface). Same issue with IBM's Visual Age.

    However, the 8.1.6 driver and above don't have this limitation as they have been ported to JNI 1.1.

     

    Back to Top
    I'm getting Unsatisfied Link Error with Oci 8 Driver ?

    First, make sure that the jdbc-oci shared object ( libocijdbc8 or liboci80Xjdbc.so etc.) and ${ORACLE_HOME}/lib are in your path. Then, try the Test sample program available in the JDBC samples directory. Sometimes, even after the shared object is loaded successfully, you may get errors such as make_c_state symbol not found. This may happen if your CLASSPATH has classes.zip from JRE 1.1.7 or JDK 1.1.6 and your running java binaries from jdk 1.1.3 or so. Make sure everything ( LD_LIBRARY_PATH, CLASSPATH, java binaries) is in sync.

    Back to Top
    Can the JDBC OCI Driver be used with Java applets?

    No, the JDBC OCI Driver is not designed for use with Java applets but designed for client-server Java applications and Java-based middle tiers. The Thin JDBC Driver is targeted for Java applet developers.

    The JDBC OCI driver transforms calls from Java to C [since the driver must use a layer of C in order to make calls to the OCI], the driver is written in a combination of Java and C which precludes it from being downloadable.

    Further, JDBC OCI driver also requires installation of the OCI libraries, SQL*Net, CORE libraries and other required support files on the machine on which the JDBC driver is installed i.e. on each of the client machines or the middle-tier Java application server.

    Back to Top

    Server Internal Driver

    When should I use the Server Internal Driver?

    You should use the Server Internal Driver when you access the database in a Java Stored Procedure. A Java Stored Procedure is a Java method that executes inside the Oracle RDBMS just like PL/SQL executes in the RDBMS. Because it is executing in the RDBMS, it is necessarily executing in a database session. The Server Internal Driver connection is the handle to that database session. So, if your code is running in a Java Stored Procedure and you want to access the database, use the Server Internal Driver, except on those rare occasions when you should use the Server Thin Driver.

    Back to Top

    Server Thin Driver

    When should I use the Server Thin Driver?

    In a Java Stored Procedure you should usually use the Server Internal Driver. It connects to the same session in which the stored procedure is executing. However, on occasion you might want to connect to another database or to a new session in the same database. In either of those two cases you would use the Server Thin Driver.

    Back to Top

    Errors

    DriverManager.getConnection gives the Error: "No suitable driver"

    Make sure that the driver is registered and that you use a connection URL consistent with your JDBC driver. See Using Oracle's JDBC Drivers for the correct values.

    Back to Top
    Error Message: "Unimplemented Method Interface"

    You are using a a JDK 1.0.2 driver with JDK 1.1.1. Use classes102.zip for JDK 1.0.2 and classes111.zip for JDK 1.1.1.

    Back to Top
    Error Message: "UnsatisfiedLinkError with OCI driver"

    When using Win NT or Win95, the Java Virtual Machine complains that it cannot load OCI73JDBC.DLL, when one of the DLLs called by OCI73JDBC.DLL cannot be loaded. The JDBC OCI drivers use shared libraries that contain the C code portions of the driver. The library is OCI73JDBC.DLL for the Oracle7 client program. The shared library is normally installed in [ORACLE_HOME]\BIN when you install the JDBC driver from the distribution. Make sure that directory is in your PATH. Read the Installation section of the documentation for more details.

    The shared library also depends on other libraries. If any of those DLLs are missing, you will end up with an error saying OCI73JDBC.DLL is missing. JDBC OCI7 requires the following Oracle7 files: CORE35.DLL, CORE35O.DLL, NLSRTL32.DLL, and ORA73.DLL

    The Java Virtual Machine (JavaSoft JDK) is JAVAI.DLL.

    The Microsoft Visual C++ runtime is MSVCRT.DLL, MSVCRTI.DLL, MSVCRT20.DLL, and MSVCRT40.DLL.

    You can find the list of dependent DLLs by going to the Windows Explorer program, right-clicking on the DLL, and choosing Quick View. The Quick View screen shows, among other things, the Import Table which lists the dependent DLLs. You can reinstall missing required support files from the Oracle installation CD. Please install "Required Support Files 7.3.4", "SQL*Net Client 2.3.4 " and "Oracle TCP/IP Protocol Adapter 2.3.4".

    Back to Top
    Error Message: "ORA-1019: unable to allocate memory."

    You are using the OCI8 driver in an Oracle7 client installation. Use the OCI7 driver.

    Back to Top
    Error Message: "ORA-01000: maximum open cursors exceeded"

    The number of cursors one client can open at a time on a connection is limited (50 is the default value). You do need to explicitly close the statement, by using the method stmt.close() in order to close and freeup the cursors.

    If you don't close these cursors explicitly, you will get this error eventually. Simply increasing the "OPEN_CURSORS" limit can help you avoid the problem for a while, but that just hides the problem, not solve it. It is your responsibility to explicitly close out cursors that you no longer need.

    Back to Top
    Error Message: "ORA-01002: fetch out of sequence"

    A JDBC Connection by default has the AutoCommit turned ON. However, to use a SQL that has 'for update' you need to have autoCommit to be turned OFF.

    Hence, the solution is to set autocommit to false.

    Back to Top
    Error Message: "ORA-12705: invalid or unknown NLS parameter value specified"

    Try explicitly setting NLS_LANG. If NLS_LANG is not set or is correctly set, then you may have a client other than Oracle7.3.4. Install Oracle7.3.4 on the client.

    Back to Top
    Error While Trying to Retrieve Text for Error ORA-12705.

    There is no Oracle installation on the client or the installation did not complete properly. If you haven't already done so, use the regular Oracle Server install CD and do a "Oracle Client" installation, to put the necessary software on your client machine. If you have already done this, check that the installation did actually complete properly, and if necessary, remove and reinstall.

    Note that you can get this error by doing a client install, and then forgetting to set ORACLE_HOME. If you have not the ORACLE_HOME environment variable, then simply setting/exporting that environment variable should fix the problem, without having to reinstall the client side.

    Back to Top
    Error Message: "Invalid driver designator"

    You are using an older version of SQL*Net. The version of Oracle on the client may be older than Oracle7.3.4. Install Oracle7.3.4 on the client.

    Back to Top
    Cant get JDBC Drivers to work with the Oracle WebServer

    If you are using Oracle WebServer v2.1 running on Windows NT, you need the patch release 2.1.0.3.2 to be able to use the JDBC drivers in the Java cartridge.

    Back to Top
    Error Message: FileNotFound Exception

    With the Thin JDBC driver, when I run my applet using Appletviewer on the local machine where the classes111.zip file is present in the CLASSPATH, my applet runs correctly. However, when I run it from a remote machine, I get a FileNotFoundException: oracle.jdbc.driver.OracleDriver not found.

    The best solution is to create your own zip file, which must be un-compressed, that contains all the JDBC classes plus the classes of your application. Then in your applet you set your ARCHIVE value to point to that zip file.

    Back to Top
    Getting Security Exceptions from Netscape when Connecting to Oracle

    With Oracle7 and Netscape 3.0 an applet using the JDBC Thin driver can only connect to an Oracle database on the same host as the web server it was downloaded from. You can solve this problem by upgrading to Oracle8 or Netscape4.0. Read the Applet section of the JDBC Documentation for more information.

    Back to Top
    Error Message: The JDBC Thin Driver Gives Me "invalid character" Errors for Unicode Literals

    The JDBC Thin driver requires double quotes around literals that contain Unicode characters. For example:

    ResultSet rset = stmt.executeQuery ("select * from \"\u6d82\u6d85\u6886\u5384\"");Back to Top
    INSERT or UPDATE operations are slow

    By default the driver commits all INSERTs and UPDATEs as soon as you execute the statement. This is known as autoCommit mode in JDBC. You can get better performance by turning autoCommit off and using explicit commit statements. Use the setAutoCommit entrypoint of the Connection class to turn off autoCommit:

    connection.setAutoCommit(false); See Batching Updates in the JDBC Documentation for information about the Oracle extensions for batching calls to INSERT and UPDATE. Batching these commands can achieve even more speed than turning off autoCommit.Back to Top
    Error Message: "Connection reset by peer"

    Typically, this is the error you will see if the server crashes out while you are connected to it. You may be in the process of establishing a connection, or you could be midway through an established connection. Either way, you should check the server side log files to see what errors and stack dumps were thrown on the server.

    Note that this error is different to what happens if you try to connect to a wrong/invalid port or even machine, you would get a different error, not this one. Its also different to the error you would get if the server is down and not accepting connection requests.

    Back to Top
    Why do I get FileNotFoundException when using jdb and JDK 1.4.2?

    When using jdb to debug JDBC programs with Sun JDK 1.4.2 you will get an error Exception occurred: java.io.FileNotFoundException with java.sql.Timestamp.valueOf near the top of the stack. This is the result of a JDK bug where an exception in native code in the jre is not recognized by JPDA. The workaround is to do a "cont" command in jdb.

    Back to Top
    What does "Protocol Violation" mean?

    The Thin driver throws this exception when it reads something from the RDBMS that it did not expect. This means that the protocol engine in the Thin driver and the protocol engine in the RDBMS are out of synch. There is no way to recover from this error. The connection is dead. You should try to close it, but that will probably fail too.

    If you get a reproducible test case that generates this error, please file a TAR with Oracle Global Support. Be sure to specify the exact version numbers of the JDBC driver and the RDBMS, including any patches.

    Back to Top

    Demo Programs

    Are there any JDBC demo programs?

    Yes. Look in $ORACLE_HOME/jdbc/demo/demo.tar on UNIX systems and $ORACLE_HOME/jdbc/demo/demo.zip on Windows systems.

    Back to Top
    How do I run the demos?

    Unzip the demo.tar or demo.zip file. You'll see a Samples-Readme.txt file. Please read this file first to get an overview of JDBC demos, then run Makefile on UNIX or invoke rundemo.bat on Windows.

    Back to Top
    What should I do when error happens when I run a demo?

    The JDBC demos should run without error. If you get an error that probably means a problem in your configuration. Check the following:

    • classpath
    • correct jdk versions
    • refer to Samples-Readme.txt, Makefile, and each .java file for pre-testing requirements.
    Back to Top

    Oracle JDBC Trace Facility

    What is the JDBC Trace Facility?

    The JDBC Trace Facility is a runtime debugging aid built into previous versions of Oracle JDBC. When enabled, it prints messages about the execution of the Oracle JDBC driver. Typically these messages include method entry, parameter values, significant internal state, internal errors, method exit, and return values.

    As of 10.1.0, the Oracle Trace Facility is only supported in classes12_g.jar and classes12dms_g.jar. All Oracle JDBC drivers supporting JDK 1.4 and later use the built in trace facility in java.util.logging. See the section on java.util.logging for info on how to get trace info when using JDBC 11 or ojdbc14_g.jar or ojdbc14dms_g.jar.

    If you are having difficulty with your JDBC application, you might find the trace helpful. Most of the messages are about internal JDBC methods and so may be obscure. Still, you might get some help. I would suggest setting the trace volume to 1 to begin with.

    If you think there is a bug in JDBC, the trace might help us in supporting you. In this case leave the trace volume at the default. Because of the large output this produces, you will need to either trace a small test case, or just trace a limited part of a larger application. Be sure and include the appropriate code prior to the failure.

    Back to Top
    How do I turn on the old JDBC trace?

    See the section on java.util.logging for info on how to get trace info when using JDBC 11.

    In order to use the JDBC Trace Facility, you must use a debug jar file: classes12_g.jar or classes12dms_g.jar. If you attempt to use the trace while using one of the other jar or zip files, you will get either an error message or no output at all.

    There are two ways to control the trace: programatically or via properties. The programmatic api allows you to enable or disable the trace and change other properties while your application is executing. Given the often high volume of trace data, it is often a good idea to only enable the trace for particularly suspect bits of code. If it is not easy to change the application source, you can control the trace via properties. These properties are read once at application startup and are not read again. You can use both the properties and the api at the same time. The properties set the initial state and the api modifies that state.

    The simplest way to turn the trace on programatically is to call

    oracle.jdbc.driver.OracleLog.startLogging(); This sends the trace to System.out. To turn it off call oracle.jdbc.driver.OracleLog.stopLogging(); You can also turn on the trace by setting the system property oracle.jdbc.Trace to "true". java -Doracle.jdbc.Trace=true MyApp Setting any of the other JDBC Trace Facility properties described below implicitly sets oracle.jdbc.Trace to "true". Back to Top
    How do I control the volume of trace output?

    See the section on java.util.logging for info on how to get trace info when using JDBC 11.

    The JDBC Trace Facility can produce a lot of output. The simplest way to control the volume is to only turn on the trace when needed.

    oracle.jdbc.driver.OracleLog.startLogging(); myApp.suspectCode(); oracle.jdbc.driver.OracleLog.stopLogging(); Often this is not possible. You can also reduce the number of trace messages by setting the trace volume. oracle.jdbc.driver.OracleLog.setLogVolume(1); The default value is 2. The maximum value is 3, but that currently does not produce a whole lot more than 2. 1 is much less than the default.

    You can control the size of each line either by setting an explicit line size, or by changing which fields are printed on each line. To change the maximum line length

    oracle.jdbc.driver.OracleLog.setMaxPrintBytes(100); or java -Doracle.jdbc.MaxPrintBytes=100 MyApp

    To control which fields are printed you can set the property oracle.jdbc.PrintFields.

    java -Doracle.jdbc.PrintFields=none MyApp The valid values are:
    • none--just prints the message
    • default
    • thread--same as default plus the thread name
    • all
    Back to Top
    Where does the trace output go?

    See the section on java.util.logging for info on how to get trace info when using JDBC 11.

    By default the trace output goes to System.out. You can send it elsewhere either with the property oracle.jdbc.LogFile

    java -Doracle.jdbc.LogFile=/tmp/jdbc.log MyApp or by calling the setLogStream api. oracle.jdbc.driver.OracleLog.setLogStream(System.err); Setting the log stream starts the trace as well. You can turn the trace off by setting the log stream to null.Back to Top
    How can I turn off DMS in a DMS enabled jar?

    There is a system property oracle.dms.console.DMSConsole. If that property is not set then DMS is active. If it is set to oracle.dms.instrument_stub.DMSConsole, then a stub implementation is used, which effectively disables DMS. One way for an application to disable it would be to call

    System.setProperty( "oracle.dms.console.DMSConsole", "oracle.dms.instrument_stub.DMSConsole"); before executing any DMS code. Another way would be to use the -D option with the Java VM. java -Doracle.dms.console.DMSConsole=oracle.dms.instrument_stub.DMSConsole MyAppBack to Top

    Development Tools and Environments

    Can I debug JDBC programs with Symantec Visual Cafe?

    Visual Cafe is no longer supported.

    Back to Top
    Can I debug JDBC programs with Microsoft's Visual J++?

    Visual J++ is no longer supported.

    Back to Top

    Supported Features

    Can the JDBC Drivers access PL/SQL Stored Procedures?

    Yes, both the Oracle JDBC OCI Driver and the Thin JDBC Driver support execution of PL/SQL stored procedures and anonymous blocks. and anonymous blocks. They support both SQL:2003 escape syntax and Oracle escape syntax. The following PL/SQL calls are available from both of Oracle JDBC Drivers:

    • SQL:2003 Syntax CallableStatement cs1 = conn.prepareCall ("{call proc (?,?)}"); CallableStatement cs2 = conn.prepareCall ("{? = call func (?,?)}");
    • Oracle Syntax CallableStatement cs1 = conn.prepareCall ("begin proc (:1,:2); end;"); CallableStatement cs2 = conn.prepareCall ("begin :1 := func (:2,:3); end;");
    Back to Top
    Do the JDBC Drivers support streaming?

    Yes, both the Oracle JDBC OCI Driver and the Thin JDBC Driver support streaming of data in either direction between the client and the server. They support all stream conversions - binary, ASCII, and Unicode. To get more information, read the stream tutorial in the Oracle JDBC Driver documentation.

    Back to Top
    Do the JDBC Drivers support multibyte character sets?

    Yes, both the Oracle JDBC OCI Driver and the Thin JDBC Driver support multibyte character sets - they can both access databases that use any Oracle character set. They convert multibyte characters to Unicode 1.2. The JDBC OCI Driver has been tested and supports all European character sets and all Asian character sets including Chinese, Japanese and Korean.

    Back to Top
    Do the JDBC Drivers work with firewalls?

    Yes, both the JDBC OCI driver and the thin JDBC driver can work in both an Intranet and in an Extranet setting. In an Extranet deployment, the drivers can be used with most industry leading firewalls which have been SQL*Net certified. Today, the following firewall vendors have certified their Firewalls with SQL*Net:

    • Stateful Inspection Firewalls: Firewalls from Checkpoint, SunSoft, and CISCO Systems.
    • Proxy-based Firewalls: Firewalls from Milkyway Networks, Trusted Information Systems, Raptor, Secure Computing Corporation, and Global Internet.
    Back to Top
    Do Oracle's JDBC drivers support PL/SQL tables/result sets/records/booleans?

    No. It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL types TABLE (now known as indexed-by tables), RESULT SET, RECORD, or BOOLEAN. There are currently no plans to change this. Instead people are encouraged to use RefCursor, Oracle Collections and Structured Object Types.

    As a workaround, you can create wrapper procedures that handle the data as types supported by JDBC.

    For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN, or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, you can create a stored procedure that handles a record in its individual components (such as CHAR and NUMBER). To wrap a stored procedure that uses PL/SQL tables, you can break the data into components or perhaps use Oracle collection types.

    Here is an example of a PL/SQL wrapper procedure MY_PROC for a stored procedure PROC that takes a BOOLEAN as input:

    PROCEDURE MY_PROC (n NUMBER) IS BEGIN IF n=0 THEN proc(false); ELSE proc(true); END IF; END; PROCEDURE PROC (b BOOLEAN) IS BEGIN ... END;Back to Top
    Is failover supported?

    Yes. When you are connecting to a RAC server, Fast Connection Failover provides rapid response to failure events. This new High-Availability feature is driver independent and works in conjunction with the Implicit connection cache and RAC to provide maximum availability of connections in the cache. This is achieved by processing RAC's down events to remove invalid connections and up events to load balance existing connections.

    If you are using the OCI driver and all you need is query fail-over, you might consider TAF. TAF primarily facilitates query failover in an application. It is not a general fail-over mechanism. Note that Fast Connection Failover and TAF can't be used together. Only one may be enabled and used at a time.

    Back to Top
    How do the JDBC drivers support Oracle ROWID datatypes? What does this have to do with getCursorName and setCursorName?

    We do not support the getCursorName and setCursorName JDBC entrypoints. Instead we provide access to ROWIDs, which provide similar functionality. JDBC 4.0 defines java.sql.Rowid which is fully compatible with oracle.sql.ROWID and is supported in the JSE 6 (ojdbc6.jar) drivers.

    If you add the ROWID pseudocolumn to a query you can retrieve it in JDBC with the ResultSet getString entrypoint. You can also bind a ROWID to a preparedStatement parameter with the setString entrypoint.

    This allows in-place updates, as in the following example:

    In the ResultSetMetaData class, columns containing ROWIDs are reported with the type oracle.jdbc.driver.OracleTypes.ROWID, whose value is -8.

    Back to Top
    How do the JDBC drivers support Oracle REFCURSOR datatypes?

    The Oracle JDBC driver supports bind variables of type REFCURSOR. A REFCURSOR is represented by a JDBC ResultSet. Use the getCursor method of the CallableStatement to convert a REFCURSOR value returned by a PL/SQL block into a ResultSet. JDBC lets you call a stored procedure that executes a query and returns a results set. Cast the corresponding CallableStatement to oracle.jdbc.driver.OracleCallableStatement to use the getCursor method.

    Back to Top
    Does ANO work with JDBC drivers?

    As of version 9.2 both the OCI and Thin drivers support ANO.

    ANO works with 8.0.X OCI Drivers version 8.0.x and above. You need to have the latest patchsets for 8.0.4, 8.0.5 and 8.0.6 to have this feature working properly.

    Note: There is a known bug (#899424) in 8.1.5 and 8.1.6sdk. While we do have a bugfix for this, the bugfix has not yet been backported and released as a patch to all previous releases. So far, this bug still exists for 8.1.5 and 8.1.6sdk.

    The bugfix is already in the 8.1.6 code, so there is no patch needed for 8.1.6 - your code should just work! For more information, see bug #899424.

    Back to Top
    Can I serialize oracle.sql.* datatypes?

    Yes. All of the oracle.sql.* classes that represent SQL data types are serializeable.

    Back to Top
    Do the JDBC Drivers support Objects or Collections?

    Yes, the Oracle JDBC drivers do support Objects and Collections. This has been true since 8.1.5.

    Back to Top
    Can I use WaitOption and AutoRollback?

    The WaitOption and AutoRollback rollback options for batching calls have been deprecated and are no longer available for use. You can no longer use the following methods:

    public void setAutoRollback (int autoRollback); public int getAutoRollback(); public void setWaitOption(int waitOption); public int getWaitOption();Back to Top
    Can a Java Stored Procedure in one database instance open a connection to another database instance?

    Yes, using the Thin-server driver. This has been supported since 8.1.6sdk.

    The only known workaround at this time is to configure the first installation to use DBLINKS when contacting the second installation. This fakes the jdbc drivers into thinking that its still working in the same one instance, and relies on DBLINKS to take care of the details. However, there are rumored to be problems with using DBLINKS on an MTS server installation.

    Back to Top

    Performance

    Which is faster, the Thin driver or the OCI driver?

    As always, it depends. There are some applications where the the Thin driver is faster, some where the OCI driver is faster. As of 10.1.0, the Thin driver is probably slightly faster than the OCI driver. In cases when the client and server are the same type of hardware and OS, the OCI driver puts a little less load on the RDBMS, even though the Thin client is faster. The differences are usually small, less than 10%. Most of our customers use the Thin driver because of its easier administration. Your mileage may vary.

    Back to Top
    Which is faster, Statements or PreparedStatements?

    Statements may be slightly faster if you are only going to execute the SQL once. PreparedStatements are much faster when the SQL will be executed more than once. If you are using the statement cache, which you should, getting a statement from the cache is the same as executing the same statement.

    In general we strongly recommend that you use PreparedStatements. This is especially true if you are sending user provided data in the SQL. By binding the data to a PreparedStatement parameter you can prevent most SQL injection attacks. Any performance advantage of using Statements is negligible.

    Back to Top

    java.util.logging

    Why doesn't OracleLog work?

    As of release 11.1.0 the Oracle JDBC drivers no longer support OracleLog and higher use the built in logging facility in the JDK. This logging facility is in java.util.logging. Only very minimal capabilities are still available in OracleLog. You should use java.util.logging instead.

    Back to Top
    How do I use java.util.logging to get trace output from the Oracle JDBC drivers?

    I thought you'd never ask.

    First, you must use a jar file that includes logging code. The optimized jar files, ojdbc5.jar and ojdbc6.jar do not include any logging code. The non-debug DMS jar files, ojdbc5dms.jar and ojdbc6dms.jar, include some logging code. The debug jar files, *_g.jar, include extensive logging code. Be sure that there are no extra Oracle JDBC jar files in your classpath.

    Second, you must turn on Oracle JDBC logging. You can turn logging on globally by setting a system property -Doracle.jdbc.Trace=true or you can control it programatically using the Oracle JDBC Diagnosibility MBean. // create name javax.management.ObjectName name = new javax.management.ObjectName("com.oracle.jdbc:type=diagnosibility,name=*"); // get the MBean server javax.management.MBeanServer mbs = java.lang.management.ManagementFactory.getPlatformMBeanServer(); // find out if logging is enabled or not System.out.println("LoggingEnabled = " + mbs.getAttribute(name, "LoggingEnabled")); // enable logging mbs.setAttribute(name, new javax.management.Attribute("LoggingEnabled", true)); // disable logging mbs.setAttribute(name, new javax.management.Attribute("LoggingEnabled", false));

    Just turning on logging provides minimal output. For more detailed and more targeted output you must configure java.util.logging.

    Back to Top
    How do I configure java.util.logging to get useful trace output from Oracle JDBC?

    The JDBC code creates a number of Loggers. In order to get interesting output you need to set the logLevel on each of these Loggers and add a Handler somewhere. See the JavaDoc for java.util.logging for more information.

    Or, you can use the convenient property file OracleLog.properties provided in the demo.zip file that is part of the Oracle JDBC drivers installation. The comments in this file explain how to use it. This is much easier and highly recommended.

    Note that in either case you still have to enable logging to get trace output. You can toggle trace output on and off without reconfiguring the Loggers. The Diagnosibility MBean doesn't mess with the Loggers at all. If you don't want to change your source to call the MBean you can add -Doracle.jdbc.Trace=true to your java execution command. This will log the entire execution.

    For more info on configuring JDBC logging see the white paper on JDBC logging. A couple of hints: setting the Level to INFO will log the SQL that is executed, setting it to FINE will log entry and exit of all public methods, setting it to anything more than FINE will fill up all of your disk space with log files. You have been warned.

    Back to Top
    What about the Server-Side Internal driver?

    In 10.1.0 the Server-Side Internal driver uses JDK 1.4 and so uses java.util.logging for trace output. You can use the convenient OracleLog.properties file in the server by executing

    System.setProperty("java.util.logging.config.file", "OracleLog.properties")

    Put OracleLog.properties into $ORACLE_HOME.

    Back to Top
Left Curve
Popular Downloads
Right Curve
Untitled Document
Left Curve
More Database Downloads
Right Curve

Oracle Open World 2014 Banner