Oracle JDBC FAQ

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 problems. The server docs (including JDBC doc) are also available online.

Note that this FAQ contains information about all released versions of the Oracle JDBC drivers. Anything that is not described as pertaining only to certain versions should pertain to all the versions. Or to the current version. At the time the question was asked.

• About This FAQ
  • What is this document?
  • Where can I find it?
  • Who maintains it?
  • What has changed recently?
• JDBC in general
  • What is JDBC?
  • Where can I learn more about JDBC?
  • Where can I learn more about Java?
• Oracle JDBC in general
  • What JDBC drivers does Oracle provide?
  • Which JDBC drivers support which versions of Oracle database?
  • Which JDBC drivers support which versions of Javasoft's JDK?
  • Which JDBC drivers support which versions of JDBC?
  • Which driver should I use?
  • Where can I get the class files?
  • What are all of these files for?
  • What about the files for the Server Thin Driver and the Server Internal Driver?
  • Can I load one of the classes files into the Oracle Server Java VM?
  • Are Oracle's JDBC drivers Y2K compliant?
  • Are Oracle's JDBC jar files identical on all platforms?
  • Can third party vendors distribute Oracle's JDBC drivers along with their own software?
  • orai18n.jar is too big? How can I make it smaller?
  • What threads do the Oracle JDBC drivers create?
  • What permissions do the Oracle JDBC drivers require?
  • I just installed 11.1 and my code won't compile. What gives?
  • I just installed the 11.1 drivers and now my application throws IllegalAccessError. What gives?
  • You say oracle.jdbc.driver is desupported, yet the package is still in the jar files?
  • I don't use oracle.jdbc.driver but I'm still having problems with 11.1. Are there any other changes?
• Installation
  • How do I install the Thin driver?
  • How do I install the OCI driver?
  • How do I install the Server-Side Internal driver or the Thin in the server driver?
  • Where can I get the files for my platform?
• DriverManager and DataSources
  • What is the difference between the DriverManager and a DataSource?
  • Which connection cache should I use, OracleConnectionCacheImpl or the new Implicit connection cache?"
  • What is JDBC OCI Connection Pooling?
  • What is "connection striping"?
  • What is UCP?
  • What about the Implicit Connection Cache?
• Connections
  • How do I open a connection to a database?
  • What are the various forms of getConnection for?
  • What is the form of a URL?
  • What is the form of the <database> description?
  • How do I use the Properties argument?
  • Don't I have to register the class OracleDriver with the DriverManager?
  • What username and password should I use when connecting to the Server Internal Driver?
  • Is there any limit on number of connections?
  • I'm getting OutofMemory Error when I set a higher default prefetch value.
  • What is a service connect string?
  • How do I connect as SYSDBA or SYSOPER?
  • What encryption methods are supported by the Oracle JDBC drivers?
  • How do I turn on encryption and checksumming with the JDBC Thin driver?
  • What is proxy authentication?
  • Do Oracle JDBC drivers support SSL?
  • Do the Oracle JDBC drivers support LDAP in the connection URL?
  • How can I use JDBC to connect to a database server behind a firewall?
• Statements, PreparedStatements, CallableStatements
  • What is defineColumnType and when should I use it?
  • Does defineColumnType force conversions on the Server?
  • Do Oracle JDBC drivers provided Bind by Name facility ?
  • For setXXX methods in PreparedStatement how do the Oracle JDBC drivers determine the data type?
  • What happens if the type of the target parameter is not the same as the type assumed by the setXXX method?
  • Why do not the drivers do the conversion on the client side?
  • For inserting into a column in a table what are the byte data types?
  • For inserting into a column in a table what are the character data types?
  • Why so many different mechanisms?
  • What is new in 10g R2?
  • What are the sizes for the setString. setCharacterStream, and setAsciiStream?
  • What are the size limit for binary data via setBytes and setBinaryStream?
  • What are the size limits for the proprietary methods setBytesForBlob, setStringForClob in oracle.jdbc.OraclePreparedStatement?
  • Why is this so complicated?
  • Does switching bind types force additional server side parsing of the statement?
  • What about CallableStatements and procedures with IN OUT parameters?
  • Will the selection of polymorphic PL/SQL procedures change when the bind type changes?
  • What about existing code?
  • For certain cases the driver is creating temporary lobs. Does it track these and free them?
  • 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?
  • Is it a good idea to use e.g. setString for a really huge string?
  • LONG RAW and LONG column types are deprecated. Why are there new uses of the setXXXStream API's?
  • The LOB API's are much more flexible, right?
  • Why can't I create a PreparedStatement that does select * from tab where id in (?, ?, ?, ...)?
