FAQs: WebLogic jDriver for Oracle

• Why does FOR UPDATE in Oracle 8 cause an ORA-01002 error?
• What causes an OCIW32.dll error?
• What transaction isolation levels does the WebLogic jDriver for Oracle support?
• How do I use Unicode codesets with the WebLogic jDriver for Oracle driver?
• How do I use OS Authentication with WebLogic jDriver for Oracle and Connection Pools?
• What type of object is returned by ResultSet.getObject()?
• How do I limit the number of Oracle database connections generated by WebLogic Server?
• How do I call Oracle stored procedures that take no parameters?
• How do I bind string values in a PreparedStatement?
• Why do I get unexpected characters from 8-bit character sets in WebLogic jDriver for Oracle?
• How do I learn what codesets are available in Oracle?
• How do I look up an "ORA" SQLException?
• What is error "ORA-6502?"
• Why do I get an error while trying to retrieve the text for ORA-12705?
• Why do I run out of resources during updates with Oracle's database link?
• How do I prevent errors when running t3dbping?
• Why do I get error "ORA-03120" when trying to access multi-byte characters from the CLOB/NCLOB column?
• Why does executing the PreparedStatement class cause a "TRUNC fails: ORA-00932: inconsistent datatypes" error?

Q. Why does FOR UPDATE in Oracle 8 cause an ORA-01002 error?

A. The Oracle 8 server generates an ORA-01002:fetch out of sequence error message when you use 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. Because this problem is due to a change in the Oracle 8 server, you should contact Oracle support for more information.

Q. What causes an OCIW32.dll error?

A. You may receive 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.

Q. What transaction isolation levels does the WebLogic jDriver for Oracle support?

A. Your servlet application may use Oracle Thin Drivers to access a database that includes BLOB fields. If you install and try to use WebLogic jDriver for Oracle and the same code fails and produces an exception similar to the following:

   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)

Setting the Isolation_level to 1 in the code that calls the RogueWave JDBCServer class works with the Oracle thin driver but fails with WebLogic jDriver for Oracle.

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

Q. How do I use Unicode codesets with the WebLogic jDriver for Oracle driver?

A. To use Unicode codesets:

1. 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.
2. Define the NLS_LANG variable in the environment where the JDBC driver is running. 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 Web site. For a list of Unicode language abbreviations, see the JavaSoft Web site.

Q. How do I use OS Authentication with WebLogic jDriver for Oracle and Connection Pools?

A. Using OS authentication in connection pools essentially means that you are using the UserId of the user who started WebLogic Server. OS authentication is available on NT and UNIX, but not on Solaris. 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 WebLogic 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.

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

2. Log in to the Oracle server as SYSTEM.
3. 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).
4. Once the userid is set up, you can connect with WebLogic 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 WebLogic 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 = myDriver.connect("jdbc:weblogic:oracle",
     props);

5. Use the Administration Console to set the attribute for your connection pool. The following code is an example of a JDBC connection pool configuration using the WebLogic jDriver for Oracle:

<JDBCConnectionPool 
    Name="myPool" 
    Targets="myserver,server1" 
    DriverName="weblogic.jdbc.oci.Driver" 
    InitialCapacity="1" 

     MaxCapacity="10"
     CapacityIncrement="2" 
     Properties="databaseName=myOracleDB" 

Q. What type of object is returned by ResultSet.getObject()?

A. WebLogic jDriver for Oracle always returns a Java object that preserves the precision of the data retrieved. WebLogic jDriver for Oracle returns the following from the getObject() method:

• For columns of types NUMBER(n) and NUMBER(m,n): a Double is returned if the defined precision of the column can be represented by a Double; otherwise BigDecimal is returned.
• For columns of type NUMBER: Because there is no explicit precision, the Java type to return is determined based on the actual value in each row, and this may vary from row to row. An Integer is returned 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. A long is returned for a value such as 123456789123.00000. If a value has a non-zero fractional component, a Double is returned if the precision of the value can be represented by a Double; otherwise a BigDecimal is returned.

Q. How do I limit the number of Oracle database connections generated by WebLogic Server?

A. You can use connection pools to limit the number of Oracle database connections generated by WebLogic Server in response to client requests. Connection pools allow T3 applications to share a fixed number of database connections. For information on how to set up connection pools, see Programming WebLogic JDBC.

Q. How do I call Oracle stored procedures that take no parameters?

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

Q. How do I bind string values in a PreparedStatement?

