The Oracle Driver

The Oracle Driver

The BEA WebLogic Type 4 JDBC Oracle driver is available in the WebLogic Server 8.1 Service Pack 2 and later releases. It is not available with the WebLogic Server 8.1 GA and Service Pack 1 releases.
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
Sample Connection Pool Configuration
Data Types
Oracle9i Data Type Support
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Batch Inserts and Updates
Auto-Generated Keys Support

Oracle Database Version Support

The BEA WebLogic Type 4 JDBC Oracle driver (the "Oracle driver") supports Oracle 8i R3 (8.1.7) and Oracle 9i running on Windows and UNIX.

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 connection pool in your WebLogic Server domain.

Oracle URL

To connect to an Oracle database, use the following URL format:
jdbc:bea:oracle://dbserver:port
The Oracle driver also supports the AlternateServers option for connection failover. See Connection Failover with AlternateServers.

Oracle Connection Properties

Table 5-1 lists the JDBC connection properties supported by the Oracle driver, and describes each property. You can use these connection properties in a JDBC connection pool configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC connection pool configuration:
property=value
Note: All connection property names are case-insensitive. For example, Password is the same as password.

Property

Description
BatchPerformanceWorkaroundOPTIONAL {true | false}. Determines the method used to execute batch operations. When set to true, the native Oracle batch mechanism is used. When set to false, the JDBC 3.0-compliant batch mechanism is used. See Batch Inserts and Updates for details. The default is false.
CatalogIncludesSynonymsOPTIONAL {true | false}. When set to true, includes synonyms in the result sets returned from the DatabaseMetaData methods getColumns, getProcedureColumns, and getIndexInfo. The default is false.
FetchTSWTSasTimestampOPTIONAL {true | false}. When set to true, allows column values with the Oracle9i TIMESTAMP WITH TIME ZONE data type to be retrieved as a JDBC TIMESTAMP data type. When set to false, column values with the Oracle9i TIMESTAMP WITH TIME ZONE data type must be retrieved as a string. See TIMESTAMP WITH TIME ZONE Data Type for details.The default is false.
LoginTimeout OPTIONAL The maximum time in seconds that attempts to create a database connection will wait. A value of 0 specifies that the timeout is the default system timeout if there is one; otherwise it specifies that there is no timeout.
MaxPooledStatementsOPTIONAL The maximum number of pooled PreparedStatements for this connection.The default is 0.
Password A case-insensitive password used to connect to your Oracle database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.
PortNumberOPTIONAL The TCP port of the Oracle listener running on the Oracle database server. The default is 1521, which is the Oracle default PortNumber when installing the Oracle database software.
ServerName Either the IP address or the DNS name (if your network supports named servers) of the server running the Oracle database software. For example, OracleAppServer or 122.23.15.12.
ServiceNameOPTIONAL Database service name to specify the database to connect to. The service name is typically the global database name, which is a combination of the database name (DB_NAME) and domain (DB_DOMAIN). An example service is hr.us.bea.com, where hr is the database name and us.bea.com is the domain. The database service is defined during database creation. If you are not sure what the service is, ask your Database Administrator.
Note: You cannot specify both a ServiceName and an SID in the connection properties. The driver generates a SQLException if both a ServiceName and an SID are specified.
SIDOPTIONAL The Oracle System Identifier that refers to the instance of the Oracle database software running on the server. The default is 'ORCL', which is the Oracle default SID when installing the Oracle database software.
Note: You cannot specify both a ServiceName and an SID in the connection properties. The driver generates a SQLException if both a ServiceName and an SID are specified.
User The case-insensitive default user name used to connect to your Oracle database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name. Operating System authentication is not currently supported in the Oracle driver.

Sample Connection Pool Configuration

Table 5-2 lists configuration attributes for a sample WebLogic Server connection pool that uses the non-XA version of the WebLogic Type 4 Oracle JDBC driver. Table 5-3 lists configuration attributes for a sample WebLogic Server connection pool that uses the XA version of the driver.

Attribute