• ResultSets
  • What does "Exhausted ResultSet: prepare_for_new_get" mean?
  • Why do I have to close ResultSets?
• Simple Data Types
  • What is going on with DATE and TIMESTAMP?
  • What is the longest value I can bind?
  • Why is the result of reading a TIMESTAMP WITH TIME ZONE different?
• Advanced Data Types (Objects)
  • How should I create instances of ADTs?
  • Why isn't the standard factory method createArrayOf supported?
• BFILEs, BLOBs, CLOBs
  • What is DBMS_LOB.ERASE doing?
  • Can I use oracle.sql.CLOB.putChars()?
  • OCI provides function to manipulate a CLOB CharSetId. What is the JDBC equivalent?
  • Writing into BLOBS is slower than inserting LONG RAWs ?
  • Why am I getting an ORA-03127 error when getting the LobLength in the following Stream sample code?
  • When I get a CLOB from the database and then update it, sometimes my changes don't appear in the database. Why?"
• 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?
  • How can I create a new REF?
• OPAQUE types
  • What are OPAQUE types?
• RowSources
  • When I set the scrollability attribute after execute, it does not have any effect on the scrollability of the RowSet. Why?
  • Can I serialize the RowSet object to a flat file even if the RowSet contains streams?
• Thin Driver
  • Can the Thin JDBC Driver be used to develop Java applications?
  • With which Browsers has the thin JDBC Driver been tested?
• OCI Driver
  • Known bug: JDBC OCI and SSL with Java green threads.
  • Does OCI Driver work Microsoft's Java VM ?
  • I'm getting Unsatisfied Link Error with OCI 8 Driver ?
  • Can the JDBC OCI Driver be used with Java applets?
• Server Internal Driver
  • When should I use the Server Internal Driver?
• Server Thin Driver
  • When should I use the Server Thin Driver?
• Errors
  • DriverManager.getConnection gives the Error: "No suitable driver"
  • Error Message: "Unimplemented Method Interface"
  • Error Message: "UnsatisfiedLinkError with OCI driver"
  • Error Message: "ORA-1019: unable to allocate memory."
  • Error Message: "ORA-01000: maximum open cursors exceeded"
  • Error Message: "ORA-01002: fetch out of sequence"
  • Error Message: "ORA-12705: invalid or unknown NLS parameter value specified"
  • Error While Trying to Retrieve Text for Error ORA-12705.
  • Error Message: "Invalid driver designator"
  • Cant get JDBC Drivers to work with the Oracle WebServer
  • Error Message: FileNotFound Exception
  • Getting Security Exceptions from Netscape when Connecting to Oracle
  • Error Message: The JDBC Thin Driver Gives Me "invalid character" Errors for Unicode Literals
  • INSERT or UPDATE operations are slow
  • Error Message: "Connection reset by peer"
  • Why do I get FileNotFoundException when using jdb and JDK 1.4.2?
  • What does "Protocol Violation" mean?
• Demo Programs
  • Are there any JDBC demo programs?
  • How do I run the demos?
  • What should I do when error happens when I run a demo?
• Oracle JDBC Trace Facility
  • What is the JDBC Trace Facility?
  • How do I turn on the Trace?
  • How do I control the volume of output?
  • Where does the output go?
  • How can I turn off DMS in a DMS enabled jar?
