 |
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.
Back to Top
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:
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:
Back to Top
Oracle JDBC in General
What JDBC drivers does Oracle provide?
Oracle provides four different types of JDBC drivers, for use in
different deployment scenarios. 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.
Back to Top
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
- JDBC 10.1.0 drivers can talk to RDBMS
- 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
Back to Top
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.
Back to Top
What threads do the Oracle
JDBC drivers create?
The drivers create a number of different threads, but only on an
as-needed basis. They do not create the threads unless your code makes
use of the feature that depends on the thread. All of these threads
are daemon threads.
-
Statement timeout thread. This thread is created if you execute any
statement with a timeout. Only one thread is created no matter how
many statements or connections. This thread lasts the lifetime of
the VM.
-
Implicit Connection Cache timeout thread.
Used to enforce timeouts on the connection
cache, and is enabled when at least one timeout
property on the connection cache is enabled. There is one thread per
connection cache. It lasts the lifetime of that connection cache.
-
Fast Connection Failover Event handler thread.
Listener thread to receive HA events from RAC.
Started only when FCF is enabled. There is one thread per
connection cache. It lasts the lifetime of that connection cache.
-
FCF worker thread Processes UP/DOWN events. Started only when the
events are being processed, and terminates after it is done its
job.
-
Runtime Load Balancing Event Handler thread.
This is the listener thread that receives RLB events
from RAC. Started only when FCF is enabled. There is one thread per
connection cache. It lasts the lifetime of that connection cache.
-
RLB Gravitate connection cache thread.
This is started only when RLB is enabled, and when
there is a need to gravitate connections in the cache
to healthy instances. The thread terminates after it has finished
its job.
-
OracleConnectionCacheImpl timeout thread.
Deprecated. Started only when this old cache is used,
to enforce timeouts on the old cache. There is one thread per
connection cache. It lasts the lifetime of that connection cache.
Back to Top
What permissions do the
Oracle JDBC drivers require?
When your application is run with a SecurityManager enabled (which it
should in production) certain operations are priviliged. In order to
do those operations the code must be granted the appropriate
permissions. 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
The <username/password> is either empty or of the form
<username>/<password>
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>
- <host>:<port>:<SID>
- <TNSName>
The following URL connects user scott with
password tiger to a database with service
orcl (Important: see more on
services) through port 1521 of host myhost, using the
Thin driver.
jdbc:oracle:thin:scott/tiger@//myhost:1521/orcl
This URL connects to the same
database using the the OCI driver and the SID inst1 without
specifying the username or password.
jdbc:oracle:oci:@myhost:1521:inst1
This URL connects to the database named GL in the
tnsnames.ora file using the Thin driver and with no username or
password specified. The username and password must be specifed elsewhere.
jdbc:oracle:thin:@GL
Support for using TNSNAMES entries with the Thin driver
is new in release 10.2.0.1.0. In order for this to work you must have
configured the file tnsnames.ora correctly
Back to Top
How do I use the Properties
argument?
In addition to the URL, use an object of the standard Java
Properties class as input. For example:
java.util.Properties info = new java.util.Properties();
info.put ("user", "scott");
info.put ("password","tiger");
info.put ("defaultRowPrefetch","15");
getConnection ("jdbc:oracle:oci:@",info);
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.
Back to Top
How do I connect as SYSDBA or SYSOPER?
The only way to do this is to use the Properties object when
connecting, rather than specifying the username and password as
strings. Put the username into the "user" property, and the password
into the "password" property. Then, put the mode into the
"internal_logon" property. Something like the following:
Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("internal_logon", "sysoper");
Connection conn = DriverManager.getConnection (url, props);
When connecting as SYSDBA or SYSOPER using the Thin driver, the RDBMS
must be configured to use a password file. See "Creating and
Maintaining a Password File" in the "Oracle Database Administrator's
Guide".
Back to Top
What encryption methods are supported by the Oracle JDBC drivers?
The JDBC OCI driver supports the same algorithms as the database
server.
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 | 
Bookmark