Value
URL jdbc:bea:oracle://host:port
Driver Class Name weblogic.jdbc.oracle.OracleDriver
Properties user=username
PortNumber=port
ServerName=host
SID=Oracle_SID
Password password
Target server or cluster name

Attribute

Value
URL jdbc:bea:oracle://host:port
Driver Class Name weblogic.jdbcx.oracle.OracleDataSource
Properties uuser=username
PortNumber=port
ServerName=host
ServiceName=db_name.db_domain
SupportsLocalTransaction true (required only for local transactions)
Password password
Target server or cluster name

Data Types

Table 5-4 lists the data types supported by the Oracle driver and how they are mapped to the JDBC data types.

Oracle Data Type

JDBC Data Type
bfile BLOB
blob BLOB
char CHAR
clob CLOB
date TIMESTAMP
long LONGVARCHAR
long raw LONGVARBINARY
nchar CHAR
nclob CLOB
number DECIMAL
number FLOAT
nvarchar2 VARCHAR
raw VARBINARY
varchar2 VARCHAR

Table 5-5 lists additional data types supported in Oracle9i only.

Oracle Data Type

JDBC Data Type
TIMESTAMP TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP
TIMESTAMP WITH TIME ZONE VARCHAR
XMLType CLOB

Oracle9i Data Type Support

Table 5-5 lists some new data types supported in Oracle9i only and lists how they are mapped to JDBC data types by the Oracle driver. This section provides details about how the Oracle driver supports these new data types.

Oracle9i Date/Time Data Types

Oracle9i provides 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 Oracle9i data types, you first must understand the values the Oracle driver assigns to the Oracle9i date/time session parameters.

Oracle9i Date/Time Session Parameters

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

Session Parameter

Description
TIME_ZONE The Oracle session time zone. Set to the current time zone as reported by the Java Virtual Machine.
NLS_TIMESTAMP_FORMAT The default timestamp format. The Oracle driver uses the JDBC timestamp escape format:YYYY-MM_DD HH24:MI:SS.FF.
NLS_TIMESTAMP_TZ_FORMAT The default timestamp with time zone format. The Oracle driver uses the JDBC timestamp escape format with the time zone field appended:YYYY-MM_DD HH24:MI:SS.FF TZH:TZM.

TIMESTAMP Data Type

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

TIMESTAMP WITH LOCAL TIME ZONE Data Type

The Oracle9i TIMESTAMP WITH LOCAL TIME ZONE data type is mapped to the java.sql.Types.TIMESTAMP java 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 Oracle9i TIMESTAMP WITH TIME ZONE data type is mapped to the java.sql.Types.VARCHAR java 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 Oracle9i 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 connection property to allow TIMESTAMP WITH TIME ZONE values to be retrieved as a timestamp. The default value of the FetchTSWTSasTimestamp connection property is false, which disables retrieving TIMESTAMP WITH TIME ZONE values as timestamps. For more information about specifying connection properties, see Oracle Connection Properties.
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. The driver maps the Oracle9i XMLType data type to the Java 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.
Oracle9i 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.

SQL Escape Sequences

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

Isolation Levels

Oracle supports isolation levels Read Committed and Serializable. 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.

JTA Support

To use JDBC distributed transactions through JTA, have Oracle version 8.1.7 or higher, and install the Oracle JAVA_XA package on the server.

Batch Inserts and Updates

The BEA WebLogic Type 4 JDBC Oracle driver provides two mechanisms for supporting batch operations. One mechanism uses the 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 the operations in the batch. 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. This mechanism complies with the JDBC 3.0 specification, but it is slower than using the native Oracle batch functionality.
The BatchPerformanceWorkaround connection property determines which batch mechanism is used. If the value of the BatchPerformanceWorkaround connection 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.

Auto-Generated Keys Support

The Oracle driver supports retrieving the values of auto-generated keys. How you return those 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 a 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.