• Development Tools and Environments
  • Can I debug JDBC programs with Symantec Visual Cafe?
  • Can I debug JDBC programs with Microsoft's Visual J++?
• Supported Features
  • Can the JDBC Drivers access PL/SQL Stored Procedures?
  • Do the JDBC Drivers support streaming?
  • Do the JDBC Drivers support multibyte character sets?
  • Do the JDBC Drivers work with firewalls?
  • Do Oracle's JDBC drivers support PL/SQL tables/result sets/records/booleans?
  • Is failover supported?
  • How do the JDBC drivers support Oracle ROWID datatypes? What does this have to do with getCursorName and setCursorName?
  • How do the JDBC drivers support Oracle REFCURSOR datatypes?
  • Does ANO work with the JDBC drivers?
  • Can I serialize oracle.sql.* datatypes?
  • Do the JDBC Drivers support Objects or Collections?
  • Can I use WaitOption and AutoRollback?
  • Can a Java Stored Procedure in one database instance open a connection to another database instance?
• Performance
  • Which is faster, the Thin driver or the OCI driver?
  • Which is faster, Statements or PreparedStatements?
• java.util.logging
  • Why doesn't OracleLog work when I use ojdbc14_g.jar?
  • How do I use java.util.logging to get trace output from the Oracle JDBC drivers?
  • How do I configure java.util.logging to get useful trace output from Oracle JDBC?
  • What about the Server-Side Internal driver?
• Release Specific Questions
  • 7.3.X
    • Can I use the Oracle7 JDBC Drivers with Oracle8?
  • 8.0.X
    • Why am I not able insert >2K data into a long column with setString using 8.0.X THIN driver?
  • 8.1.5 (8i)
    • Does our drivers support the 8i 'Connect through proxy' functionality?
    • Is it possible to return multi-row query results from a stored procedure?
    • Is DML Returning Supported ?
    • Is Multi-tier Authentication supported?
    • Which version of JDBC is supported?
  • 8.1.7 (8i r2)
    • Is DML Returning Supported ?
    • Can 8.1.7 connect to a 9.0.1 database?
    • Which version of JDBC is supported?
  • 9.0.1 (9i)
    • Is DML Returning Supported ?
  • 9.2.0 (9i r2)
    • Is DML Returning Supported ?
  • 10.1.0 (10g r1)
    • Is DML Returning Supported ?
  • 10.2.0 (10g r2)
    • Is DML Returning Supported ?

About This FAQ

This is the Official Oracle JDBC FAQ.

You can find it on the OTN website at http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html.

It is maintained by the Oracle JDBC Development Team with the invaluable assistance of other Oracle Development Teams, the Oracle Java Platform Product Managers, and the Oracle Support Organization.

Material related to Oracle Database 11.2 was added. Some older material was moved to the Version specific section and other very old material was deleted.

JDBC in General

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.

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 .

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

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

• Core Java 2, Volume 1: Fundamentals
• Java in a Nutshell
• Effective Java Programming Language Guide–2nd Edition

Oracle JDBC in General

Oracle provides four different types of JDBC drivers, for use in different deployment scenarios. The 11.2.0 drivers can access Oracle 9.0.1 and higher. 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 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.

  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 Oracle Call Interface for more information.

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

• JDBC 9.0.1 drivers can talk to RDBMS
  • 11.1.0
  • 10.2.0
  • 10.1.0
  • 9.2.0
  • 9.0.1
  • 8.1.7
  • 8.1.6
  • 8.1.5
  • 8.0.6
  • 8.0.5
  • 8.0.4
  • 7.3.4
• JDBC 9.2.0 drivers can talk to RDBMS
  • 11.2.0
  • 11.1.0
  • 10.2.0
  • 10.1.0
  • 9.2.0
  • 9.0.1
  • 8.1.7
• JDBC 10.1.0 drivers can talk to RDBMS
  • 11.2.0
  • 11.1.0
  • 10.2.0
  • 10.1.0
  • 9.2.0
  • 9.0.1
  • 8.1.7
