Type 4 JDBC Drivers

The Oracle Driver

The following sections describe how to configure and use the BEA WebLogic Type 4 JDBC Oracle driver:

Oracle Database Version Support

The DataDirect Connect for JDBC Oracle driver (the "Oracle driver") supports:

Oracle Driver Classes

The driver classes for the BEA WebLogic Type 4 JDBC Oracle driver are:

Use these driver classes when configuring a JDBC data source in your WebLogic Server domain.

Oracle URL

To connect to an Oracle database, use the following URL format:

   jdbc:bea:oracle://dbserver:port

Oracle Connection Properties

Table 6-1 lists the JDBC connection properties supported by the Oracle driver, and describes each property. You can use these connection properties in a JDBC data source configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC data source configuration:

   property=value

All connection property names are case-insensitive. For example, Password is the same as password. Required properties are noted as such.

Performance Considerations

Setting the following connection properties for the Oracle driver as described in the following list can improve performance for your applications:

BatchPerformanceWorkaround

The driver can use a JDBC 3.0-compliant batch mechanism or the native Oracle batch mechanism to execute batch operations. If your application does not use update count information, performance can be improved by using the native Oracle batch environment. The JDBC 3.0-compliant mechanism returns individual update counts for each statement or parameter set in the batch as required by the JDBC 3.0 specification. The native Oracle batch mechanism does not return individual update counts for each statement or parameter set in the batch. For this reason, when the native Oracle batch mechanism is used, the driver returns a value of SUCCESS_NO_INFO (-2) in the returned update count array.

CatalogOptions

Retrieving synonym and remarks information is very expensive with Oracle. If your application does not need to return this information, the driver can improve performance. Standard JDBC behavior is to include synonyms in the result set of calls to the following DatabaseMetaData methods: getColumns(), getProcedures(), getProcedureColumns(), and getIndexInfo(). In addition, the driver can include Remarks information in the result sets of calls to the following DatabaseMetaData methods: getTables() and getColumns().

InsensitiveResultSetBufferSize

To improve performance when using scroll-insensitive result sets, the driver can cache the result set data in memory instead of writing it to disk. By default, the driver caches 2 MB of insensitive result set data in memory and writes any remaining result set data to disk. Performance can be improved by increasing the amount of memory used by the driver before writing data to disk or by forcing the driver to never write insensitive result set data to disk. The maximum cache size setting is 2 GB.

MaxPooledStatements

To improve performance, the driver's own internal prepared statement pooling should be enabled when the driver does not run from within an application server or from within another application that does not provide its own prepared statement pooling. When the driver's internal prepared statement pooling is enabled, the driver caches a certain number of prepared statements created by an application. For example, if the MaxPooledStatements property is set to 20, the driver caches the last 20 prepared statements created by the application. If the value set for this property is greater than the number of prepared statements used by the application, all prepared statements are cached.

ResultSetMetaDataOptions

By default, the Oracle driver skips the additional processing required to return the correct table name for each column in the result set when the ResultSetMetaData.getTableName() method is called. Because of this, the getTableName() method may return an empty string for each column in the result set. If you know that your application does not require table name information, this setting provides the best performance.

See ResultSet MetaData Support for more information about returning ResultSet metadata.

ServerType

When using a dedicated server connection, a server process on UNIX (a thread on Windows) is created to serve only your application connection. When you disconnect, the process goes away. The socket connection is made directly between your application and this dedicated server process. This can provide considerable performance improvements, but will use significantly more resources on UNIX servers. Because this is a thread on Oracle servers running on Windows platforms, the additional resource usage on the server is significantly less. The ServerType property should be set to dedicated when you have a batch environment with lower numbers of connections, your Oracle server has excess processing capacity and memory available when at maximum load, or if you have a performance-sensitive application that would be degraded by sharing Oracle resources with other applications.

WireProtocolMode

This driver can improve performance if you typically return data that is repeated in consecutive rows, for example, the data in column1/row1 is the same as the data in column1/row2, and so on. See WireProtocolMode.

