As a new oracle DBA you might have to troubleshoot issues with Oracle JDBC drivers. Below article gives an indepth overview of all different concepts and issues with the oracle JDBC drivers.

http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#01_01

Oracle JDBC

updated July 18, 2005

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 zip and 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?

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

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” means?

Simple Data Types

What is going on with DATE and TIMESTAMP?

What is the longest value I can bind?

Advanced Data Types (Objects)

Your question here.

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 ?

8.1.7 (8i r2)

Is DML Returning 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

What is this document?

This is the Official Oracle JDBC FAQ.

Back to Top

Where can I find it?

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

Back to Top

Who maintains it?

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.

Back to Top

What has changed recently?

Material related to Oracle Database 10gR2 was added.

Back to Top

JDBC in General

What is JDBC?

JDBC technology is an API that lets you access virtually any tabular data source from the Java programming language. It provides cross-DBMS connectivity to a wide range of SQL databases, and now, with the new JDBC API, it also provides access to other tabular data sources, such as spreadsheets or flat files.

From Sun’s JDBC page.

Back to Top

Where can I learn more about JDBC?

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

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

JDBC API Tutorial and Reference, Third Edition

Java Programming with Oracle JDBC

Database Programming with JDBC and Java

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 Java Programming with Oracle JDBC

Back to Top

Where can I learn more about Java?

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

Oracle JDBC in General

What JDBC drivers does Oracle provide?

Oracle provides four different types of JDBC drivers, for use in different deployment scenarios. The 10.2.0 drivers can access Oracle 8.1.7 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 Oracle. The driver accesses the SQL engine by using internal function 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.

One customer has asked us for an Oracle JDBC Type1 driver. This is really an Oracle ODBC (not JDBC) driver, that you connect to using a JDBC-ODBC bridge driver. Oracle does supply an ODBC driver, but does not supply a bridge driver. Instead, you can get one of these JDBC-ODBC bridge drivers from http://java.sun.com/products/jdbc/drivers.html. This configuration should work, but a JDBC type2 or type4 driver will have more features, and will be faster.

Which JDBC drivers support which versions of Oracle Database?

JDBC 8.1.7 drivers can talk to RDBMS

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.0.1 drivers can talk to RDBMS

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

10.1.0

9.2.0

9.0.1

8.1.7

JDBC 10.1.0 drivers can talk to RDBMS

10.1.0

9.2.0

9.0.1

8.1.7

JDBC 10.2.0 drivers can talk to RDBMS

10.2.0

10.1.0

9.2.0

9.0.1

8.1.7

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

The following versions of the Oracle JDBC drivers are no longer supported.

JDBC 7.3.4 drivers can talk to RDBMS 7.3.4.

JDBC 8.0.4 drivers can talk to RDBMS 8.0.4 and 7.3.4.

JDBC 8.0.5 drivers can talk to RDBMS 8.0.5, 8.0.4 and 7.3.4.

JDBC 8.0.6 drivers can talk to RDBMS 8.0.6, 8.0.5, 8.0.4 and 7.3.4.

JDBC 8.1.5 drivers can talk to RDBMS 8.1.5, 8.0.6, 8.0.5, 8.0.4 and 7.3.4.

JDBC 8.1.6 drivers can talk to RDBMS 8.1.6, 8.1.5, 8.0.6, 8.0.5, 8.0.4 and 7.3.4.

Note: The ADT features in 8.1.5 driver and above only works for 8.1.5 database and above.

A related question is about versions NOT listed here. If its not listed here, its probably 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.0.3 – support for which was discontinued long ago. People still using 8.0.3 need to contact their support channels for upgrade info (some of these upgrades are free!).

Back to Top

Which JDBC drivers support which versions of Javasoft’s JDK?

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

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

Which JDBC drivers support which versions of JDBC?

In Oracle 8.1.5, we provide JDK 1.1.x drivers, and do 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.

In Oracle 8.1.6, we provide JDBC drivers for Java1.1.x and for Java2 (aka 1.2). Our 1.2 Oracle drivers are fully JDK 1.2 compliant. Both our 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()”).

In Oracle 8.1.7, we support:

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.

In Oracle 9.0.1 we support:

JDBC 2.0 Extensions except for:

using both global and local transactions on the same connection.

In Oracle 9.2.0 we support:

Partial support for JDBC 3.0:

transaction savepoints

using both global and local transactions on the same connection.

In Oracle 10.1.0 we support:

Full support for JDBC 3.0 except for:

retrieving auto-generated keys

result-set holdability