• JDBC 10.2.0 drivers can talk to RDBMS
  • 11.2.0
  • 11.1.0
  • 10.2.0
  • 10.1.0
  • 9.2.0
  • 9.0.1
  • 8.1.7
• JDBC 11.1.0 drivers can talk to RDBMS
  • 11.2.0
  • 11.1.0
  • 10.2.0
  • 10.1.0
  • 9.2.0
  • 9.0.1
• JDBC 11.2.0 drivers can talk to RDBMS
  • 11.2.0
  • 11.1.0
  • 10.2.0
  • 10.1.0
  • 9.2.0
  • 9.0.1

A related question is about versions NOT listed here. If its not listed here, its not supported. You should verify with your support channel that you are still on a supported version of Oracle. For example, a lot of people have asked about 8.1.7 which has been desupported. People still using 8.1.7 need to contact their support channels for upgrade info (some of these upgrades are free!).

• pre-8i OCI and THIN Drivers - JDK 1.0.x and JDK 1.1.x
• 8.1.5 OCI and THIN Drivers - JDK 1.0.x and JDK 1.1.x
• 8.1.6SDK THIN Driver - JDK 1.1.x and JDK 1.2.x (aka Java2)
• 8.1.6SDK OCI Driver - Only JDK 1.1.x
• 8.1.6 OCI and THIN Driver - JDK 1.1.x and JDK 1.2.x
• 8.1.7 OCI and THIN Driver - JDK 1.1.x and JDK 1.2.x
• 9.0.1 OCI and THIN Driver - JDK 1.1.x, JDK 1.2.x and JDK 1.3.x
• 9.2.0 OCI and THIN Driver - JDK 1.1.x, JDK 1.2.x, JDK 1.3.x, and JDK 1.4.x
• 10.1.0 OCI and THIN Driver - JDK 1.2.x, JDK 1.3.x, and JDK 1.4.x
• 10.2.0 OCI and THIN Driver - JDK 1.2.x, JDK 1.3.x, JDK 1.4.x, and JDK 5.0.x
• 11.1.0 OCI and THIN Driver - JDK 1.5.x and JDK 1.6.x
• 11.2.0 OCI and THIN Driver - JDK 1.5.x and JDK 1.6.x

Please note that JDK 1.4 is not supported by the 11 drivers.

The Server Thin Driver and Server Internal Driver support the same version of Java as is supported by the Oracle Server VM.

• Oracle 8i supports J2SE 1.1
• Oracle 9i R1 supports J2SE 1.2
• Oracle 91 R2 supports J2SE 1.3
• Oracle 10g supports J2SE 1.4
• Oracle 11 supports JSE 5

Oracle 9.0.1 supports:

• JDBC 2.0 Extensions except for:
• using both global and local transactions on the same connection.

Oracle 9.2.0 supports:

• Partial support for JDBC 3.0:
• transaction savepoints
• using both global and local transactions on the same connection.

Oracle 10.1.0 supports:

• Full support for JDBC 3.0 except for:
• retrieving auto-generated keys
• result-set holdability
• returning multiple result-sets.

Oracle 10.2.0 supports:

• Full support for JDBC 3.0
  Note that there is no real change in the support for the following in the database. All that has changed is that some methods that previously threw SQLException now do something more reasonable instead.
• result-set holdability
• returning multiple result-sets.

Oracle 11.1.0 and 11.2.0 support:

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. In most cases it is as fast or faster than the OCI driver (from 10.1.0), has almost exactly the same set of features, and is easier to administer. In a few cases the OCI driver has slightly better performance. The OCI driver supports a few Oracle features better than the Thin driver. The Thin driver is easier to administer since it does not require installation of the OCI C libraries. The Thin driver will work on any machine that has a suitable Java VM, whereas with the OCI driver you must install the proper OCI C libraries for each machine. We recommend using the Thin driver unless you must have one or more of the OCI only features, or until it is clear that the small performance gain provided by the OCI driver is worth the extra effort.

