Yes, if it isn't already obvious.

For more details, see the JDBC Y2K paper.

Yes.

The actual .jar files are identical on all platforms. We only build these on one platform, and just copy them over to the others. The only difference is the .so (or .dll) file that is called by the OCI client. The .jar files are identical and can be swapped around between platforms if you want.

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

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

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

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

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

1. Unpack orai18n.jar into a temporary directory

2. Delete all files in your temporary directory EXCEPT the character set glb files that your application uses AND the following 18 class files:
   oracle/i18n/util/ClassLoaderChooser.class oracle/i18n/util/ConverterArchive.class oracle/i18n/util/GDKMessage.class oracle/i18n/util/GDKOracleMetaData.class oracle/i18n/util/OraClassLoader.class oracle/i18n/util/OraResourceBundle.class oracle/i18n/util/message/Messages.class oracle/i18n/text/converter/CharacterConverter12Byte.class oracle/i18n/text/converter/CharacterConverterOGS.class oracle/i18n/text/converter/CharacterConverter1Byte.class oracle/i18n/text/converter/CharacterConverterGB18030.class oracle/i18n/text/converter/CharacterConverterJAEUC.class oracle/i18n/text/converter/CharacterConverterLC.class oracle/i18n/text/converter/CharacterConverterLCFixed.class oracle/i18n/text/converter/CharacterConverterZHTEUC.class oracle/i18n/text/converter/CharacterConverter2ByteFixed.class oracle/i18n/text/converter/CharacterConverterSJIS.class oracle/i18n/text/converter/CharacterConverterShift.class

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

   select NLS_CHARASET_ID('ja16sjis') from DUAL;

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

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

   oracle/i18n/data/	(lx20<OracleCharacterSetId>.glb files)
   oracle/i18n/util/	(3 class files)
   oracle/i18n/util/message/	(1 class files)
   oracle/i18n/text/converter/	(11 class files)

3. Create a new jar file from the temporary directory using a different file name other than orai18n.jar and add the new jar file to your CLASSPATH.

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

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

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

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

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

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

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

The classes in the package oracle.jdbc.driver are desupported in 11.1. You should use the types in the package oracle.jdbc instead. Typically all that is required is to replace oracle.jdbc.driver with oracle.jdbc. If that doesn't work then your code is using classes or methods that were never intended for public use. You will have to find another solution. There is no workaround or backward compatibility mode.

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

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

See the answer to the previous question.

Customer use of the package is desupported. We did not remove the package or the classes it contains. This package is the core implementation code for the driver. We could have renamed the package but did not because it would have been a lot of work for not much gain.

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

Yes.

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

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

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

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

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

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

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

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

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

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

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

each of which returns a connection.

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

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

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

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

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

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

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

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

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

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

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

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

DriverManager defines three different forms of the getConnection method:

getConnection(String)

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

getConnection(String, Properties)

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

getConnection(String, String, String)

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

DataSource defines two getConnection methods:

getConnection()

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

getConnection(String, String)

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

The general form of a URL is

The <drivertype> is one of

• thin
• oci
• kprb

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

Note that a URL like

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The basic format of a service URL is:

Examples:

For more info see the JDBC User Guide.

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

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

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

In 11.1 and 11.2 the JDBC Thin driver supports:

• RC4_40
• RC4_56
• RC4_128
• RC4_256
• DES40C
• DES56C
• 3DES112
• 3DES168
• SSL
• AES256
• AES192
• AES128

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Notes:

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

   could be replaced with

   begin Insert into blob_tab (blob_col) values (? ); end;
2. The Oracle specific method setBytesForBlob may be used as an alternate workaround. This will be available in the first patch set for 10gR2

Notes:

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

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

Yes.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

This section is on simple data types. :-)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The old behavior was incorrect. See bug 4322830.

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

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

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

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

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

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

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

Row Sources

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

A bean's properties can be classified as:

• object creation properties
• runtime properties

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

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

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

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

Back to Top

Thin Driver

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

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

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

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

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

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

Back to Top

OCI Driver

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

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

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

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

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

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

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

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

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

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

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

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

Back to Top

Server Internal Driver

When should I use the Server Internal Driver?

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

Back to Top

Server Thin Driver

When should I use the Server Thin Driver?

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

Back to Top

Errors

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

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

Back to Top Error Message: "Unimplemented Method Interface"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Hence, the solution is to set autocommit to false.

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

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

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

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

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

Back to Top Error Message: "Invalid driver designator"

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

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

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

Back to Top Error Message: FileNotFound Exception

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

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

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

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

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

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

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

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

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

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

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

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

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

Back to Top What does "Protocol Violation" mean?

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

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

Back to Top

Demo Programs

Are there any JDBC demo programs?

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

Back to Top How do I run the demos?

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

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

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

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

Back to Top

Oracle JDBC Trace Facility

What is the JDBC Trace Facility?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

java -Doracle.jdbc.PrintFields=none MyApp The valid values are:

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

Back to Top Where does the trace output go?

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

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

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

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

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

Development Tools and Environments

Can I debug JDBC programs with Symantec Visual Cafe?

Visual Cafe is no longer supported.

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

Visual J++ is no longer supported.

Back to Top

Supported Features

Can the JDBC Drivers access PL/SQL Stored Procedures?

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

• SQL:2003 Syntax CallableStatement cs1 = conn.prepareCall ("{call proc (?,?)}"); CallableStatement cs2 = conn.prepareCall ("{? = call func (?,?)}");
• Oracle Syntax CallableStatement cs1 = conn.prepareCall ("begin proc (:1,:2); end;"); CallableStatement cs2 = conn.prepareCall ("begin :1 := func (:2,:3); end;");

Back to Top Do the JDBC Drivers support streaming?

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

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

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

Back to Top Do the JDBC Drivers work with firewalls?

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

• Stateful Inspection Firewalls: Firewalls from Checkpoint, SunSoft, and CISCO Systems.
• Proxy-based Firewalls: Firewalls from Milkyway Networks, Trusted Information Systems, Raptor, Secure Computing Corporation, and Global Internet.

Back to Top Do Oracle's JDBC drivers support PL/SQL tables/result sets/records/booleans?

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

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

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

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

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

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

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

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

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

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

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

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?

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

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

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

Back to Top Can I use WaitOption and AutoRollback?

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

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

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

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

Back to Top

Performance

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

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

Back to Top Which is faster, Statements or PreparedStatements?

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

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

Back to Top

java.util.logging

Why doesn't OracleLog work?

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

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

I thought you'd never ask.

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

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

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

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

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

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

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

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

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

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

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

Put OracleLog.properties into $ORACLE_HOME.

Back to Top

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.

Back to Top

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 Which version of JDBC is supported?

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

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

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

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

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 Can 8.1.7 connect to a 9.0.1 database?

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.

Back to Top Which version of JDBC is supported?

Oracle 8.1.7, supports:

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

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

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