Frequently Asked Questions

Open all Close all
  • Whats New in 19c?

    Updated January 5, 2022

    This document contains answers to the most frequently asked questions about Oracle's JDBC drivers. Note that this FAQ addresses specific technical questions only and are used to document solutions to frequent customer questions as well as any known problems. Go through the JDBC Reference Guide and Javadoc for JDBC for more detailed information.

    The section below highlights the key JDBC features of 19c. Please refer to Revisiting the Performance & Scalability of Java Applications that use RDBMSes for more details.

    • Latest Java Standards: Supports JDK17, JDK11, JDK8 and compliant to JDBC 4.3 (ojdbc11.jar and ojdbc10.jar) and JDBC 4.2(ojdbc8.jar) by JDBC driver and Universal Connection Pool (ucp.jar)

    • Connection: Easy Connect Plus for easier TCPS connections and passing connection properties (19c only); new ojdbc.properties file to set connection properties; different ways for setting TNS_ADMIN; setting server's domain name (DN) cert as a connection property; and support of a new wallet_property (my_wallet_directory);
    • Performance: Reactive Streams Ingest (RSI) for streaming data into the Oracle Database (21c and later); Oracle connection manager (CMAN) in traffic director mode (CMAN-TDM)
    • High Availability: Transparent Application Continuity (TAC); AC support in DRCP; and AC support for legacy Oracle JDBC types implemented as concrete Java classes
    • Scalability: Oracle RAC data affinity; and shard routing APIs for mid-tiers
    • Security: Automatic provider resolution for OraclePKIProvider; support for Key Store Service (KSS); and HTTPS proxy support
    • Data types: Accessibility to PL/SQL associate arrays; Oracle REF CURSOR as IN bind parameter; and JSON datatype validation
  • Are there any Oracle Database Cloud Service related documents for Java developers?

    For Java developers using JDBC driver and/or UCP, the detailed instructions to connect to database service on cloud are present on JDBC with DB Cloud page .

JDBC in General