returning multiple result-sets.

In Oracle 10.2.0 we support:

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.

Back to Top

Which driver should I use?

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.

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.

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.

Back to Top

Where can I get the class files?

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.

Back to Top

What are all of these files for?

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

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.

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.

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

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

Some older (pre 10.1.0) versions of Oracle JDBC included some of the following files. These files are not shipped in Oracle Database 10g 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_g.zip

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

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.

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

Back to Top

What about the files for the Server Thin Driver and the Server Internal Driver?

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

Back to Top

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

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

Back to Top

Are Oracle’s JDBC drivers Y2K compliant?

Yes.

For more details, see the JDBC Y2K paper.

Back to Top

Are Oracle’s JDBC zip and jar files identical on all platforms?

Yes.

The actual .zip and .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 .zip and .jar files are identical and can be swapped around between platforms if you want.

Back to Top

Can third party vendors distribute Oracle’s JDBC drivers along with their own software?

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.

Back to Top

orai18n.jar is too big. How can I make it smaller?

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:

Unpack orai18n.jar into a temporary directory

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)

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.

What threads do the Oracle JDBC drivers create?

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

Statement timeout thread. This thread is created if you execute any statement with a timeout. Only one thread is created no matter how many statements or connections. This thread lasts the lifetime of the VM.

Implicit Connection Cache timeout thread. Used to enforce timeouts on the connection cache, and is enabled when at least one timeout property on the connection cache is enabled. There is one thread per connection cache. It lasts the lifetime of that connection cache.

Fast Connection Failover Event handler thread. Listener thread to receive HA events from RAC. Started only when FCF is enabled. There is one thread per connection cache. It lasts the lifetime of that connection cache.

FCF worker thread Processes UP/DOWN events. Started only when the events are being processed, and terminates after it is done its job.

Runtime Load Balancing Event Handler thread. This is the listener thread that receives RLB events from RAC. Started only when FCF is enabled. There is one thread per connection cache. It lasts the lifetime of that connection cache.

RLB Gravitate connection cache thread. This is started only when RLB is enabled, and when there is a need to gravitate connections in the cache to healthy instances. The thread terminates after it has finished its job.

OracleConnectionCacheImpl timeout thread. Deprecated. Started only when this old cache is used, to enforce timeouts on the old cache. There is one thread per connection cache. It lasts the lifetime of that connection cache.

What permissions do the Oracle JDBC drivers require?

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

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

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

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

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

Back to Top

Installation

How do I install the Thin driver?

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

Back to Top

How do I install the OCI driver?

The JDBC OCI driver generally requires an Oracle client-installation of the same version the driver. Starting from 10.1.0, the JDBC OCI driver is available for install with the OCI Instant Client feature, which does not require a complete Oracle client-installation. Please see “What are all of these files for” for details. Also refer to the documentation on OCI Instant Client install.

Back to Top

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

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

Back to Top

Where can I get the files for my platform?

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

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

Back to Top

DriverManager and DataSources

What is the difference between the DriverManager and a DataSource?

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

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

DriverManager.registerDriver(new OracleDriver());

You only have to register the driver once. Then call getConnection to create a new connection. There are three getConnection methods:

getConnection(String url)

getConnection(String url, Properties info)

getConnection(string url, String user, String password)

each of which returns a connection.

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

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

ds = new oracle.jdbc.pool.OracleDataSource();

ds.setURL(myURL);

conn = ds.getConnection(user, password);

Back to Top

Which connection cache should I use, OracleConnectionCacheImpl or the new Implicit connection cache?

You should use the new Implicit connection caching mechanism. This new connection caching mechanism is driver independent. It provides access to connection caching via OracleDataSource, and supports a number of new features such as

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

Note that the old connection cache, OracleConnectionCacheImpl, is deprecated and will be desupported in the next major release.

Back to Top

What is JDBC OCI Connection Pooling?

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

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

Back to Top

What is connection striping?

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

Back to Top

Connections

How do I open a connection to a database?

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

Back to Top

What are the various forms of getConnection for?

DriverManager defines three different forms of the getConnection method:

getConnection(String)

All of the information needed to describe the desired connection is encoded into the URL String parameter.

getConnection(String, Properties)

Some of the information is coded into the URL String parameter. The rest is passed as key value pairs in the Properties parameter. This is the most powerful and flexible of the three forms. There are properties that can be set using this form that cannot be set any other way.

getConnection(String, String, String)

This is a convenience method that passes the username and password as arguments rather than encoding them into the URL. It is frequently used in simple programs.