Using tnsnames.ora Files

The tnsnames.ora file is used to map connection information for each Oracle service to a logical alias. The Oracle driver allows you to retrieve basic connection information from a tnsnames.ora file, including:

In a tnsnames.ora file, connection information for an Oracle service is associated with an alias, or Oracle net service name. Each net service name entry contains connect descriptors that define listener and service information. The following example in Listing 6-1 shows connection information in a tnsnames.ora file configured for the net service name entries, FITZGERALD.SALES and ARMSTRONG.ACCT.

FITZGERALD.SALES =

  (DESCRIPTION = 

    (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

       (CONNECT_DATA =

          (SID = ORCL)

       )

  )

ARMSTRONG.ACCT =

  (DESCRIPTION = 

  (ADDRESS_LIST= 

     (FAILOVER = on)

     (LOAD_BALANCE = on) 

     (ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

     (ADDRESS= (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))

     (ADDRESS= (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))

)   

  (CONNECT_DATA=

     (SERVICE_NAME = acct.us.yourcompany.com)

    )

)

Using this example, if the Oracle driver referenced the Oracle net service name entry FITGERALD.SALES, the driver would connect to the Oracle database instance identified by the Oracle SID ORCL (SID=ORCL). Similarly, if the Oracle driver referenced ARMSTRONG.ACCT, the driver would connect to the Oracle database identified by the service name acct.us.yourcompany.com (SERVICE_NAME=acct.us.yourcompany.com). In addition, the driver would enable connection failover (FAILOVER=on) and client load balancing (LOAD_BALANCE=on).

Typically, a tnsnames.ora file is installed when you install an Oracle database. By default, the tnsnames.ora file is located in the ORACLE_HOME\network\admin directory on Windows and the $ORACLE_HOME/network/admin directory on UNIX.

Connecting to the Database

To retrieve connection information from an Oracle tnsnames.ora file with the Oracle driver, you must inform the driver which tnsnames.ora file (using the TNSNamesFile property) and Oracle service name entry (using the TNSServerName property) to use so that the driver can reference the correct connection information. For example:

   <JDBCConnectionPool 
   DriverName="weblogic.jdbc.oracle.OracleDriver" 
   Name="myDriver" 
   PasswordEncrypted="{3DES}r8a+P5qIVJzgiWQDTAN/OA==" 
   Properties="TNSServerName=myTNSServerName;user=user;TNSNamesFile=/usr/local/network/admin/tnsnames.ora"

   Targets="myserver" 
   TestConnectionsOnReserve="true" 
   TestTableName="SQL SELECT 1 FROM DUAL" 
   URL="jdbc:bea:oracle:TNSNamesFile=/usr/local/network/admin/tnsnames.ora" 
   XAPasswordEncrypted="" /> 

The URL specifies the path and filename of the tnsnames.ora file (jdbc:bea:oracle:TNSNamesFile=/usr/local/network/admin/tnsnames.ora) and the Properties specifies the server name (TNSServerName=myTNSServerName) to use for the connection.

If using tnsnames.ora files with a Security Manager on a Java 2 Platform, read permission must be granted to the tnsnames.ora file. See Granting Access to Oracle tnsnames.ora Files for an example.

Configuring the tnsnames.ora File

If using a tnsnames.ora file to retrieve connection information, do not specify the following connection properties to prevent connection information conflicts:

If any of these properties are specified in addition to the TNSNamesFile and TNSServerName properties, the driver generates an exception.

Table 6-2 lists the Oracle driver properties that correspond to tnsnames.ora connect descriptor parameters. If using a tnsnames.ora file, do not specify any of the driver properties listed to prevent connection information conflicts.

For more information about configuring tnsnames.ora files, refer to your Oracle documentation.

Data Types

Table 6-3 lists the data types supported by the Oracle driver and describes how they are mapped to the JDBC data types.

See GetTypeInfo for more information about data types.

Oracle Date/Time Data Types

Oracle9i and higher supports the following date/time data types: TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, and TIMESTAMP WITH TIME ZONE. To understand how the Oracle driver supports these data types, you first must understand the values the Oracle driver assigns to the Oracle date/time session parameters.

Date/Time Session Parameters

At connection time, the Oracle driver sets the following date/time session parameters:

TIMESTAMP Data Type

The Oracle TIMESTAMP data type is mapped to the JDBC TIMESTAMP data type.

TIMESTAMP WITH LOCAL TIME ZONE Data Type

The Oracle TIMESTAMP WITH LOCAL TIME ZONE data type is mapped to the TIMESTAMP JDBC data type.

When retrieving TIMESTAMP WITH LOCAL TIME ZONE columns, the value returned to the user is converted to the time zone specified by the TIME_ZONE session parameter.

When setting TIMESTAMP WITH LOCAL TIME ZONE columns:

TIMESTAMP WITH TIME ZONE Data Type

By default, the Oracle TIMESTAMP WITH TIME ZONE data type is mapped to the VARCHAR JDBC data type.

When retrieving TIMESTAMP WITH TIME ZONE values as a string (using resultSet.getString, for example), the value is returned as the string representation of the timestamp including time zone information. The string representation is formatted in the format specified by the Oracle NLS_TIMESTAMP_TZ_FORMAT session parameter.

By default, retrieving TIMESTAMP WITH TIME ZONE values as a timestamp (using resultSet.getTimeStamp, for example) is not supported because the time zone information stored in the database would be lost when the data is converted to a timestamp. To provide backward compatibility with existing applications, you can use the FetchTSWTZasTimestamp property to allow TIMESTAMP WITH TIME ZONE values to be retrieved as a timestamp. The default value of the FetchTSWTSasTimestamp property is false, which disables retrieving TIMESTAMP WITH TIME ZONE values as timestamps.

When setting TIMESTAMP WITH TIME ZONE columns:

XMLType Data Type

The Oracle driver supports tables containing columns specified as XMLType for Oracle9i and higher. The driver maps the Oracle XMLType data type to the JDBC CLOB data type. XMLType columns can be used in queries just like any other column type. The data from XMLType columns can be retrieved as a String, Clob, CharacterStream, or AsciiStream. When inserting or updating XMLType columns, the data to be inserted or updated must be in the form of an XMLType data type.

Oracle provides the xmltype() function to construct an XMLType data object. The xmlData argument of the xmltype() function can be specified as a string literal or a parameter marker. If a parameter marker is used, the parameter value may be set using the setString, setClob, setCharacterStream, or setAsciiStream methods.

The following code inserts data into an XMLType column using a statement with a string literal as the xmlData argument of the xmltype() function:

   //  Insert xml data as a literal

   String sql = "insert into XMLTypeTbl values (1, xmltype('" +
      "<emp><empNo>123</empNo><empName>Mark</empName></emp>'))";

   Statement stmt = con.createStatement();

   stmt.executeUpdate(sql);

The following code inserts data into an XMLType column using a prepared statement:

   //  Insert xml data as a String parameter

   String xmlStr =

      "<emp><empNo>234</empNo><empName>Trish</empName></emp>";

   String sql = "insert into XMLTypeTbl values (?, xmltype(?))";

   PreparedStatement prepStmt = con.prepareStatement(sql);

   prepStmt.setInt(1, 2);

   prepStmt.setString(2, xmlStr);

   prepStmt.executeUpdate();

When the data from an XMLType column is retrieved as a Clob, the XMLType data cannot be updated using the Clob object. Calling the setString, setCharacterStream, or setAsciiStream methods of a Clob object returned from an XMLType column generates a SQLException.

REF CURSOR Data Type Support

REF CURSOR is the Oracle data type for a cursor variable. Because JDBC does not support a cursor variable data type, the Oracle driver returns REF CURSOR output parameters and return values to the application as result sets. The Oracle driver automatically converts the REF CURSOR data to a result set, which can be retrieved using getResultSet or getMoreResults. Because REF CURSOR data is returned as result sets and not as output parameters, REF CURSOR output parameters are not included in results from DatabaseMetaData.getProcedureColumns calls.

In your application, omit any parameter markers for the REF CURSOR and do not declare an output parameter for the REF CURSOR as shown in the following examples. These examples reference the following stored procedure definition:

   CREATE PACKAGE foo_pkg AS

      TYPE EmpCurTyp IS REF CURSOR RETURN fooTbl%ROWTYPE;" 

      PROCEDURE selectEmployeeManager(empId IN INT, empCursor OUT
         EmpCurTyp,
         mgrCursor out EmpCurTyp);

      FUNCTION selectEmployee2 (empId IN INT) return EmpCurTyp;

   END foo_pkg;

// Call a function that accepts an input parameter 

// and returns a REF CURSOR as the return value. Omit the 

// placeholder for the refcursor return value parameter.

// The REF CURSOR is returned as a result set.

sql = "{call foo_pkg.selectEmployee2(?)}";

callStmt = con.prepareCall(sql);

callStmt.setInt(1, 2); 

moreResults = callStmt.execute();

while (true) {

   if (moreResults) {

      //  Get the result set that represents the REF CURSOR

      resultSet = callStmt.getResultSet();

      displayResults(resultSet);

      resultSet.close();

      resultSet = null;

      System.out.println(); 

   }

   else {

         updateCnt = callStmt.getUpdateCount();

         if (updateCnt == -1) {

            break;

         }

         System.out.println("Update Count: " + updateCnt);

     }

     moreResults = callStmt.getMoreResults();

}

// Call the stored procedure that accepts an input parameter 

// and returns two REF CURSORs. Omit the placeholder for 

// REF CURSOR parameters. The REF CURSORs are returned as 

// result sets.

sql = "{call foo_pkg.selectEmployeeManager(?)}";

callStmt = con.prepareCall(sql);

callStmt.setInt(1, 2); 

moreResults = callStmt.execute();

while (true) {

   if (moreResults) {

      //  Get the result set that represents the REF CURSOR

      resultSet = callStmt.getResultSet();

      displayResults(resultSet);

      resultSet.close();

   }

   else {

         updateCnt = callStmt.getUpdateCount();

         if (updateCnt == -1) {

            break;

         }

      }

      moreResults = callStmt.getMoreResults();

}

Character Set Conversion

To control which code page the driver uses to communicate with the Oracle server, use the CodePageOverride property. The code page specified by this property overrides the code page used by the driver to convert character data to the database character set. This option has no effect on how the driver converts character data to the national character set.

See Table 6-4 for a details about the different options available for character set conversions.

SQL Escape Sequences

See SQL Escape Sequences for JDBC, for information about the SQL escape sequences supported by the Oracle driver.

Isolation Levels

The Oracle driver supports the Read Committed and Serializable isolation levels. The default is Read Committed.

Using Scrollable Cursors

The Oracle driver supports scroll-sensitive result sets, scroll-insensitive result sets, and updatable result sets.

Batch Inserts and Updates

The Oracle driver provides two mechanisms for supporting batch operations:

The BatchPerformanceWorkaround property determines which batch mechanism is used. If the value of the BatchPerformanceWorkaround property is true, the native Oracle batch mechanism is used; otherwise, the JDBC 3.0-compliant mechanism is used. The default value of the BatchPerformanceWorkaround property is false.

Parameter Metadata Support

The Oracle driver supports returning parameter metadata as described in this section.

Insert and Update Statements

The Oracle driver supports returning parameter metadata for the following forms of Insert and Update statements:

where operator is any of the following SQL operators: =, <, >, <=, >=, and <>.

Select Statements

The Oracle driver supports returning parameter metadata for Select statements that contain parameters in ANSI SQL 92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTS predicate constructs. Refer to the ANSI SQL reference for detailed syntax.

Parameter metadata can be returned for a Select statement if one of the following conditions is true:

The following Select statements show further examples for which parameter metadata can be returned:

   SELECT col1, col2 FROM foo WHERE col1 = ? and col2 > ?

   SELECT ... WHERE colname = (SELECT col2 FROM t2 WHERE col3 = ?)

   SELECT ... WHERE colname LIKE ?

   SELECT ... WHERE colname BETWEEN ? and ?

   SELECT ... WHERE colname IN (?, ?, ?)

   SELECT ... WHERE EXISTS(SELECT ... FROM T2 WHERE col1 < ?)

ANSI SQL 92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BY statements are supported. For example:

   SELECT * FROM t1 WHERE col = ? ORDER BY 1

Joins are supported. For example:

   SELECT * FROM t1,t2 WHERE t1.col1 = ?

Fully qualified names and aliases are supported. For example:

   SELECT a, b, c, d FROM T1 AS A, T2 AS B WHERE A.a = ? and B.b = ?"

ResultSet MetaData Support

If your application requires table name information, the Oracle driver can return table name information in ResultSet metadata for Select statements. By setting the ResultSetMetaDataOptions property to 1, the Oracle driver performs additional processing to determine the correct table name for each column in the result set when the ResultSetMetaData.getTableName() method is called. Otherwise, the getTableName() method may return an empty string for each column in the result set.

When the ResultSetMetaDataOptions property is set to 1 and the ResultSetMetaData.getTableName() method is called, the table name information that is returned by the Oracle driver depends on whether the column in a result set maps to a column in a table in the database. For each column in a result set that maps to a column in a table in the database, the Oracle driver returns the table name associated with that column. For columns in a result set that do not map to a column in a table (for example, aggregates and literals), the Oracle driver returns an empty string.

The Select statements for which ResultSet metadata is returned may contain aliases, joins, and fully qualified names. The following queries are examples of Select statements for which the ResultSetMetaData.getTableName() method returns the correct table name for columns in the Select list:

   SELECT id, name FROM Employee

   SELECT E.id, E.name FROM Employee E 

   SELECT E.id, E.name AS EmployeeName FROM Employee E

   SELECT E.id, E.name, I.location, I.phone FROM Employee E, EmployeeInfo I
      WHERE E.id = I.id

   SELECT id, name, location, phone FROM Employee, EmployeeInfo WHERE id =
      empId

   SELECT Employee.id, Employee.name, EmployeeInfo.location, 
      EmployeeInfo.phone FROM Employee, EmployeeInfo WHERE Employee.id =
      EmployeeInfo.id

The table name returned by the driver for generated columns is an empty string. The following query is an example of a Select statement that returns a result set that contains a generated column (the column named "upper").

   SELECT E.id, E.name as EmployeeName, {fn UCASE(E.name)} 

      AS upper FROM Employee E

The Oracle driver also can return schema name and catalog name information when the ResultSetMetaData.getSchemaName() and ResultSetMetaData.getCatalogName() methods are called if the driver can determine that information. For example, for the following statement, the Oracle driver returns "test" for the catalog name, "test1" for the schema name, and "foo" for the table name:

   SELECT * FROM test.test1.foo 

The additional processing required to return table name, schema name, and catalog name information is only performed if the ResultSetMetaData.getTableName(), ResultSetMetaData.getSchemaName(), or ResultSetMetaData.getCatalogName() methods are called.

Rowset Support

The Oracle driver supports any JSR 114 implementation of the RowSet interface, including:

See http://www.jcp.org/en/jsr/detail?id=114 for more information about JSR 114.

Auto-Generated Keys Support

The Oracle driver supports retrieving the values of auto-generated keys. An auto-generated key returned by the Oracle driver is the value of a ROWID pseudo column.

How you return these values depends on whether you are using an Insert statement that contains parameters:

The application fetches the values of generated keys from the driver using the Statement.getGeneratedKeys method.