WebLogic 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
• Oracle Driver Classes
• Oracle URL
• Oracle Connection Properties
• Using tnsnames.ora Files
• Data Types
• SQL Escape Sequences
• Isolation Levels
• Using Scrollable Cursors
• Batch Inserts and Updates
• Parameter Metadata Support
• Auto-Generated Keys Support

 

---

Oracle Database Version Support

The BEA WebLogic Type 4 JDBC Oracle driver (the "Oracle driver") supports

• Oracle 9i R1 and R2
• Oracle 10g R1 and R2

See Supported Database Configurations in Supported Configurations for WebLogic Server 9.0.

 

---

Oracle Driver Classes

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

XA: weblogic.jdbcx.oracle.OracleDataSource

Non-XA: weblogic.jdbc.oracle.OracleDriver

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

Note: All connection property names are case-insensitive. For example, Password is the same as password.

 

 

---

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:

• Oracle server name and port
• Oracle System Identifier (SID) or Oracle service name
• Server process type (shared or dedicated)
• Connection failover instructions
• Client load balancing instructions

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.

 

Listing 6-1 tnsnames.ora Example

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.

Notes:

• The connection URL does not specify the server name and port of the database server; that information is specified in the tnsnames.ora file referenced by the TNSNamesFile property.
• If coding a path on Windows to the tnsnames.ora file in a Java string, the backslash character (\) must be preceded by the Java escape character, a backslash. For example: TNSNamesFile=c:\\oracle92\\NETWORK\\ADMIN\\tnsnames.ora.

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:

• Using a timestamp (using PreparedStatement.setTimestamp, for example), the value set is converted to the time zone specified by the TIME_ZONE session parameter.
• Using a string (using PreparedStatement.setString, for example), the string is passed as-is to the server. The supplied string must be in the format specified by the NLS_TIMESTAMP_TZ_FORMAT session parameter. If not, the Oracle server generates an error when it attempts to convert the string to the TIMESTAMP WITH LOCAL TIME ZONE type.

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:

• Using a timestamp (using PreparedStatement.setTimestamp, for example), the value set is converted to the time zone specified by the TIME_ZONE session parameter.
• Using a string (using PreparedStatement.setString, for example), the string is passed as-is to the server. The supplied string must be in the format specified by the NLS_TIMESTAMP_TZ_FORMAT session parameter. If not, the Oracle server generates an error when it attempts to convert the string to the TIMESTAMP WITH TIME ZONE type.

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;

Listing 6-2 REF Cursor Example 1: Calling a Stored Procedure That Returns a Single REF CURSOR

// 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();

}

Listing 6-3 REF Cursor Example 2: Calling a Stored Procedure that Returns Multiple REF CURSORs

// 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();

}

 

---

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.

Note: When the Oracle driver cannot support the requested result set type or concurrency, it automatically downgrades the cursor and generates one or more SQLWarnings with detailed information.

 

---

Batch Inserts and Updates

The Oracle driver provides two mechanisms for supporting batch operations:

• The first mechanism uses native Oracle batch functionality. This mechanism typically is the faster of the two mechanisms, but it is not compliant with the JDBC 3.0 specification because the native Oracle functionality returns a single update count for all operations in the batch. Because that single update count cannot be resolved into individual update counts for the driver, the driver returns a value of SUCCESS_NO_INFO (-2) for each entry in the update count array. The JDBC 3.0 specification requires individual update counts to be returned for each operation in the batch.
• The second mechanism uses code that resides in the driver to execute the batch operations and complies with the JDBC 3.0 specification, but it is slower than using native Oracle batch functionality.

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 for the following forms of SQL:

• INSERT INTO foo VALUES (?, ?, ?)
• INSERT INTO foo (col1, col2, col3) VALUES (?, ?, ?)
• UPDATE foo SET col1=?, col2=?, col3=? WHERE col1 operator ? [{AND | OR} col2 operator ?]

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

 

---

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:

• When using an Insert statement that contains no parameters, the Oracle driver supports the following form of the Statement.execute and Statement.executeUpdate methods to inform the driver to return the values of auto-generated keys:
• Statement.execute (String sql, int autoGeneratedKeys)
• Statement.executeUpdate (String sql, int autoGeneratedKeys)
• When using an Insert statement that contains parameters, the Oracle driver supports the following form of the Connection.prepareStatement method to inform the driver to return the values of auto-generated keys:
• Connection.prepareStatement (String sql, int autoGeneratedKeys)

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

Contact BEA |  Feedback |  Privacy  |