DataSource defines two getConnection methods:

getConnection()

This method returns a connection created using the URL, username, and password used to create the DataSource. This is the most commonly used form of getConnection in large applications.

getConnection(String, String)

This method returns a connection created using the URL used to create the DataSource, but with the username and password provided as arguments by the caller. You’ll know when you need to use this one.

Back to Top

What is the form of a URL?

The general form of a URL is

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

The <drivertype> is one of

thin

oci

kprb

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

Note that a URL like

jdbc:oracle:thin:/@mydatabase

has an empty username and password whereas this URL

jdbc:oracle:thin:@mydatabase

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

Back to Top

What is the form of the <database> description?

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

//<host>:<port>/<service>

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

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

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

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

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

jdbc:oracle:thin:@GL

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

Back to Top

How do I use the Properties argument?

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

java.util.Properties info = new java.util.Properties();

info.put (“user”, “scott”);

info.put (“password”,”tiger”);

info.put (“defaultRowPrefetch”,”15”);

getConnection (“jdbc:oracle:oci:@”,info);

The table below lists the connection properties that Oracle JDBC drivers support. Not all versions support all properties. The table below is the complete list for 10.1.0. If a property is not supported, it is silently ignored.

Connection Properties Recognized by Oracle 10.1.0 JDBC Drivers Key Value Comment user String The value of this property is used as the user name when connecting to the database. password String The value of this property is used as the password when connecting to the database. database String The value of this property is used as the SID of the database. server String The value of this property is used as the host name of the database. internal_logon String The value of this property is used as the user name when performing an internal logon. Usually this will be SYS or SYSDBA.

defaultRowPrefetch int The value of this property is used as the default number of rows to prefetch.

defaultExecuteBatch int The value of this property is used as the default batch size when using Oracle style batching.

processEscapes boolean If the value of this property is “false” then the default setting for Statement.setEscapeProccessing is false.

disableDefineColumnType boolean When this connection property has the value true, the method defineColumnType is has no effect. This is highly recommended when using the Thin driver, especially when the database character set contains four byte characters that expand to two UCS2 surrogate characters, e.g. AL32UTF8. The method defineColumnType provides no performance benefit (or any other benefit) when used with the 10.x.x Thin driver. This property is provided so that you do not have to remove the calls from your code. This is especially valuable if you use the same code with Thin driver and either the OCI or Server Internal driver.

DMSName String Set the name of the DMS Noun that is the parent of all JDBC DMS metrics.

DMSType String Set the type of the DMS Noun that is the parent of all JDBC DMS metrics.

AccumulateBatchResult boolean When using Oracle style batching, JDBC determines when to flush a batch to the database. If this property is true, then the number of modified rows accumulated across all batches flushed from a single statement. The default is to count each batch separately.

oracle.jdbc.J2EE13Compliant boolean If the value of this property is “true”, JDBC uses strict compliance for some edge cases. In general Oracle’s JDBC drivers will allow some operations that are not permitted in the strict interpretation of J2EE 1.3. Setting this property to true will cause those cases to throw SQLExceptions. There are some other edge cases where Oracle’s JDBC drivers have slightly different behavior than defined in J2EE 1.3. This results from Oracle having defined the behavior prior to the J2EE 1.3 specification and the resultant need for compatibility with existing customer code. Setting this property will result in full J2EE 1.3 compliance at the cost of incompatibility with some customer code. Can be either a system property or a connection property.

The default value of this property is “false” in classes12.jar and ojdbc14.jar. The default value is “true” in classes12dms.jar and ojdbc14dms.jar. It is true in the dms jars because they are used exclusively in Oracle Application Server and so J2EE compatibility is more important than compatibility with previous Oracle versions.

oracle.jdbc.TcpNoDelay boolean If the value of this property is “true”, the TCP_NODELAY property is set on the socket when using the Thin driver. See java.net.SocketOptions.TCP_NODELAY. Can be either a system property or a connection property.

defaultNChar boolean If the value of this property is “true”, the default mode for all character data columns will be NCHAR.

useFetchSizeWithLongColumn boolean If the value of this property is “true”, then JDBC will prefetch rows even though there is a LONG or LONG RAW column in the result. By default JDBC fetches only one row at a time if there are LONG or LONG RAW columns in the result. Setting this property to true can improve performance but can also cause SQLExceptions if the results are too big.

remarksReporting boolean If the value of this property is “true”, OracleDatabaseMetaData will include remarks in the metadata. This can result in a substantial reduction in performance.

