WebLogic Type 4 JDBC Drivers
|
 |
 |
|
|
 |
 |
The Oracle Driver
Note: 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
- Performance Considerations
- Using tnsnames.ora Files
- Sample Connection Pool Configuration
- Data Types
- Character Set Conversion
- SQL Escape Sequences
- Isolation Levels
- Using Scrollable Cursors
- Batch Inserts and Updates
- Parameter Metadata Support
- ResultSet MetaData Support
- Rowset Support
- Auto-Generated Keys Support
Oracle Database Version Support
The BEA WebLogic Type 4 JDBC 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 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
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
All connection property names are case-insensitive. For example, Password is the same as password. Required properties are noted as such.
|
{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 connection pools, 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 database serveruntil 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 serverwhen 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 Performance Considerations 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 Performance Considerations 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 Performance Considerations for information about specifying the server type using a |
|
|
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 Performance Considerations 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 Performance Considerations 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 Performance Considerations 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 Performance Considerations 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. |
|
|
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. If this is the case, set
|
Performance Considerations
Setting the following connection properties for the Oracle driver as described in the following list can improve performance for your applications:
- BatchPerformanceWorkaround
- CatalogOptions
- InsensitiveResultSetBufferSize
- MaxPooledStatements
- ResultSetMetaDataOptions
- ServerType
- WireProtocolMode
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:
- 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 5-1 shows connection information in a tnsnames.ora file configured for the net service name entries, FITZGERALD.SALES and ARMSTRONG.ACCT.
Listing 5-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 5-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.
Sample Connection Pool Configuration
Table 5-3 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-4 lists configuration attributes for a sample WebLogic Server connection pool that uses the XA version of the driver.
|
|
|
Data Types
Table 5-5 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 theTIME_ZONEsession 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 theNLS_TIMESTAMP_TZ_FORMATsession parameter. If not, the Oracle server generates an error when it attempts to convert the string to theTIMESTAMP WITH LOCAL TIME ZONEtype.
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 theTIME_ZONEsession 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 theNLS_TIMESTAMP_TZ_FORMATsession parameter. If not, the Oracle server generates an error when it attempts to convert the string to theTIMESTAMP WITH TIME ZONEtype.
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 5-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 5-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();
}
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 5-6 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.
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 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:
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 <>.
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 statement contains a predicate value expression that can be targeted against the source tables in the associated FROM clause. For example:
In this case, the value expression "bar" can be targeted against the table "foo" to determine the appropriate metadata for the parameter.
- The statement contains a predicate value expression part that is a nested query. The nested query's metadata must describe a single column. For example:
SELECT * FROM foo WHERE bar > ?
SELECT * FROM foo WHERE (SELECT x FROM y WHERE z = 1) < ?
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, mployeeInfo 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:
- When using an
Insertstatement that contains no parameters, the Oracle driver supports the following form of theStatement.executeandStatement.executeUpdatemethods 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
Insertstatement that contains parameters, the Oracle driver supports the following form of theConnection.prepareStatementmethod 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.
