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 Driver Classes

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.

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.

{-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 default is 2048 (KB).

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.

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 obtain your password.

PortNumber

OPTIONAL

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.

ServerName

OPTIONAL

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.

{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.

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:

sales.us.acme.com

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.

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.

TNSServerName

OPTIONAL

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.

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 obtain your user name. Operating System authentication is not currently supported by the Oracle driver.

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. If this is the case, set WireProtocolMode=2 and the driver optimizes network traffic to the Oracle server for result sets containing multiple rows that have repeating data in some or all of the columns.

If you return single row result sets or result sets that do not contain repeating data, set this WireProtocolMode=1 or performance may be degraded.

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.

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:

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.

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:

ServerNameServiceName

PortNumberServerTypeSID

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.

The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The PORT parameter is used within the ADDRESS parameter to specify the port number for each server entry. For example:

(ADDRESS_LIST=

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

...

)

A port of 1521, the default port number when installing an Oracle database, is specified for server1.

ServerName = server_name

HOST = server_name

The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The HOST parameter is used within the ADDRESS parameter to specify the server name for each server entry. The server entry can be an IP address or a server name. For example:

(ADDRESS_LIST=

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

...

)

The server name server1 is specified in the first server entry.

ServerType = {shared | dedicated}

SERVER = {shared | dedicated}.

If SERVER=shared is specified in the CONNECT_DATA parameter in the tnsnames.ora file, the server process (UNIX) or thread (Windows) to be used is retrieved from a pool. For example:

(CONNECT_DATA=

(SERVER=shared)

)

When SERVER=shared, this setting allows there to be fewer processes than the number of connections, reducing the need for server resources.

When SERVER=dedicated, a server process is created to service only that connection. When that connection ends, so does the process (UNIX) or thread (Windows).

ServiceName = service_name

SERVICE_NAME = service_name

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:

sales.us.acme.com

The service name is specified in the CONNECT_DATA parameter. For example:

(CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com)

)

This parameter is mutually exclusive with the SID attribute and is useful to specify connections to an Oracle Real Application Clusters (RAC) system rather than a specific Oracle instance.

SID = SID

SID = SID

The Oracle System Identifier (SID) that refers to the instance of the Oracle database running on the server. The default Oracle SID that is configured when installing your Oracle database software is ORCL. The SID is specified in the CONNECT_DATA parameter. For example:

(CONNECT_DATA= (SID=ORCL)

)

This parameter is mutually exclusive with the SERVICE_NAME attribute.

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.

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:

Session Parameter

Description

TIME_ZONE

The Oracle session time zone. The Oracle driver sets the time zone to the current time zone as reported by the Java Virtual Machine.

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 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:

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

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.

Table 5-6 Character Set Conversions for the Oracle Driver

Code PageOverride Value

Description

UTF8

The driver uses the UTF8 character set to communicate with the Oracle server. Using this value forces the driver to use UTF8 to communicate with the Oracle server. This can negatively affect performance.

SJIS

The driver uses the JA16SJIS character set to communicate with the Oracle server. The driver uses the SHIFT-JIS code page to convert character data to the JA16SJIS character set.

ENHANCED_SJIS

The driver uses a modified version of the JA16SJIS character set to communicate with the Oracle server. The ENHANCED_SJIS character set provides all the mappings of the SHIFT-JIS character set. In addition, it maps the following MS-932 characters to the corresponding SJIS encoding for those characters:

\UFF5E Wave dash

\U2225 Double vertical line

\UFFE0 Cent sign

\UFF0D Minus sign

\UFFE1 Pound sign

\UFFE2 Not sign

The driver uses the ENHANCED_SJIS code page to convert character data to the JA16SJIS character set.

ENHANCED_SJIS_ORACLE

The driver uses a modified version of the JA16SJIS character set to communicate with the Oracle server. The ENHANCED_SJIS_ORACLE character set provides all the mappings of the SHIFT-JIS character set. In addition, it maps the following MS-932 characters to the corresponding characters to which Oracle maps them:

\UFF5E Wave dash

\U2225 Double vertical line

\UFFE0 Cent sign

\UFF0D Minus sign

\UFFE1 Pound sign

\UFFE2 Not sign

\U301C Tilde

The driver uses the ENHANCED_SJIS_ORACLE code page to convert character data to the JA16SJIS character set.

MS932

The driver uses the JA16SJIS character set to communicate with the Oracle server. The driver uses the MS932 code page to convert character data to the JA16SJIS character set. This value is provided for backward compatibility. Earlier versions of the driver used the MS932 code page when converting character data to JA16SJIS.

SQL Escape Sequences

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:

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:

SELECT * FROM foo WHERE bar > ?

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 (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:

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:

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.