includeSynonyms boolean If the value of this property is “true”, JDBC will include synonyms when getting information about a column.

restrictGetTables boolean If the value of this property is “true”, JDBC will return a more refined value for DatabaseMetaData.getTables. By default JDBC will return things that are not accessible tables. These can be non-table objects or accessible synonymns for inaccessible tables. If this property is true JDBC will return only accessible tables. This has a substantial performance penalty.

fixedString boolean If the value of this property is “true”, JDBC will use FIXED CHAR semantic when setObject is called with a String argument. By default JDBC uses VARCHAR semantics. The difference is in blank padding. With the default there is no blank padding so, for example, ‘a’ does not equal ‘a ‘ in a CHAR(4). If true these two will be equal.

oracle.jdbc.ocinativelibrary String Set the name of the native library for the oci driver. If not set, the default name, libocijdbcX (X is a version number), is used.

SetBigStringTryClob boolean Setting this property to “true” forces PreparedStatement.setString method to use setStringForClob if the data is larger than 32765 bytes. Please note that using this method with VARCHAR and LONG columns may cause large data to be truncated silently, or cause other errors differing from the normal behavior of setString.

oracle.jdbc.StreamBufferSize int Set size of the buffer for the InputStream/Reader obtained from getXXXStream/getXXXReader. The default size is 16k. The size passed should be at least 4096 or else 4096 is assumed.

OCINewPassword String Pass the value of new password to be set during logon. This could be typically used for resetting the password when the password has expired or when the account is in the grace period.

oracle.jdbc.RetainV9BehaviorForLongBind boolean This is applicable only for the thin driver.

Pass true to retain the V9 bind behavior for Long and potential long binds.

false is the default behavior which would emulate the same behavior as in OCI driver.

oracle.jdbc.FreeMemoryOnEnterImplicitCache boolean Clear the define buffers before caching the statement when Implicit statement caching is enabled.

Setting the value to true would enable the clearing of define buffers before caching of Statements in the Statement cache. false is the default value and this would behave in the same way as statement caching worked in prior releases.

oracle.jdbc.ReadTimeout int Read timeout while reading from the socket. This affects thin driver only. Timeout is in milliseconds.

Back to Top

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

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

Back to Top

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

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

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

Back to Top

Is there any limit on number of connections?

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

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

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

Back to Top

I’m getting OutofMemory Error when I set a higher default prefetch value.

The solution is to increase the startup size (-ms) and maximum size (-mx) of memory allocation pool.

Back to Top

What is a service connect string?

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

The basic format of a service URL is:

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

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

Examples:

jdbc:oracle:thin:@//myserver.com/customer_db

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

For more info see the JDBC User Guide.

How do I connect as SYSDBA or SYSOPER?

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

Properties props = new Properties();

props.put(“user”, “scott”);

props.put(“password”, “tiger”);

props.put(“internal_logon”, “sysoper”);

Connection conn = DriverManager.getConnection (url, props);

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

Back to Top

What encryption methods are supported by the Oracle JDBC drivers?

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

The JDBC Thin driver supports:

RC4_40

RC4_56

RC4_128

RC4_256

DES40C

DES56C

3DES112

3DES168

SSL

Back to Top

How do I turn on encryption and checksumming with the JDBC Thin driver?

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

Properties props = new Properties();

props.put(“oracle.net.encryption_types_client”,

“(3DES168)”);

props.put(“oracle.net.encryption_client”,

“REQUIRED”);

props.put(“oracle.net.crypto_checksum_types_client”,

“(MD5)”);

props.put(“oracle.net.crypto_checksum_client”,

“REQUIRED”);

Back to Top

What is proxy authentication?

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

Back to Top

Do Oracle JDBC drivers support SSL?

Yes, but the support is driver-specific. SSL encryption has been supported in the JDBC-OCI driver since Oracle JDBC 9.2.x, and is supported in the THIN driver starting in the 10.2. SSL authentication has been supported in the JDBC-OCI driver since Oracle JDBC 9.2.x, but is not yet supported in the Thin driver. Also note that when you use JDBC driver with SSL, the backend Oracle server should be at least 9.2.x.

Back to Top

Do the Oracle JDBC drivers support LDAP in the connection URL?

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

Back to Top

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

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

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

Back to Top

Statements, PreparedStatements, CallableStatements

What is defineColumnType and when should I use it?

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

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

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

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

Back to Top

Does defineColumnType force conversions on the Server?

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

Back to Top

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

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

Back to Top

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

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

Back to Top

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

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

Back to Top

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

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