A. Suppose you are trying to get the PreparedStatement class to bind Strings in a statement. The setString() method doesn't seem to work. Here is how you have 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();

The preceding code does not work because 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();

Q. Why do I get unexpected characters from 8-bit character sets in WebLogic jDriver for Oracle?

A. 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 (?).

Q. How do I learn what codesets are available in Oracle?

A. 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 lists of all 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:

  VALUE
  -------------------------------------------------------------
  AL24UTFFSS

You can use Oracle's installation tools to install additional codesets. Contact Oracle for more information.

Q. How do I look up an "ORA" SQLException?

A. If your WebLogic jDriver for Oracle application produces an SQLException such as:

  java.sql.SQLException: ORA-12536: TNS: operation would block

You can look up an Oracle error by using the oerr command. For example, the description of error ORA-12536 can be found with the command:

 > oerr ora 12536

Q. What is error "ORA-6502?"

A. The default length of a string bound to an OUTPUT parameter of a CallableStatement is 128 characters. If the value you assign to the bound parameter exceeds that length, you will get this error.

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.

Q. Why do I get an error while trying to retrieve the text for ORA-12705?

A. This error occurs when you have not set the ORACLE_HOME environment variable properly — a common mistake. In order to use WebLogic jDriver for Oracle, the Oracle client software needs to be installed and ORACLE_HOME must be set.

You may also see this error message if you try to use WebLogic 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.

Q. Why do I run out of resources during updates with Oracle's database link?

A. When you use Oracle's database link to update your database, you may get error "maximum number of temporary table locks exceeded" even if you close your result sets and statements when you finish.

The database link is an object in the local database that allows you to access tables, views, and such in a remote database. The database link is controlled by the Oracle server, so the driver has no control over its use of resources. The link appears to perform the commit (since other processes could see the records that were being created), but it doesn't free any resources until the connection is closed. The solution is to remove the database link and use the JDBC driver to do your selects, inserts, and updates.

Q. How do I prevent errors when running t3dbping?

A. When you are testing your Oracle database connections under UNIX, you can run SQL*PLUS and can successfully ping the database using utils.dbping. However, when you use the multitier utils.t3dbping utility, you receive an ORA-12154 error message.

First, make sure that your ORACLE_HOME environment variable is correctly set to point to your Oracle installation. This variable must be set in the environment where the WebLogic server is running.

In the C-shell issue the following command:

  $ setenv ORACLE_HOME path

where path is the path to your Oracle installation.

In the Bourne shell, issue the following commands:

  $ ORACLE_HOME=path
  $ export ORACLE_HOME

where path is the path to your Oracle installation. When you ping your database using the two-tier utils.dbping utility, the JDBC driver loads the database client library and establishes the connection to the database. When you use the multitier utils.t3dbping utility, the WebLogic Server loads a two-tier driver and uses it to establish a database connection. In both cases, the same method is used to connect to the database. SQL*PLUS works because it doesn't require ORACLE_HOME to find the client libraries.

If you are still experiencing problems, try this:

1. Open a command shell.
2. Run the two-tier version of utils.dbping in this shell.
3. Start WebLogic in this shell from the command line:

   $ java -ms32m -mx32m weblogic.server

4. Open a second command shell.
5. Run the utils.t3dbping in the second shell against the server running in the first command shell.

If this procedure doesn't work, please send the output from these commands to WebLogic technical support.

Q. Why do I get error "ORA-03120" when trying to access multi-byte characters from the CLOB/NCLOB column?

A. Accessing exceeded characters would result in ORA-03120 error when getting the length of the clob from the CLOB/NCLOB column returns a bigger value than the actual length from the OCI layer. Using Oracle 8.1.6.3 solves this problem.

Q. Why does executing the PreparedStatement class cause a "TRUNC fails: ORA-00932: inconsistent datatypes" error?

A. According to Oracle Metalink Bug Database Doc ID: 144784.1, in the absence of explicit data typecasting, OCI assumes that a bind variable is a CHAR data type. If the SQL statement intends to use the bind variable as a DATE data type, but OCI thought it was a CHAR, the SQL parser will have a conflict in data types. The fix is to explicitly use data conversion functions to convert the bind variables in the problem queries. For example, a select string of

String st = "select count(*) from simple_table where TRUNC(mydate) = TRUNC(?)"; 

should be changed to:

String st = "select count(*) from simple_table where TRUNC(mydate) = TRUNC(TO_DATE(?))";