Release Specific Questions

  • Which version of JDBC drivers are supported ?

    Please refer to the table below for the supported versions of JDBC drivers. Please note that the information in this table provides the summary for your convenience, we recommend you to refer to page#4 on Lifetime Support Policy for more details and for any updates.

    Release GA Date Premier Support Ends Extended Support Ends Sustaining Support Ends
    21c (Innovation Release) Aug 2021 Apr 2024 Not Available Indefinite
    19c (Long Term Release) Apr 2019 Apr 2024 Apr 2027 Indefinite
    18c Jul 2018 Jun 2021 Not Available Indefinite
    12.2 Mar 2017 Nov 30, 2020 (Limited Error Correction Period for 12.2.0.1 – Dec 1, 2020 – Mar 31, 2022) Not Available Indefinite
    EE 12.1 Jun 2013 Jul 2018 Jul 2022 Indefinite
  • What is the JDBC and RDBMS interoperability matrix or the certification matrix?

    Please refer to the table that covers the JDBC driver interoperability matrix for the supported Oracle database versions. The recommendation is that JDBC driver version should always be either same as or higher than the Oracle database version in order to leverage the latest capabilities of the driver.

    Interoperability Matrix Database 23.3 Database 21.x Database 19.x Database 18.3 Database 12.2 and 12.1
    JDBC 23.3 Yes Yes Yes No No
    JDBC 21.x Yes Yes Yes Was Was
    JDBC 19.x Yes Yes Yes Was Was
    JDBC 18.x No Was Was Was Was
    JDBC 12.2 and 12.1 No Was Was Was Was
    Was: Was a supported combination but one of the releases is no longer covered by any of Premier Support, Primary Error Correct support, Extended Support, nor Extended Maintenance Support. Fixes are no longer possible.
  • What are the Oracle JDBC releases Vs JDK versions?

    The Oracle JDBC driver is always compliant to the latest JDK version in each of the new releases. In some versions, JDBC drivers support multiple JDK versions. Use the table below to choose the correct JDBC driver based on your preferred JDK version.

    Oracle Database version JDBC Jar files specific to the release
    23.x ojdbc11.jar with JDK11, JDK17, JDK19, and JDK21
    ojdbc8.jar with JDK8 and JDK11
    21.x ojdbc11.jar with JDK11, JDK17, and JDK19
    ojdbc8.jar with JDK8 and JDK11
    19.x ojdbc10.jar with JDK11, JDK17, and JDK19
    ojdbc8.jar with JDK8, JDK11, and JDK17
    18.x ojdbc8.jar with JDK8 and JDK11
    12.2 or 12cR2 ojdbc8.jar with JDK 8
    12.1 or 12cR1 ojdbc7.jar with JDK 7 and JDK 8
    ojdbc6.jar with JDK 6
    11.2 or 11gR2 ojdbc6.jar with JDK 6, JDK 7, and JDK 8
    (Note: JDK7 and JDK8 are supported in 11.2.0.3 and 11.2.0.4 only)
    ojdbc5.jar with JDK 5
  • What are the Oracle JDBC releases Vs JDBC specifications?

    The table lists the Oracle JDBC drivers and the JDBC specification supported in that release.

    Oracle Database version JDBC specification compliance
    23.x and 21.x JDBC 4.3 in ojdbc11.jar
    JDBC 4.2 in ojdbc8.jar
    19.x JDBC 4.3 in ojdbc10.jar
    JDBC 4.2 in ojdbc8.jar
    18.3 JDBC 4.2 in ojdbc8.jar
    12.2 or 12cR2 JDBC 4.2 in ojdbc8.jar
    12.1 or 12cR1 JDBC 4.1 in ojdbc7.jar
    JDBC 4.0 in ojdbc6.jar
    11.2 or 11gR2 JDBC 4.0 in ojdbc6.jar
    JDBC 3.0 in ojdbc5.jar
  • Are the Oracle JDBC drivers certified against OpenJDK?
     

    Oracle JDBC drivers are certified only with the Oracle JVM (formerly Sun JVM). However, customers have been using Oracle JDBC drivers with non-Oracle JVMs (e.g., IBM JVM). The only caveat is that for the Oracle JDBC development team and Oracle Support to consider an issue pertaining to Oracle JDBC drivers, we will mandate that such issue be reproduced on the Oracle JVM.

  • What is the recommended 19.x JDBC driver to be used with JDK11?     

    19.x version has
    (a) ojdbc8.jar (compiled with JDK8 (JDBC 4.2) and can be used with JDK9, JDK11) and
    (b) ojdbc10.jar (compiled with JDK10 (JDBC 4.3) and can be used with JDK11).
    If you are using JDK11 then, ojdbc8.jar is still a better choice as it includes all the 4.3 features but as Oracle extensions. Customers can use ojdbc10.jar only if they need JDBC 4.3 features available through standard Java SE.
    Example:
    ojdbc8.jar:
    Connection conn = DriverManager.getConnection(. . .);    // conn.beginRequest(); would fail because beginRequest is not in Java 8  ((OracleConnection)conn).beginRequest(); // succeeds because beginRequest is provided as an Oracle extension

    ojdbc10.jar:
    Connection conn = DriverManager.getConnection(. . .);  conn.beginRequest(); // succeeds because beginRequest is in Java 10        ((OracleConnection)conn).beginRequest(); // succeeds because OracleConnection supports JDBC 4.3 (in Java 10) and beginRequest is part of JDBC 4.3
  • 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.

  • Where can I get the JDBC jar files?

    Please download the required JDBC jar and other companion jars such as orai18n.jar, oraclepki.jar, osdt_core.jar, osdt_cert.jar from the Oracle Technology Network JDBC Download Page.

  • What are the different JAR files on the 19.x JDBC driver download page for?

    Please refer to the table below to know more details about the JDBC drivers.

    • ojdbc10-full.tar.gz: This archive contains the latest 19.x JDBC Thin driver (ojdbc10.jar), the Universal Connection Pool (ucp.jar), their Readme(s) and companion jars.
    • ojdbc8-full.tar.gz: This archive contains the latest 19.x JDBC Thin driver (ojdbc8.jar), the Universal Connection Pool (ucp.jar), their Readme(s) and companion jars.
    • ojdbc10.jar: Certified with JDK11 and JDK10, all the classes to support basic functionality for the Thin driver. Additional jar files are required when you use some features
    • ojdbc8.jar: Certified with JDK9 and JDK8, all the classes to support basic functionality for the Thin driver. Additional jar files are required when you use some features
    • ucp.jar: Universal Connection Pool(UCP) that provides the connection pool capabilities.
    • oraclepki.jar, osdt_core.jar, and osdt_cert.jar: Additional jar required to access Oracle Wallets from Java
    • orai18n.jar: For use by the Oracle Notification Services (ONS) daemon
    • simplefan.jar: Java APIs for subscribing to RAC events via ONS
    • xbd6.jar: Classes to support standard JDBC 4.x java.sql.SQLXML interface
    • ojdbc8_g.jar: Same as ojdbc8.jar except compiled with the -g option to include debugging information and with java.util.logging calls included.
    • ojdbc8dms.jar: Same as ojdbc8.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.jaris 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.
    • ojdbc8dms_g.jar: Same as ojdbc8dms.jar except compiled with the -g option to include debugging information and with full JDBC logging support.