Back to Top

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

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

Back to Top

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

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

Back to Top

Why so many different mechanisms?

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

Back to Top

What is new in 10g R2?

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

Back to Top

What are the sizes for the setString. setCharacterStream, and setAsciiStream?

See the table below.

Form

Stmt

Driver

Lower Limit

Upper Limit

Bind mechanism

Note

All

Null

All

SQL

Client

1 char

32766 chars

Direct

All

SQL

Client

32767 chars

2147483647 bytes

Stream

All

SQL

Client

2147483648 bytes

2147483647 chars

Temp Clob

CHAR

Server

1 char

65536 bytes

Direct

1, 2

NCHAR

1 char

4000 bytes

Direct

NCHAR

4001 bytes

2147483647 chars

Temp Clob

CHAR

65537 bytes

2147483647 bytes

Stream

2147483647 bytes

2147483647 chars

Temp Clob

All

PL/SQL

All

1 char

32512 chars

Direct

All

PL/SQL

All

32513 chars

2147483647 chars

Temp Clob

Back to Top

What are the size limit for binary data via setBytes and setBinaryStream?

See table below:

Stmt

Driver

Lower Limit

Upper Limit

Bind mechanism

Note

SQL

Client

32767 bytes

2147483648 bytes

Stream

All

Null

SQL

All

2000 bytes

Direct

SQL

All

2000 bytes

2147483647 bytes

Stream

3, 4

PL/SQL

All

32512 bytes

Direct

PL/SQL

All

32513 bytes

2147483647 bytes

Temp blob

Back to Top

What are the size limits for the proprietary methods setBytesForBlob, setStringForClob in oracle.jdbc.OraclePreparedStatement?

See table below:

API

FORM

Stmt

Driver

Lower Limit

Upper Limit

Bind mechanism

Note

setBytesForBlob

n/a

All

Null

All

Client

1 byte

2000 bytes

Direct

All

Client

2001 bytes

21474836487 bytes

Temp blob

setStringForClob

All

Null

All

Client

1 char

32766 chars

Direct

All

Client

32767 chars

2147483647 chars

Temp clob

All

Server

1 char

4000 bytes

Direct

All

Server

4001 bytes

2147483647 chars

Temp clob

Notes:

The server side internal driver cannot convert data for CLOB parameters of SQL statements that is larger than 4000 bytes. For NCHAR parameters the driver converts the data to a temporary nclob. This can be done since there are no NLONG parameters.

This limitation does not exist for PL/SQL. 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 clob_tab (clob_col ) values (? )

could be replaced with

begin Insert into clob_tab (clob_col ) values (? ); end;

The Oracle specific method setStringForClob may be used as an alternate workaround. This will be available in the first patch set for 10gR2

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;

The Oracle specific method setBytesForBlob may be used as an alternate workaround. This will be available in the first patch set for 10gR2

Why is this so complicated?

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

Back to Top

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

Yes.

Back to Top

What about CallableStatements and procedures with IN OUT parameters?

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

Back to Top

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

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

Back to Top

What about existing code?

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

Back to Top

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

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

Back to Top

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

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

Back to Top

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

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

Back to Top

LONG RAW and LONG column types are deprecated. Why are there new uses of the setXXXStream API’s?

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

Back to Top

The LOB API’s are much more flexible, right?

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

Back to Top

Why can’t I create a PreparedStatement that does select * from tab where id in (?, ?, ?, …)?

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

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

Back to Top

ResultSets

What does “Exhausted Resultset: prepare_for_new_get” means?

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

ResultSet rset = stmt.executeQuery (“select ROWID from EMP”);

…

rset.close (); // or stmt.close ();

rset.getString (1);

Back to Top

Simple Data Types

What is going on with DATE and TIMESTAMP?

This section is on simple data types. 🙂

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

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

There are several ways to address this problem:

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

Back to Top

What is the longest value I can bind?

Method Column Type Maximum length

setBytes LONG 4k bytes

setBytes LONG RAW 2G bytes

setString LONG 32k chars (SetBigStringTryClob=”false”)

4k chars (SetBigStringTryClob=”true”)

setString CLOB 2G chars

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

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

Back to Top

Advanced Data Types (Objects)

Your question here.

BFILEs, BLOBs, CLOBs

What is DBMS_LOB.ERASE doing?

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

Back to Top

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

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

Back to Top

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

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

Back to Top

Is writing into BLOBS is slower than inserting LONG RAWs ?

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

Back to Top

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

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

Back to Top