You can get the classes files from the Oracle Technology Network SQLJ & JDBC Download Page.

Since the Thin driver is 100% Pure Java--Write Once, Run Anywhere--you can use the jar files on any platform that has an appropriate Java VM. The orai18n.jar (previously nls_charset) files are 100% Java so the same files are used for all platforms. The shared library files (.so, .dll) are platform specific so you must use the file appropriate for your platform. These are available in the OCI Instant Client installation. Only the Solaris, Linux, and NT shared libraries are available for download on OTN. You will have to get the share libraries for other platforms from the Oracle client installation CD for that platform.

There are a large number of classes files that are part of the Oracle JDBC installation.

ojdbc5.jar

All the classes to support basic functionality for the Thin and OCI drivers when using JDK 1.5 (JSE 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.

ojdbc6.jar

All the classes to support basic functionality for the Thin and OCI drivers when using JDK 1.6 (JSE 6). 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.

libocijdbc<major_version_id>.so (Solaris)

Native library for the JDBC OCI driver.

• for JDBC 8.x.x drivers, the <major_version_id> is 8
• for JDBC 9.x.x drivers, the <major_version_id> is 9
• for JDBC 10.x.x drivers, the <major_version_id> is 10
• for JDBC 11.x.x drivers, the <major_version_id> is 11

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 US7ASCII, W8DEC, WE8ISO8859P1, or ShiftJIS, and the application uses ADTs, then you must include this file in your classpath. It is possible to use your favorite zip utility to delete unneeded files from the jar, but not easy. See orai18n.jar is too big. How do I make it smaller?

This file is still required under the circumstances described above, but it is not technically part of the JDBC drivers. It is available on the JDBC dowrload page or you can get it as part of the Oracle Globalization Kit.

Some older (pre 11.1.0) versions of Oracle JDBC included some of the following files. These files are not shipped in Oracle Database 11 either because they are no longer supported or because they are no longer needed.

classes111.jar

Classes for the Thin and OCI drivers when using a Java 1.1 VM. Includes support for all Oracle character sets as simple columns and for US7ASCII, W8DEC, and ShiftJIS in Advanced Data Types (objects).

classes111.zip

Same as classes111.jar except in zip format. Useful because some VMs can't read jar files.

classes111_g.jar

Same as classes111.jar except compiled with the -g option to include debugging information and with Oracle JDBC logging included.

classes111_g.zip

Same as classes111_g.jar except in zip format. Useful because some VMs can't read jar files.

classes12.jar

Classes for the Thin and OCI drivers when using a Java 1.2 or 1.3 VM.

classes12.zip

Same as classes12.jar except in zip format. This file will almost certainly not be available in future releases. You should use classes12.jar instead.

classes12_g.jar

Same as classes12.jar except compiled with the -g option to include debugging information and with Oracle JDBC logging included.

classes12_g.zip

Same as classes12_g.jar except in zip format. Useful because some VMs can't read jar files.

classes12dms.jar

Same as classes12.jar except includes code to support DMS, Oracle Dynamic Monitoring Service. 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.

classes12dms_g.jar

Same as classes12dms.jar except compiled with the -g option to include debugging information and with Oracle JDBC logging included.

ojdbc14.jar

Same as classes12.jar except for use with Java 1.4 VMs. Beginning with this file, Oracle JDBC classes files will be named ojdcbXX.jar, where XX is the Java version number. The classes12 files will not be renamed. We will not provide zip files for Java 1.4 and beyond.

ojdbc14_g.jar

Same as ojdbc14.jar except compiled with the -g option to include debugging information and with java.util.logging calls included.

ojdbc14dms.jar

Same as ojdbc14dms.jar except includes code to support DMS, Oracle Dynamic Monitoring Service. 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.

ojdbc14dms_g.jar

Same as ojdbc14dms.jar except compiled with the -g option to include debugging information and with java.util.logging calls included.

nls_charset11.jar

Contains the classes to support all Oracle character sets in Advanced Data Types (objects) when using a Java 1.1 VM. If the database character set is one other than US7ASCII, W8DEC, or ShiftJIS, and the application uses ADTs, then you must include this class in your classpath. Or you can unzip this file and add the appropriate file to your classpath. The files are named with the Oracle character set number.

nls_charset11.zip

Same as nls_charset11.jar except in zip format. Useful because some VMs can't read jar files.

nls_charset12.jar

Same as nls_charset11.jar except for use with Java 1.2, 1.3, and 1.4 VMs. Not used with Oracle 10.1.0 and later. Use orai18n.jar instead.

nls_charset12.zip

Same as nls_charset12.jar except in zip format. Useful because some VMs can't read jar files.

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.

No. There is no need. The necessary class files are installed as part of the database installation.

You can't do it without sufficient privileges and if you have those privileges, you shouldn't do it because it breaks things.

Yes, if it isn't already obvious.

For more details, see the JDBC Y2K paper.

Yes.

The actual .jar files are identical on all platforms. We only build these on one platform, and just copy them over to the others. 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.

A third party software company (and Oracle partner) wanted to know if they could distribute the Oracle JDBC drivers with their application to their own customers. This is a legal question, rather then a technical one. I'm no legal expert, but the PMs here tell me that the short answer is yes so long as you follow the licensing terms spelled out at http://www.oracle.com/technology/software/htdocs/distlic.html.

For something legally binding, get a lawyer or contact your local Oracle sales rep for more details.

Follow the directions in the 10.2.0.1.0 or later version of the Oracle JDBC Developer's Guide. The instructions in the 10.1.0 documentation are incorrect. If you do not have access to the 10.2.0.1.0 or later Developer's Guide, then you can follow the instructions here.

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

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

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.

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. What permissions do the Oracle JDBC drivers require?

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.

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. You will have to find another solution. There is no workaround or backward compatibility mode.

The package oracle.jdbc was introduced in 8.1.7. In 9.0.1 we deprecated the package oracle.jdbc.driver and updated all the documentation to refer to oracle.jdbc. At that time we also started telling people that oracle.jdbc.driver would be desupported. That was over six prior. Every release since then has warned that oracle.jdbc.driver was going to be desupported. Two years prior we announced on the JDBC OTN page and forum that 11.1 would desupport oracle.jdbc.driver. And so it does.

We take backward compatibility very seriously, but there have been many changes in Oracle, Java, and JDBC since the first release of the drivers. For a variety of reasons it has long been necessary for us to desupport customer use of the implementation classes that are contained in oracle.jdbc.driver. We have been trying to get the word out for over six years. We are sorry that you are having problems, but we had to make this step.

See the answer to the previous question.

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 could have renamed the package but did not because it would have been a lot of work for not much gain.

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.

Yes.

The class oracle.jdbc.pool.OracleConnectionCacheImpl has also been desupported. We only gave a couple of years notice about this. Use the Oracle Implicit Connection Cache instead. It is documented in the Oracle JDBC Developer's Guide.

This approach to connection caching proved very problematic. Basically we couldn't get it to work reliably and it is a poor design. We have only limited resources and OracleConnectionCacheImpl was a huge resource sink. We wanted to use our resources on projects with more long term benefit to our customers, so we had to let it go.

Installation

Put the jar files in a convenient location and include the appropriate jar files in your classpath. See What are all these files for? to determine which files you need.

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 see "What are all of these files for" for details. Also refer to the documentation on OCI Instant Client install.

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?

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.

DriverManager and DataSources

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:

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:

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:

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.

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.

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.

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.

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.

Connections

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.

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.

The general form of a URL is

The <drivertype> is one of

• thin
• oci
• kprb

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

Note that a URL like

has an empty username and password whereas this URL

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

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.

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

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.

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

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

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.

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.

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.

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.

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.

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:

Examples:

For more info see the JDBC User Guide.

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:

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

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

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

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.

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.

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.

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

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.

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

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.

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.

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

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

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

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

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

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

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

Yes.

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.

Yes. Consider that this can be exploited in your PL/SQL 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.

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

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

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.

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

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

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.

ResultSets

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.

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.

Simple Data Types

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.

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.

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)

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

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

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

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.

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.

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

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.

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

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.

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:

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