Property	Description
BatchPerformanceWorkaroundOPTIONAL	{true \| false}. Determines the method used to execute batch operations. When set to true, the native Oracle batch mechanism is used. When set to false, the JDBC 3.0-compliant batch mechanism is used. See Batch Inserts and Updates for details. The default is false.
CatalogIncludesSynonymsOPTIONAL	{true \| false}. When set to true, includes synonyms in the result sets returned from the DatabaseMetaData methods getColumns, getProcedureColumns, and getIndexInfo. The default is false.
FetchTSWTSasTimestampOPTIONAL	{true \| false}. When set to true, allows column values with the Oracle9i TIMESTAMP WITH TIME ZONE data type to be retrieved as a JDBC TIMESTAMP data type. When set to false, column values with the Oracle9i TIMESTAMP WITH TIME ZONE data type must be retrieved as a string. See TIMESTAMP WITH TIME ZONE Data Type for details.The default is false.
LoginTimeout OPTIONAL	The maximum time in seconds that attempts to create a database connection will wait. A value of 0 specifies that the timeout is the default system timeout if there is one; otherwise it specifies that there is no timeout.
MaxPooledStatementsOPTIONAL	The maximum number of pooled PreparedStatements for this connection.The default is 0.
Password	A case-insensitive password used to connect to your Oracle database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.
PortNumberOPTIONAL	The TCP port of the Oracle listener running on the Oracle database server. The default is 1521, which is the Oracle default PortNumber when installing the Oracle database software.
ServerName	Either the IP address or the DNS name (if your network supports named servers) of the server running the Oracle database software. For example, OracleAppServer or 122.23.15.12.
ServiceNameOPTIONAL	Database service name to specify the database to connect to. The service name is typically the global database name, which is a combination of the database name (DB_NAME) and domain (DB_DOMAIN). An example service is hr.us.bea.com, where hr is the database name and us.bea.com is the domain. The database service is defined during database creation. If you are not sure what the service is, ask your Database Administrator. Note: You cannot specify both a ServiceName and an SID in the connection properties. The driver generates a SQLException if both a ServiceName and an SID are specified.
SIDOPTIONAL	The Oracle System Identifier that refers to the instance of the Oracle database software running on the server. The default is 'ORCL', which is the Oracle default SID when installing the Oracle database software. Note: You cannot specify both a ServiceName and an SID in the connection properties. The driver generates a SQLException if both a ServiceName and an SID are specified.
User	The case-insensitive default user name used to connect to your Oracle database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name. Operating System authentication is not currently supported in the Oracle driver.

Attribute	Value
URL	jdbc:bea:oracle://host:port
Driver Class Name	weblogic.jdbc.oracle.OracleDriver
Properties	user=username PortNumber=port ServerName=host SID=Oracle_SID
Password	password
Target	server or cluster name

Attribute	Value
URL	jdbc:bea:oracle://host:port
Driver Class Name	weblogic.jdbcx.oracle.OracleDataSource
Properties	uuser=username PortNumber=port ServerName=host ServiceName=db_name.db_domain
SupportsLocalTransaction	true (required only for local transactions)
Password	password
Target	server or cluster name

Oracle Data Type	JDBC Data Type
bfile	BLOB
blob	BLOB
char	CHAR
clob	CLOB
date	TIMESTAMP
long	LONGVARCHAR
long raw	LONGVARBINARY
nchar	CHAR
nclob	CLOB
number	DECIMAL
number	FLOAT
nvarchar2	VARCHAR
raw	VARBINARY
varchar2	VARCHAR

Oracle Data Type	JDBC Data Type
TIMESTAMP	TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMP
TIMESTAMP WITH TIME ZONE	VARCHAR
XMLType	CLOB

Session Parameter	Description
TIME_ZONE	The Oracle session time zone. Set to the current time zone as reported by the Java Virtual Machine.
NLS_TIMESTAMP_FORMAT	The default timestamp format. The Oracle driver uses the JDBC timestamp escape format:YYYY-MM_DD HH24:MI:SS.FF.
NLS_TIMESTAMP_TZ_FORMAT	The default timestamp with time zone format. The Oracle driver uses the JDBC timestamp escape format with the time zone field appended:YYYY-MM_DD HH24:MI:SS.FF TZH:TZM.