When I get a CLOB from the database and then update it, sometimes my changes don’t appear in the database. Why?”

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

Back to Top

REF types

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

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

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

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

public class SomeREF implements java.io.Serializable {

String typeName;

byte[] bytes;

public SomeREF (oracle.sql.REF ref) throws SQLException {

this.typeName = ref.getBaseTypeName ();

this.bytes = ref.getBytes ();

}

public oracle.sql.REF toREF (Connection conn) throws SQLException {

return new oracle.sql.REF (new oracle.sql.StructDescriptor

(typeName,conn),conn, bytes);

}

Back to Top

How can I create a new REF?

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

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

create or replace type point as object (x number, y number);

create table point_values_table of point;

create table point_ref_table (p ref point);

You can insert a new point value in point_values_table, a new ref to it in the point_ref_table and return the REF to the client with following code:

oracle.jdbc.driver.OracleCallableStatement call =

(oracle.jdbc.driver.OracleCallableStatement) conn.prepareCall

(“declare x ref point; “ + “begin insert into point_values_table p values

(point(10, 20))” + “ returning ref(p) into x; “ + “ ? := x; “ + “end;”);

call.registerOutParameter (1, oracle.jdbc.driver.OracleTypes.REF,”SCOTT.POINT”);

call.execute ();

oracle.sql.REF ref = (oracle.sql.REF)call.getObject (1);

Back to Top

OPAQUE types

What are OPAQUE types?

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

Back to Top

Row Sources

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

A bean’s properties can be classified as:

object creation properties

runtime properties

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

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

Back to Top

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

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

Back to Top

Thin Driver

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

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

Back to Top

With which Browsers has the thin JDBC Driver been tested?

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

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

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

Back to Top

OCI Driver

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

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

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

Back to Top

Does OCI Driver work Microsoft’s Java VM ?

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

However, our 8.1.6 driver and above won’t have this limitation as they have been ported to JNI 1.1.

Back to Top

I’m getting Unsatisfied Link Error with Oci 8 Driver ?

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

Back to Top

Can the JDBC OCI Driver be used with Java applets?

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

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

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

Back to Top

Server Internal Driver

When should I use the Server Internal Driver?

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

Back to Top

Server Thin Driver

When should I use the Server Thin Driver?

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

Back to Top

Errors

DriverManager.getConnection gives the Error: “No suitable driver”

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

Back to Top

Error Message: “Unimplemented Method Interface”

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

Back to Top

Error Message: “UnsatisfiedLinkError with OCI driver”

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

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

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

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

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

Back to Top

Error Message: “ORA-1019: unable to allocate memory.”

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

Back to Top

Error Message: “ORA-01000: maximum open cursors exceeded”

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

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

Back to Top

Error Message: “ORA-01002: fetch out of sequence”

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

Hence, the solution is to set autocommit to false.

Back to Top

Error Message: “ORA-12705: invalid or unknown NLS parameter value specified”

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

Back to Top

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

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

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

Back to Top

Error Message: “Invalid driver designator”

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

Back to Top

Cant get JDBC Drivers to work with the Oracle WebServer

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

Back to Top

Error Message: FileNotFound Exception

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

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

Back to Top

Getting Security Exceptions from Netscape when Connecting to Oracle

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

Back to Top

Error Message: The JDBC Thin Driver Gives Me “invalid character” Errors for Unicode Literals

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

ResultSet rset = stmt.executeQuery (“select * from \”\u6d82\u6d85\u6886\u5384\””);

Back to Top

INSERT or UPDATE operations are slow

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

connection.setAutoCommit(false);

See Batching Updates in the JDBC Documentation for information about the Oracle extensions for batching calls to INSERT and UPDATE. Batching these commands can achieve even more speed than turning off autoCommit.

Error Message: “Connection reset by peer”

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

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

Back to Top

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

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

Back to Top

What does “Protocol Violation” mean?

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

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

Back to Top

Demo Programs

Are there any JDBC demo programs?

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

Back to Top

How do I run the demos?

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

Back to Top

What should I do when error happens when I run a demo?

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

classpath

correct jdk versions

refer to Samples-Readme.txt, Makefile, and each .java file for pre-testing requirements.

Oracle JDBC Trace Facility

What is the JDBC Trace Facility?

The JDBC Trace Facility is a runtime debugging aid built into 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 from ojdbc14_g.jar and ojdbc14dms_g.jar.

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

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

Back to Top

How do I turn on the trace?

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

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

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

oracle.jdbc.driver.OracleLog.startLogging();

This sends the trace to System.out. To turn it off call

oracle.jdbc.driver.OracleLog.stopLogging();

You can also turn on the trace by setting the system property oracle.jdbc.Trace to “true”.

java -Doracle.jdbc.Trace=true MyApp

Setting any of the other JDBC Trace Facility properties described below implicitly sets oracle.jdbc.Trace to “true”.

How do I control the volume of trace output?

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

oracle.jdbc.driver.OracleLog.startLogging();

myApp.suspectCode();

oracle.jdbc.driver.OracleLog.stopLogging();

Often this is not possible. You can also reduce the number of trace messages by setting the trace volume.

oracle.jdbc.driver.OracleLog.setLogVolume(1);

The default value is 2. The maximum value is 3, but that currently does not produce a whole lot more than 2. 1 is much less than the default.

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

oracle.jdbc.driver.OracleLog.setMaxPrintBytes(100);

java -Doracle.jdbc.MaxPrintBytes=100 MyApp

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

java -Doracle.jdbc.PrintFields=none MyApp

The valid values are:

none—just prints the message

default

thread—same as default plus the thread name

all

Where does the trace output go?

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

java -Doracle.jdbc.LogFile=/tmp/jdbc.log MyApp

or by calling the setLogStream api.

oracle.jdbc.driver.OracleLog.setLogStream(System.err);

Setting the log stream starts the trace as well. You can turn the trace off by setting the log stream to null.

Back to Top

How can I turn off DMS in a DMS enabled jar?

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

System.setProperty(

“oracle.dms.console.DMSConsole”,

“oracle.dms.instrument_stub.DMSConsole”);

before executing any DMS code. Another way would be to use the -D option with the Java VM.

java

Doracle.dms.console.DMSConsole=oracle.dms.instrument_stub.DMSConsole MyApp

Back to Top

Development Tools and Environments

Can I debug JDBC programs with Symantec Visual Cafe?

Yes, it is possible to debug Java programs that use Oracle Thin JDBC Driver with Symantec Visual Cafe? Java development tool. However, it is not possible to debug JDBC OCI programs with Visual Cafe?.

Back to Top

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

Yes, it is possible to debug Java programs that use Oracle Thin JDBC Driver with Microsoft Visual J++ Java development tool. Further, it is also possible to debug JDBC OCI programs in Visual J++ but the driver has been built for the Javasoft Java VM. It is therefore necessary to modify the Visual J++ environment to run using the Javasoft Java VM instead of the Microsoft VM.

For more information, see the following web site: http://www.javaworld.com/javaworld/javatips/jw-javatip25.html

Back to Top

Supported Features

Can the JDBC Drivers access PL/SQL Stored Procedures?

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

SQL92 Syntax

CallableStatement cs1 = conn.prepareCall (“{call proc (?,?)}”);

CallableStatement cs2 = conn.prepareCall (“{? = call func (?,?)}”);

Oracle Syntax

CallableStatement cs1 = conn.prepareCall (“begin proc (:1,:2); end;”);

CallableStatement cs2 = conn.prepareCall (“begin :1 := func (:2,:3); end;”);

Back to Top

Do the JDBC Drivers support streaming?

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

Back to Top

Do the JDBC Drivers support multibyte character sets?

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

Back to Top

Do the JDBC Drivers work with firewalls?

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

Stateful Inspection Firewalls: Firewalls from Checkpoint, SunSoft, and CISCO Systems.

Proxy-based Firewalls: Firewalls from Milkyway Networks, Trusted Information Systems, Raptor, Secure Computing Corporation, and Global Internet.

Do Oracle’s JDBC drivers support PL/SQL tables/result sets/records/booleans?

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

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

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

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

PROCEDURE MY_PROC (n NUMBER) IS

BEGIN

IF n=0

THEN proc(false);

ELSE proc(true);

END IF;

END;

PROCEDURE PROC (b BOOLEAN) IS

BEGIN

…

END;

Back to Top

Is failover supported?

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

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

Back to Top

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

We do not support the getCursorName and setCursorName JDBC entrypoints. Instead we provide access to ROWIDs, which provide similar functionality. Oracle JDBC drivers implement ROWID as a Java string.

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:

Statement stmt = conn.createStatement ();

// Query the employee names with “FOR UPDATE” to lock the rows.

// Select the ROWID to identify the rows to be updated.

ResultSet rset = stmt.executeQuery (“select ENAME, ROWID from EMP for update”);

// Prepare a statement to update the ENAME column at a given ROWID

PreparedStatement pstmt = conn.prepareStatement (“update EMP set ENAME = ? where ROWID = ?”);

// Loop through the results of the query

while (rset.next ())

{

String ename = rset.getString (1);

String rowid = rset.getString (2); // Get the ROWID as a String

pstmt.setString (1, ename.toLowerCase ());

pstmt.setString (2, rowid); // Pass ROWID to the update statement

pstmt.executeUpdate (); // Do the update

}

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

Back to Top

How do the JDBC drivers support Oracle REFCURSOR datatypes?

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

CallableStatement cstmt;

ResultSet cursor;

// Use a PL/SQL block to open the cursor

cstmt = conn.prepareCall

(“begin open ? for select ename from emp; end;”);

cstmt.registerOutParameter (1, OracleTypes.CURSOR);

cstmt.execute ();

cursor = ((OracleCallableStatement)cstmt).getCursor (1);

// Use the cursor like a normal ResultSet

while (cursor.next ())

{System.out.println (cursor.getString (1));}

Back to Top

Does ANO work with JDBC drivers?

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

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

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

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

Back to Top

**Can I serialize oracle.sql.* datatypes?**

Oracle JDBC drivers do not allow you to serialize any of the datatypes under oracle.sql.* (CHAR, ROWID, REF, NUMBER, etc). Future drivers may allow you to serialize some oracle.sql.* classes. (See bug 3144878.)

Back to Top

Do the JDBC Drivers support Objects or Collections?

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

Back to Top

Can I use WaitOption and AutoRollback?

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

public void setAutoRollback (int autoRollback);

public int getAutoRollback();

public void setWaitOption(int waitOption);

public int getWaitOption();

Back to Top

Can a Java Stored Procedure in one database instance open a connection to another database instance?

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

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

Back to Top

Performance

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

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

Back to Top

Which is faster, Statements or PreparedStatements?

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

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

Back to Top

java.util.logging

Why doesn’t OracleLog work when I use ojdbc14_g.jar?

As of release 10.1.0 the Oracle JDBC drivers that support JDK 1.4 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 when using ojdbc14_g.jar. You should use java.util.logging instead.

Back to Top

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

I thought you’d never ask.

First, you must use either ojdbc14_g.jar or ojdbc14dms_g.jar. These are the only jar files that include logging code. ojdbc14.jar and ojdbc14dms.jar do not include any logging or trace code. classes12_g.jar and classes12dms_g.jar use OracleLog to provide trace output. See the section on the Oracle JDBC Trace Facility for information on how to use OracleLog. Be sure that there are no extra Oracle JDBC jar files in your classpath.

Second, you must turn on Oracle JDBC logging. You do this by calling oracle.jdbc.driver.OracleLog.setTrace(true). You can turn off all Oracle JDBC trace output by calling setTrace(false). This will generate minimal trace output. To get more and more useful output you need to configure java.util.logging.

Back to Top

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

The JDBC code creates a number of Loggers. In order to get interesting output you need to set the logLevel on each of these Loggers and add a PrintHandler 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 call oracle.jdbc.driver.OracleLog.setTrace(true) to get any JDBC trace output. You can toggle trace output on and off without reconfiguring the Loggers. setTrace doesn’t mess with the Loggers at all. If you don’t want to change your source to call OracleLog.setTrace you can add -Doracle.jdbc.Trace=true to your java execution command. This will log the entire execution.

Back to Top

What about the Server-Side Internal driver?

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

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

Put OracleLog.properties into $ORACLE_HOME.

Release Specific Questions

7.3.X

Can I use the Oracle7 JDBC Drivers with Oracle8?

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

Why am I not able insert >2K data into a long column with setString using 8.0.X THIN driver?

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.

Back to Top

8.1.5 (8i)

Does our drivers support the 8i ‘Connect through proxy’ functionality?

Yes, through SQLNET Proxies.

Back to Top

Is it possible to return multi-row query results from a stored procedure?

Yes, as a Ref Cursor.

Back to Top

Is DML Returning Supported ?

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.

Back to Top

Is Multi-tier Authentication supported ?

No.

Back to Top

8.1.7 (8i r2)

Is DML Returning Supported ?

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

Back to Top

9.0.1 (9i)

Is DML Returning Supported ?

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

Back to Top

9.2.0 (9i r2)

Is DML Returning Supported ?

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

Back to Top

10.1.0 (10g r1)

Is DML Returning Supported ?

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.

Back to Top

10.2.0 (10g r2)

Is DML Returning Supported ?

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

Back to Top

E-mail this page

Printer View