A bean's properties can be classified as:

• object creation properties
• runtime properties

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.

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

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.

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.

OCI Driver

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.

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.

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.

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.

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

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

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.

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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:

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.

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.

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

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

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.

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

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.

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

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.

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

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

• none--just prints the message
• default
• thread--same as default plus the thread name
• all

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

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

Development Tools and Environments

Visual Cafe is no longer supported.

Visual J++ is no longer supported.

Supported Features

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

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.

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.

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.

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:

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.

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.

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.

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.

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

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

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:

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

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.

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

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.

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.

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.

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

Put OracleLog.properties into $ORACLE_HOME.

Release Specific Questions

7.3.X

Yes it is possible to use the Oracle7 JDBC OCI and Thin JDBC Drivers with Oracle8. Applications developed to these drivers with an Oracle7 server will continue to work with an Oracle8 server when the database is upgraded. There are two important considerations to keep in mind however:

• Oracle7 Client: The Oracle7 JDBC OCI Driver does require the Oracle7 client or required support files i.e. the OCI, CORE, NLS and other required support files. Therefore, if you have upgraded to an Oracle8 client along with your Oracle8 server, you will need to create a separate $ORACLE_HOME, install the Oracle7 client and use it against the Oracle8 server
• Further, the Oracle7 JDBC Drivers do not provide access to any of the new performance and scalability features as well the object relational functionality provided by the Oracle8 database server.

