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
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.
|
{true | false}. Determines the method used to execute batch operations. If set to true, the native Oracle batch mechanism is used. The native Oracle batch mechanism does not return individual update counts for each statement or parameter set in the batch. For this reason, the driver returns a value of SUCCESS_NO_INFO (-2) for each entry in the returned update count array. If set to false, the JDBC 3.0-compliant batch mechanism is used. If an application can accept not receiving update count information, setting this property to true can significantly improve performance. The default is false. See Batch Inserts and Updates for details. |
|
|
This property is recognized for compatibility with existing data sources, but we recommend that you use the CatalogOptions property instead to include synonyms in result sets. |
|
|
{0 | 1 | 2 | 3}. Determines the type of information included in result sets returned from catalog functions. If set to 0, result sets contain default DatabaseMetaData results. If set to 1, result sets contain Remarks information returned from the DatabaseMetaData methods: getTables and getColumns. If set to 2, result sets contain synonyms returned from the DatabaseMetaData methods: getColumns, getProcedures, getProcedureColumns, and getIndexInfo. If set to 3, result sets contain remarks and synonyms (as described in options 1 and 2). |
|
|
The number of times the driver retries connections to a database server until a successful connection is established. Valid values are 0 and any positive integer. If set to 0, the driver does not retry a connection attempt. |
|
|
The number of seconds the driver waits before retrying connections to a database server when ConnectionRetryCount is set to a positive integer. |
|
|
{true | false}. If set to true, allows column values with the TIMESTAMP WITH TIME ZONE data type (Oracle9i or higher) to be retrieved as a JDBC TIMESTAMP data type. If set to false, column values with the TIMESTAMP WITH TIME ZONE data type must be retrieved as a string. See TIMESTAMP WITH TIME ZONE Data Type for more information. |
|
|
{-1 | 0 | x}. Determines the amount of memory used by the driver to cache insensitive result set data. It must have one of the following values: If set to -1, the driver caches all insensitive result set data in memory. If the size of the result set exceeds available memory, an OutOfMemoryException is generated. Because the need to write result set data to disk is eliminated, the driver processes the data more efficiently. If set to 0, the driver caches all insensitive result set data in memory, up to a maximum of 2 GB. If the size of the result set data exceeds available memory, the driver pages the result set data to disk. Because result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. If set to x, where x is a positive integer, the driver caches all insensitive result set data in memory, using this value to set the size (in KB) of the memory buffer for caching insensitive result set data. If the size of the result set data exceeds the buffer size, the driver pages the result set data to disk. Because the result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. Specifying a buffer size that is a power of 2 results in more efficient memory use. |
|
|
The maximum time in seconds that attempts to create a database connection will wait. A value of |
|
|
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 obtain your password. |
|
|
The TCP port of the Oracle listener running on the Oracle database server. The default is 1521, which is the Oracle default port number when installing the Oracle database software. If using a tnsnames.ora file to provide connection information, do not specify this property. See Using tnsnames.ora Files for information about specifying a port number for the Oracle listener using a tnsnames.ora file. |
|
|
Specifies either the IP address or the server name (if your network supports named servers) of the Oracle server. For example, 122.23.15.12 or OracleAppServer. If using a tnsnames.ora file to provide connection information, do not specify this property. See Using tnsnames.ora Files for information about specifying a server name using a tnsnames.ora file. |
|
|
{Shared | Dedicated}. Specifies whether the connection is established using a shared or dedicated server process (UNIX) or thread (Windows). If set to Shared, the server process to be used is retrieved from a pool. The socket connection between the client and server is made to a dispatcher process on the server. This setting allows there to be fewer processes than the number of connections, reducing the need for server resources. Use this value when a server must handle many users with fewer server resources. If set to Dedicated, a server process is created to service only that connection. When that connection ends, so does the process (UNIX) or thread (Windows). The socket connection is made directly between the application and the dedicated server process or thread. When connecting to UNIX servers, a dedicated server process can provide significant performance improvement, but uses more resources on the server. When connecting to Windows servers, the server resource penalty is insignificant. Use this value if you have a batch environment with low numbers of users. If unspecified, the driver uses the server type set on the server. If using a tnsnames.ora file to provide connection information, do not specify this property. See Using tnsnames.ora Files for information about specifying the server type using a tnsnames.ora file. |
|
|
The database service name that specifies the database used for the connection. The service name is a string that is the global database name-a name that typically comprises the database name and domain name. For example: This property is useful to specify connections to an Oracle Real Application Clusters (RAC) system rather than a specific Oracle instance because the nodes in a RAC system share a common service name. If using a tnsnames.ora file to provide connection information, do not specify this property. See Using tnsnames.ora Files for information about specifying the database service name using a tnsnames.ora file. |
|
|
The Oracle System Identifier that refers to the instance of the Oracle database running on the server. This property is mutually exclusive with the ServiceName property. The default is ORCL, which is the default SID that is configured when installing your Oracle database. If using a tnsnames.ora file to provide connection information, do not specify this property. See Using tnsnames.ora Files for information about specifying an Oracle SID using a tnsnames.ora file. |
|
|
The path and filename to the tnsnames.ora file from which connection information is retrieved. The tnsnames.ora file contains connection information that is mapped to Oracle net service names. Using a tnsnames.ora file to centralize connection information simplifies maintenance when changes occur. The value of this property must be a valid path and filename to a tnsnames.ora file. If you specify this property, you also must specify the TNSServerName property. If this property is specified, do not specify the following properties to prevent connection information conflicts: If any of these properties are specified in addition to this property, the driver generates an exception. See Using tnsnames.ora Files for information about using tnsnames.ora files to connect. |
|
|
The Oracle net service name used to reference the connection information in a tnsnames.ora file. The value of this property must be a valid net service name entry in the tnsnames.ora file specified by the TNSNamesFile property. If this property is specified, you also must specify the TNSNamesFile property. If this property is specified, do not specify the following properties to prevent connection information conflicts: If any of these properties are specified in addition to this property, the driver generates an exception. See Using tnsnames.ora Files for information about using tnsnames.ora files to connect. |
|
|
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 obtain your user name. Operating System authentication is not currently supported by the Oracle driver. |
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.
- 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 col1operator? [{AND | OR} col2operator?]
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:
The application fetches the values of generated keys from the driver using the Statement.getGeneratedKeys method.
