Technical FAQ: Questions about jDriver for Oracle
FAQ Index
Error using FOR UPDATE statement with Oracle 8
Why have I been receiving the following error message from my Oracle 8
server when using a FOR UPDATE statement: ORA-01002: fetch out of
sequence?
There is a problem in the Oracle 8
server that causes this error when using a FOR UPDATE
statement with AUTOCOMMIT turned on (which is the default state
when using JDBC). This is known to happen on Oracle 8.0 and 8.1 on
Solaris and on Oracle 8.1 on Windows NT. If you turn AUTOCOMMIT
off, you will not receive this error. Since this problem is due
to a change in the Oracle 8 server, you should contact Oracle support
for more information.
OCIW32.DLL error
I have been receiving the following error message when using your JDBC
driver for Oracle: "The ordinal 40 could not be loaded in the dynamic
link library OCIW32.dll"
This problem is caused by an out-of-date version of OCIW32.DLL in your
system directory. Some programs install this file in the system
directory in order to run. If you remove this file from the system
directory you should no longer receive this error.
Doesn't the jDriver for Oracle
driver support all of the transaction isolation levels?
Our servlet application uses the Oracle JDBC
thin drivers to access a database that includes BLOB fields. We
installed jDriver for Oracle and the same code fails with the following
exception:
com.roguewave.jdbtools.v2_0.LoginFailureException:
TRANSACTION_READ_UNCOMMITTED isolation level not allowed
The Stack Trace:
com.roguewave.jdbtools.v2_0.LoginFailureException:
TRANSACTION_READ_UNCOMMITTED isolation level not allowed
at com.roguewave.jdbtools.v2_0.jdbc.JDBCServer.createConnection
(JDBCServer.java :46)
at com.roguewave.jdbtools.v2_0.ConnectionPool.getConnection_
(ConnectionPool.jav a:412)
at com.roguewave.jdbtools.v2_0.ConnectionPool.getConnection
(ConnectionPool.java :109)
We set the Isolation_level to 1 in our code that calls the
RogueWave JDBCServer class. This works with the Oracle thin driver but
fails with jDriver for Oracle. Any clues?
jDriver for Oracle supports the following transaction isolation levels:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
According to the Oracle documentation, the Oracle DBMS only supports
these two isolation levels. Unlike other JDBC drivers, WebLogic's
drivers throw an exception if you try to use an isolation level that
is unsupported. Some drivers silently ignore attempts to set an
unsupported isolation level. WebLogic suggests testing whether the
Oracle thin driver is not just ignoring settings for unsupported
isolation events.
Running Oracle under HP-UX 10.x
We're running Oracle on HP-UX 10.2. Can we use jDriver for Oracle?
jDriver for Oracle requires HP-UX 11.0. Native threads are not available
on HP-UX 10.2 and there are other problems with the JVMs on that
OS. We recommend that you run jDriver for Oracle with HP-UX 11.0 with the
latest available JDK and HP patches.
See the next FAQ for more information about
jDriver for Oracle on HP-UX 11.0.
Running Oracle under HP-UX 11.0
When I run dbping to test my connection to Oracle, I get the following
error message:
java.sql.SQLException: ORA-01019: unable to allocate memory in the user
I'm using Oracle server 7.3.4, with Oracle 7.3.4 client libraries and
jDriver for Oracle on HP-UX 11.0. What's going on?
You need to install Oracle 8.0.4 client
libraries. jDriver for Oracle
drivers running under HP-UX 11.0 require the Oracle 8.0.4 client
environment and a server version of at least 7.3.4.
Also, make sure that the environment variables ORACLE_HOME and
ORACLE_SID are set properly.
Using multibyte character sets
with jDriver for Oracle
I am having trouble using Unicode codesets with the jDriver for Oracle
driver. How can I use a codeset in my Oracle database with the
WebLogic JDBC drivers?
There are two important things you must do to use Unicode
codesets:
- You must install the appropriate codeset when you install
Oracle. If you did not do this in the original installation, you will
need to re-run the Oracle installer and install the proper codeset.
- You must define the NLS_LANG variable in the environment where the
JDBC driver is running. You can do this by assigning the proper
codeset to NLS_LANG in the shell from where you start the WebLogic
Server.
The Developers Guide has more information about internationalization support. For general information
about Unicode see the Unicode website. For a list of Unicode language
abbreviations, see the JavaSoft website.
Do you support Oracle 8?
Do you support Oracle 8? All features, or only a subset, and if so,
which features are supported? Can I use WebLogic to talk to both a 7.3
server and an 8.0 server?
See the WebLogic Platforms
page for details on Oracle client and server support.
What version of Oracle client/server does jDriver for Oracle require?
jDriver for Oracle is based on version 7.3.4 of the Oracle Call Interface
(OCI) for all database operations and therefore inherits the
requirements of any OCI application. According to Oracle, you must use
the same client version as the application was built with. On most
platforms, jDriver for Oracle is built with Oracle OCI version 7.3.4. (On
HP-UX 11.0, jDriver for Oracle uses the Oracle 8 client.) At the very
least, you must run jDriver for Oracle in a 7.3.4 client
environment. According to Oracle, it is also imperative that you are
using a 7.3.4 or higher server as well.
Using jDriver for Oracle with Oracle 7.x client libraries and Oracle 8
I am using jDriver for Oracle 3.0.x and Oracle 7.x client
libraries with Oracle 8. How do I connect the 7.x product to the
Oracle 8?
According to Oracle, the TNSNAMES.ORA file has been moved from
$ORACLE_HOME/network/admin to $ ORACLE_HOME/net80/admin. You will need
to copy the file:
$ORACLE_HOME/net80/admin/tnsnames.ora
to
$ORACLE_HOME/network/admin/tnsmname.ora You will
also need to install SQL*Net 2.3 because some 7.x products may have
problems with Beta versions of 8.x that are installed with Oracle
versions before 8.03.
Hanging problem with Oracle
7.3.3 and jDriver for Oracle on Windows NT
July 1997: We have had reports of problems with jDriver for Oracle
hanging on Windows NT. We have traced this to the Oracle 7.3.3 client
libraries, which was released in late June 1997.
The solution is to install the 7.3.4 client libraries, which
are required with jDriver for Oracle
Release 3.0 and later.
Can I use jDriver for Oracle with IBM's VisualAge for Java?
Does the WebLogic jDriver for Oracle driver work with IBM VisualAge for Java?
Because of a bug in VisualAge JNI, jDriver for Oracle does not currently
work with VisualAge for Java. The jDriver for Oracle native layer receives
incorrect values from VisualAge JNI. Until the VisualAge bug is fixed,
you should use a type 4 JDBC driver for Oracle, such as Oracle's own
JDBC driver.
Does jDriver for Oracle work with Oracle 7.3?
Does the WebLogic jDriver for Oracle driver work with Oracle 7.3? If so,
does it use the multithreading
capability of Oracle 7.3 when making multiple simultaneous connection
with Oracle using the same username?
Yes, jDriver for Oracle does work with Oracle 7.3. In fact, for WebLogic
version 2.5.4 and later, we require the use of the Oracle 7.3.4 client
libraries because of several Oracle bugs that were fixed in Oracle's
7.3.4 release. jDriver for Oracle should take advantage of the MTS, since
this functionality is below the level of OCI and SQLNet.
How do I use OS authentication
with jDriver for Oracle and WebLogic JDBC connection pools?
I'd like to set up a WebLogic JDBC connection pool so that it uses the
authentication associated with the user who is running my client
application. I'm using jDriver for Oracle. How can I do that?
Using OS authentication
in connection pools essentially means that you are using the userid
of the user that started the WebLogic Server. This means that
database security will rely strictly on the security of WebLogic; that
is, if you are allowed to make a client connection to the WebLogic
Server and access the pool, then you can get to the database.
You can do this with jDriver for Oracle because Oracle uses the process
owner to determine who is attempting the connection. In the case of
WebLogic JDBC, this is always the user that started the WebLogic
Server.
To set up your Oracle instance to use this feature, your DBA needs to
follow these basic steps. The full procedure is described in more
detail in your Oracle documentation.
- Add the following line to the INIT[sid].ORA file:
OS_AUTHENT_PREFIX = OPS$
Note that the string "OPS$" is arbitrary and up to the DBA.
- Log in to the Oracle server as SYSTEM.
- Create a user named OPS$userid, where userid is some
operating system login ID. This user should be granted the standard
privileges (for example, CONNECT and RESOURCE).
Once the userid has been set up, you can connect with jDriver for Oracle
by specifying "/" as the username property and "" as the password
property. Here is an example for testing this connection with the dbping utility:
$ java utils.dbping ORACLE "/" "" myserver
Here is a code example for jDriver for Oracle:
Properties props = new Properties();
props.put("user", "/");
props.put("password", "");
props.put("server", "myserver");
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle", props);
Here is how you set up the registration of a connection pool (this one
named "eng") for use with this system:
weblogic.jdbc.connectionPool.eng=\
url=jdbc:weblogic:oracle,\
driver=weblogic.jdbc.oci.Driver,\
initialCapacity=4,\
maxCapacity=10,\
capacityIncrement=2,\
props=user="/";password="";server=myserver
weblogic.allow.reserve.weblogic.jdbc.connectionPool.eng=guest,joe,jill
The second property sets up an access control list for this connection
pool and gives the users "guest," "joe," and "jill" the same database
privileges as the user that started the WebLogic Server.
What type of Object is returned by ResultSet.getObject()?
I am confused about what type of object will be returned by the ResultSet.getObject() method. What
can I expect if my database column is a NUMBER(16,4), or just NUMBER?
jDriver for Oracle always returns a Java object that will preserve the precision
of the data retrieved. With Release 2.5 and later, jDriver for Oracle returns
the following from the getObject() method:
- For columns of types NUMBER(n) and NUMBER(m,n): we return a Double
if the defined precision of the column can be represented by a Double;
otherwise we return BigDecimal.
- For columns of type NUMBER: Because there is no explicit
precision, we determine the Java type to return based on the actual
value in each row, and this may vary from row to row. We will return
an Integer if the value has a zero-valued fractional component and the
value can be represented by an integer.
For example, 1.0000 will be an integer. We will return a long for a
value such as 123456789123.00000. If a value has a non-zero fractional
component we return a Double if the precision of the value can be
represented by a Double; otherwise we return a BigDecimal.
Can I use SQL*Net V1 with jDriver for Oracle?
Will SQL*Net V1 work with the 2.5 release of WebLogic's software,
specifically the JDBC drivers?
We recommend that you use SQL*Net V2. SQL*Net V1 is no longer included
with the Oracles releases after version 7.3.3. The WebLogic
jDriver for Oracle JDBC driver (version 2.5.4 and later) requires the
Oracle 7.3.4 client libraries.
If you do not know what version of SQL*Net you have, or what the
differences between V1 and V2 of SQL*Net are, contact your DBA or
Oracle technical support.
Personal Oracle and Windows95
We have been trying to establish connectivity between Java and
Personal Oracle 7 on Windows95, but we haven't been successful. Is
there a problem with compatibility?
You must be using the Oracle 7.3 client
libraries in order to use jDriver for Oracle
with Windows 95.
How do I limit the number of Oracle database connections?
I want to limit the number of Oracle database
connections generated by the WebLogic Server in response to client
requests. How would I do this?
If you want to limit the number of database connections used by the
WebLogic Server, you can use connection pools. Connection pools allow
T3Clients to share a fixed number of database connections. For more
information on how to set up connection pools, see the Developers
Guide, Using WebLogic
JDBC.
Why am I running out of allocated processes in Oracle?
I'm tuning my connections between WebLogic and an Oracle database and
I've used up all of my allocated Oracle processes. The database is
running on a Solaris platform; the client is running Oracle 7.2.x (or
earlier). Why am I exceeding the limit?
In Oracle versions prior to 7.3, Oracle has hardwired a connection
limit in its Oracle Call Interface (OCI) libraries, which WebLogic
uses to implement the jDriver for Oracle
drivers. The number of connections allowed by OCI from a given client
varies depending on the Oracle version and platform. For the Solaris
platform there is hardwired limit of 50 connections.
The solution is to upgrade to Oracle 7.3.3 or later. In these later
versions, Oracle has increased the hardwired limit on the number of
connections to much greater than 50. Note that on UNIX platforms you
must also upgrade your WebLogic software to version 2.5 or later,
because earlier versions of the WebLogic JDBC drivers use the
Oracle 7.2 OCI libraries.
I'm getting back integers
instead of doubles from an Oracle number column
When I use the getObject()
method to retrieve values stored in an Oracle DBMS number column, I
expect to get back doubles, but the values are being returned as
integers. I'm running WebLogic 2.5.1 and JDK 1.1.4 with Oracle 7.3.2.
WebLogic jDriver for Oracle
is conservative in converting data types when data is returned with
the getObject() method, to
guard against a loss of precision.
To get back a single data type when retrieving number values from an
Oracle number column, use the getDouble() method to return all of the values as
doubles. If you want more precision, you can use the getBigDecimal() method, but you will
have to state a scale for the values returned, which might involve
rounding some values. Using the getString() method is another option that insures
precision, but it will require more manipulation if you want to do
further calculations.
How can I call Oracle stored
procedures that take no parameters?
I'm working with the CallableStatement methods for the jDriver for Oracle
driver and having trouble calling an Oracle procedure that has no
parameters. How can I call a stored procedure with no parameters from
Java?
Here is what we use that works:
CallableStatement cstmt = conn.prepareCall("Begin procName; END;");
cstmt.execute();
where procName is the name of an Oracle stored procedure. This
is standard Oracle SQL syntax that works with any Oracle DBMS. You
might also use the following syntax:
CallableStatement cstmt = conn.prepareCall("{call procName};");
cstmt.execute();
This code, which conforms to the Java Extended SQL spec, will work
with any DBMS, not just Oracle.
How do I bind String values in a PreparedStatement?
I'm using jDriver for Oracle. I cannot get the PreparedStatement class to
bind Strings in a statement. The setString() method doesn't seem to
work. Here is how I set up the PreparedStatement:
String pstmt = "select n_name from n_table where n_name LIKE '?%'";
PreparedStatement ps = conn.prepareStatement(pstmt);
ps.setString(1, "SMIT");
ResultSet rs = ps.executeQuery();
You can't bind a value into another value in a PreparedStatement. The
complete value needs to be specified in a String (without using
embedded quotes) and then bound to an unquoted question-mark
(?). Here is the corrected code:
String matchvalue = "smit%";
String pstmt = "select n_name from n_table where n_name LIKE ?";
PreparedStatement ps = conn.prepareStatement(pstmt);
ps.setString(1, matchvalue);
ResultSet rs = ps.executeQuery();
Using 8-bit character sets with Oracle/Solaris
I'm using jDriver for Oracle on Solaris with an 8-bit character set, but
I'm not seeing the characters I expect. What's the problem?
If you are using an Oracle database with an 8-bit character set on
Solaris, make sure you set NLS_LANG to the proper value on the client. If NLS_LANG is unset, it defaults to a
7-bit ASCII character set, and tries to map characters greater than
ASCII 128 to a reasonable approximation (for example, á,
à, â would all map to a). Other characters are mapped to
a question mark (?).
Using Oracle JDBC drivers with a non-English codeset
I'm using the jDriver for Oracle
driver with an Oracle server that is based on a non-English
codeset. Most characters that I insert or select pass through the
driver fine, but certain characters are changed. Why is that?
Our drivers depend on the Oracle client software to do codeset
conversion. For certain codesets -- especially multibyte codesets --
the Oracle codeset conversion tables are incomplete or invalid. This
mainly pertains to multibyte codesets. If your Oracle client
installation is older than version 7.3.3, we suggest that you upgrade
to the latest version of the Oracle client software.
How do I know what codesets I
have available in Oracle?
When I am using the jDriver for Oracle driver with my Oracle database,
how can I find out what foreign language codesets I can access and
use?
To find out what codesets you currently have available in Oracle,
execute the following SQL query from SQLPlus at the command line:
SQL> SELECT value FROM v$nls_valid_values
WHERE parameter='CHARACTERSET';
The response will be a listing of all of the codesets currently
installed on your system. This listing will look something like the
following shortened list:
VALUE
-----------------------------------------------------------
US7ASCII
WE8DEC
WE8HP
US8PC437
WE8EBCDIC37
WE8EBCDIC500
WE8EBCDIC285
...
If you want to constrain the value in the query to a specific codeset
you are searching for, you might use a SQL query like the following:
SQL> SELECT value FROM v$nls_valid_values
WHERE parameter='CHARACTERSET' and VALUE='AL24UTFFSS';
This would produce the following response if the codeset is installed:
You can adjust the length of the value of the bound parameter by
passing an explicit length with the scale argument to the CallableStatement.registerOutputParameter() method.
You may also see this error message if you try to use
jDriver for Oracle's internationalization capabilities with a
language/codeset combination that is not installed on your system. If
you get the ORA-12705 error with the correct error text, then either
you have set NLS_LANG
improperly, or you do not have the right codesets installed on your
system.