Oracle JDBC in General

  • What are the different JDBC drivers that 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. We recommend all of our customers to use JDBC Thin driver as most of the new features are developed only on JDBC Thin driver. Because it is written entirely in Java, this driver is platform-independent.
    • 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.
    • JDBC OCI client-side driver: This is a JDBC Type 2 driver that uses Java native methods to call entry points 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 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 entry points 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.
  • Which driver should I use?

    The best choice is to use Oracle JDBC thin driver. All the new enhancements and features are implemented only on JDBC Thin driver.

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

    For in-place processing within your database session (i.e., Java in the database), you must use either the embedded type 2 driver (or server internal driver); if your java code running in your session needs to access a remote Oracle database or another session within the same database instance, then you must use the embedded type 4 driver (or server thin driver).

  • What about the Jars 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. Check out the InternalT2Driver.java and InternalT4Driver.java for reference.

  • 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 FUTC license and run this by your legal department and then contact your local Oracle sales rep for more details.

  • 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.

    The way to find out what permissions to grant is to look at the file ojdbc.policy on the download page. 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. 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.

Installation

  • How do I install the Thin driver?

    Download the Oracle JDBC driver that is compliant with the JDK version that you are using. You can find the latest versions of the JDBC driver on the download page.. Make sure to include the JDBC drivers on the classpath. See What are the different JAR files on the download page for? to determine which files you need.

  • How do I install the OCI driver?

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

  • 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?

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.

    DataSources provide a more flexible way to create Connections. 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);
  • Which connection pool should I use?

    You should use Universal Connection Pool (UCP). 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 de-supported. Note that the old connection cache, OracleConnectionCacheImpl was desupported in 11.1.

  • 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.

Connections

  • What is the form of a URL?

    The general form of a URL is

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

    The <drivertype>

    • 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.

  • 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.wi7. 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.

  • 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".

  • 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
  • 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");
  • 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.

  • 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.

  • 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.

  • 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.

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.

  • Does defineColumnType force conversions on the Server?

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

  • How to turn off EscapeProcessing at the jdbc driver level instead of calling stmt.setEscapeProcessing(false) in application code?

    Please use a property 'CONNECTION_PROPERTY_PROCESS_ESCAPES' in OracleConnection.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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  
  • What are the size limit for binary data via setBytes and setBinaryStream?
    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  

    Notes:

    could be replaced with

    begin Insert into blob_tab (blob_col) values (? ); end;

    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 (?)
    2. The Oracle specific method setBytesForBlob may be used as an alternate workaround.
  • 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.
    2. The Oracle specific method setBytesForBlob may be used as an alternate workaround.
  • Does switching bind types force additional server side parsing of the statement?

    Yes.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

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);

  • 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(); }

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.

  • 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.

  • 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.

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.

  • 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.

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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

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); } }

  • 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);

OPAQUE types

  • What are OPAQUE types?

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

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.

  • 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.

Thin Driver

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

    Yes, the thin JDBC Driver can be used to develop Java applications. 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.

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.

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.

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.

  • 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"c.

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

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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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\"");

  • 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.

  • 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.

  • 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.

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.

  • 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.

  • 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.

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.

  • 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".

  • 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
  • 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.

  • 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 MyApp

Development Tools and Environments

  • Can I debug JDBC programs with Symantec Visual Cafe?

    Visual Cafe is no longer supported.

  • Can I debug JDBC programs with Microsoft's Visual J++?

    Visual J++ is no longer supported.

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;");
  • 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.

  • 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.

  • 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.
  • 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;

  • 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.

  • 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.

  • 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.

  • 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.

  • Can I serialize oracle.sql.* datatypes?

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

  • 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.

  • 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();

  • 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.

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.

  • 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.

java.util.logging

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

    First, you must use a jar file that includes logging code. The JDBC driver ojdbc8.jar does not include any logging code. The non-debug DMS jar file, ojdbc8dms.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.

  • 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.

  • What about the Server-Side Internal driver?

    Server-Side Internal driver uses java.util.logging for tracing 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.