8.0.X

Basically, our 8.0.X THIN DRIVER has Oracle 7 Properties, and hence you cannot use setString to insert >2000 byte data into a long column. Long columns with >2K data needs to be streamed and you cannot stream with setString.

8.1.5 (8i)

Yes, through SQLNET Proxies.

Yes, as a Ref Cursor.

Not in the current drivers. However, we do have plans to support it in 8.1.6 for JDBC-kprb driver. This support will not be available for the Thin and JDBC-OCI drivers in 816, but will be available post-8.1.6.

No.

Oracle 8.1.5 provides JDK 1.1.x drivers and does not support JDK 1.2. Oracle 8.1.5 currently provides JDBC 1.22 and a subset of JDBC 2.0 features. The support we provide covers structured types in JDBC 2.0; it does not include any XA support.

Oracle 8.1.6, provides JDBC drivers for Java1.1.x and for Java2 (aka 1.2). The 1.2 Oracle drivers are fully JDK 1.2 compliant. Both the 1.1.x and our 1.2 JDBC drivers are JDBC 2.0 compliant except for Calender datatype support. Features supported include:

• Core JDBC:
• Beta support for scrollable cursors
• Support for structured types and lob types.
• Batching support compliant with the JDBC 2.0 API

• JDBC 2.0 Extensions:
• Connection Pooling
• JDBC-XA support (but not "recover()").

8.1.7 (8i r2)

Not in the current drivers. However, we do have plans to support it in post 8.1.7 drivers.

There is a known bug(#1725012) when using JDBC 8.1.7 thin driver to connect to a 9.0.1 RDBMS - applying the corresponding JDBC patch solves this known bug.

Oracle 8.1.7, supports:

• JDBC 2.0 Extensions:
• JDBC-XAResource.recover() support

To implement recover, please note that it is dependant on a lot of code outside of our control and therefore is not firmly committed to at this time. It may even have to move to the 8.2 release because of this.

9.0.1 (9i)

Not in the current drivers. However, we do have plans to support it in post 9.0.1 drivers.

9.2.0 (9i r2)

Not in the current drivers. However, we do have plans to support it in post 9.2.0 drivers.

10.1.0 (10g r1)

Not in the current drivers. However, we do have plans to support it in post 10.1.0 drivers. We really mean it this time.

10.2.0 (10g r2)

YES! And it's about time. See the Developer's Guide for details.