Note: | The WebLogic Type 4 JDBC Oracle driver described in this document has been deprecated as of release 10.3 of WebLogic Server. It will be removed in the next release of WebLogic Server. Instead of this deprecated driver, use the Oracle Thin Driver that is also provided with WebLogic Server. For details about the Oracle Thin Driver, see “Using Third-Party JDBC Drivers with WebLogic Server” in Configuring and Managing WebLogic JDBC. |
The following sections describe how to configure and use the WebLogic Type 4 JDBC Oracle driver:
The WebLogic Type 4 JDBC Oracle driver (the "Oracle driver") supports:
The driver classes for the WebLogic Type 4 JDBC Oracle driver are:
Use these driver classes when configuring a JDBC data source in your WebLogic Server domain.
The connection URL format for the Oracle driver is:
jdbc:bea:oracle://hostname
:port
[;property
=value
[;...]]
hostname
is the TCP/IP address or TCP/IP host name of the server to which you are connecting. See Using IP Addresses for details on using IP addresses.Note: | Untrusted applets cannot open a socket to a machine other than the originating host. |
port
is the number of the TCP/IP port. property=value
specifies connection properties. For a list of connection properties and their valid values, see Oracle Connection Properties.jdbc:bea:oracle://server3:1521;ServiceName=ORCL;User=test;Password=secret
See Using tnsnames.ora Files for instructions on retrieving connection information from an Oracle tnsnames.ora file.
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
All connection property names are case-insensitive. For example, Password is the same as password. Required properties are noted as such.
{auto | kerberos | kerberosUIDPassword | ntlm | client | userIDPassword}. Determines which authentication method the driver uses when establishing a connection.
If set to auto (the default), the driver uses user ID/password, Kerberos, or NTLM authentication when establishing a connection. The driver selects an authentication method based on a combination of criteria, such as whether the application provides a user ID, the driver is running on a Windows platform, and the driver can load the DLL required for NTLM authentication. See Using the AuthenticationMethod Property for more information about using this value.
If set to kerberos, the driver uses Kerberos authentication. The driver ignores any user ID or password specified.
If set to kerberosUIDPassword, the driver first uses Kerberos to authenticate the user. Next, the driver reauthenticates the user using user ID/password authentication. If a user ID and password are not specified, the driver throws an exception. If either Kerberos or user ID/password authentication fails, the connection attempt fails and the driver throws an exception.
If set to ntlm, the driver uses NTLM authentication if the DLL required for NTLM authentication can be loaded. If the driver cannot load the DLL, the driver throws an exception. The driver ignores any user ID or password specified. This value is supported for Windows clients only.
If set to client, the driver uses the user ID of the user logged onto the system on which the driver is running to authenticate the user to the database. The Oracle database server relies on the client to authenticate the user and does not provide additional authentication. The driver ignores any user ID or password specified.
If set to userIdPassword, the driver uses user ID/password authentication. If a user ID and password are not specified, the driver throws an exception.
NOTE: The values type2 and none are deprecated, but are recognized for backward compatibility. We recommend that you use the ntlm and userIdPassword values, respectively, instead.
See Authentication for more information.
|
|||
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 an application can accept not receiving update count information, setting this property to true can significantly improve performance.
See Batch Inserts and Updates for details.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
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 1, result sets contain remarks information returned from the DatabaseMetaData methods: getTables() and getColumns().
If set to 2 (the default), result sets contain synonyms returned from the DatabaseMetaData methods: getColumns(), getImportedKeys(), getExportedKeys(), getPrimaryKey(), getProcedures(), getProcedureColumns(), and getIndexInfo().
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
{UTF8 | SJIS | ENHANCED_SJIS | ENHANCED_SJIS_ORACLE | MS932}. The code page to be used by the driver to convert Character data. The specified code page overrides the default database code page or column collation. All Character data returned from or written to the database is converted using the specified code page. This option has no effect on how the driver converts character data to the national character set.
By default, the driver automatically determines which code page to use to convert Character data. Use this property only if you need to change the driver’s default behavior.
If set to UTF8, the driver uses the UTF-8 code page to send data to the Oracle server as Unicode. The UTF8 code page converts data from the Java String format UTF-16 to UTF-8. If you specify this value, the driver forces the value of the
WireProtocolMode property to 2.
If set to SJIS, the driver uses the SHIFT-JIS code page to convert character data to the JA16SJIS character set.
If set to ENHANCED_SJIS, the driver uses the ENHANCED_SJIS code page to convert character data from the Java String format UTF-16 to SJIS as defined by the ICU character conversion library. 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 This value is provided for backward compatibility. Only use this value when the Oracle database character set is SHIFT_JIS.
If set to ENHANCED_SJIS_ORACLE, the driver uses the ENHANCED_SJIS_ORACLE code page to convert Character data from the Java String format UTF-16 to Oracle’s definition of SJIS. When the driver connects to an Oracle database with a JA16SJIS character set, the driver uses this code page by default. The ENHANCED_SJIS_ORACLE code page is a super set of the MS932 code page. Only use this value when the Oracle database character set is SHIFT_JIS.
|
|||
If set to MS932, the driver uses the Microsoft MS932 code page to convert Character data from the Java String format UTF-16 to SJIS. This value is provided for backward compatibility because earlier versions of the driver used the MS932 code page when converting Character data to JA16SJIS. Only use this value when the Oracle database character set is SHIFT_JIS.
|
|||
{serverDefault | waitImmediate | waitBatch | noWaitImmediate | noWaitBatch}. Typically, redo changes generated by update transactions are written to disk immediately when an transaction is committed, and the session waits for the disk write to complete before returning control to the application. Oracle 10g R2 can let the log writer write the redo changes to disk in its own time instead of immediately and return control to the application before the disk write is complete instead of waiting. This property controls this behavior by setting the value of the Oracle COMMIT_WRITE session parameter.
Not waiting for redo log changes to be written to disk improves performance for applications that have both of the following characteristics:
If set to serverDefault (the default), the driver uses the redo log behavior set by the database server.
If set to waitImmediate, the commit operation does not return control to the application until redo changes are written to disk. Redo changes are written to disk immediately. Use this value if your application processes multiple update transactions one at a time.
If set to waitBatch, the commit operation does not return control to the application until redo changes are written to disk. The write task may be deferred by the server until additional transactions are ready to be written to disk. Use this value if your application processes multiple update transactions simultaneously. Using this value when an application performs only a few transactions decreases performance
|
|||
If set to noWaitImmediate, redo changes are written to disk immediately, but the commit operation returns control to the application without waiting for this operation to complete. Use this value if your application processes multiple update transactions one at time and data integrity is not critical.
If set to noWaitBatch, the redo write task may be deferred by the server until additional transactions are ready to be written to disk, but the commit operation returns control to the application without waiting for this operation to complete. Use this value if your application processes multiple update transactions simultaneously and data integrity is not critical.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
The number of times the driver retries connections to the database server until a successful connection is established. Valid values are 0 and any positive integer.
If an application sets a login timeout value (for example, using DataSource.loginTimeout or DriverManager.loginTimeout), the login timeout takes precedence over this property.
The ConnectionRetryDelay property specifies the wait interval, in seconds, used between attempts.
|
|||
The number of seconds the driver waits before retrying connections to the database server when ConnectionRetryCount is set to a positive integer.
The ConnectionRetryCount property specifies the number of times the driver will attempt to connect to the database server.
|
|||
If set to 1 (the default), the driver checks the data type being requested against the data type of the column from which the data is being returned. If a conversion between the requested type and column type is not defined, the driver generates an "unsupported data conversion" exception regardless of the column value.
|
|||
{true | false}. Determines whether a cancel request sent as the result of a query timing out is subject to the same query timeout value as the statement it cancels.
If set to true, the cancel request times out using the same timeout value, in seconds, that is set for the statement it cancels. For example, if your application calls Statement.setQueryTimeout(5) on a statement and that statement is cancelled because its timeout value was exceeded, a cancel request is sent that also will time out if its execution exceeds 5 seconds. If the cancel request times out, because the server is down, for example, the driver throws an exception indicating that the cancel request was timed out and the connection is no longer valid.
|
|||
{noEncryption | SSL}. Determines whether SSL encryption is used to encrypt and decrypt data transmitted over the network between the driver and database server.
NOTE: Connection hangs can occur if the driver attempts to connect to a database server that requires SSL. You may want to set a login timeout using the
LoginTimeout property to avoid problems when connecting to a server that requires SSL.
If set to SSL, data is encrypted using SSL. If the database server does not support SSL, the connection fails and the driver throws an exception. When SSL is enabled, the following properties also apply:
See Using tnsnames.ora Files for information about enabling SSL encryption using a tnsnames.ora file.
See Data Encryption for more information about configuring data encryption.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
{true | false}. If set to true, allows column values with the TIMESTAMP WITH TIME ZONE data type (Oracle9i or higher) to be returned 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.
|
|||
{host_name | #SERVERNAME#}. Specifies a host name for certificate validation when SSL encryption is enabled (
EncryptionMethod=SSL ) and validation is enabled (ValidateServerCertificate=true ). This property is optional and provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.
If a host name is specified, the driver compares the specified host name to the DNSName value of the SubjectAlternativeName in the certificate. If a DNSName value does not exist in the SubjectAlternativeName or if the certificate does not have a SubjectAlternativeName, the driver compares the host name with the Common Name (CN) part of the certificate’s Subject name. If the values do not match, the connection fails and the driver throws an exception.
If #SERVERNAME# is specified, the driver compares the server name specified in the connection URL or data source of the connection to the DNSName value of the SubjectAlternativeName in the certificate. If a DNSName value does not exist in the SubjectAlternativeName or if the certificate does not have a SubjectAlternativeName, the driver compares the host name to the CN parts of the certificate’s Subject name. If the values do not match, the connection fails and the driver throws an exception.
NOTE: If multiple CN parts are present, the driver validates the host name against each CN part. If any one validation succeeds, a connection is established.
If unspecified, the driver uses the server name specified in the connection URL or data source of the connection to validate the certificate.
See Data Encryption for information about configuring for authentication.
|
|||
Specifies one or multiple SQL commands to be executed by the driver after it has established the connection to the database and has performed all initialization for the connection. For example:
|
|||
{-1 | 0 | x}. Determines the amount of memory used by the driver to cache insensitive result set data.
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.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
{true | false}. Determines whether the driver uses its internal conversion algorithm or the JVM conversion algorithm when converting double or float values to string values.
If set to true, the driver uses the JVM algorithm when converting double or float values to string values.
If set to false (the default), the driver uses its internal algorithm when converting double or float values to string values. Setting the property to false improves performance; however, slight rounding differences can occur when compared to the same conversion using the JVM algorithm. These differences are within the allowable error of the double and float data types.
|
|||
Specifies the directory of the keystore file to be used when SSL is enabled using the
EncryptionMethod property and SSL client authentication is enabled on the database server. The keystore file contains the certificates that the client sends to the server in response to the server’s certificate request.
|
|||
Specifies the password used to access the keystore file when SSL is enabled using the
EncryptionMethod property and SSL client authentication is enabled on the database server. The keystore file contains the certificates that the client sends to the server in response to the server’s certificate request.
|
|||
Specifies the password used to access the individual keys in the keystore file when SSL is enabled using the
EncryptionMethod property and SSL client authentication is enabled on the database server. This property is useful if any of the keys in the keystore file have a different password than the keystore file.
|
|||
A case-insensitive password used to connect to your Oracle database. A password is required only if user ID/password authentication is enabled on your database. If so, contact your system administrator to obtain your password.
See Authentication for information about configuring for authentication.
|
|||
The TCP port of the Oracle listener running on the Oracle database server. The default is 1521,which is the default port number the Oracle database software uses during its installation.
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.
|
|||
{positive integer | -1 | 0}. Sets the default query timeout (in seconds) for all statements created by a connection.
If set to a positive integer, the driver uses the value as the default timeout for any statement created by the connection. To override the default timeout value set by this connection option, call the Statement.setQueryTimeout() method to set a timeout value for a particular statement.
|
|||
{0 | 1}. The Oracle driver can return table name information in the ResultSet metadata for Select statements if your application requires that information.
If set to 0 (the default) and the ResultSetMetaData.getTableName() method is called, the driver does not perform additional processing to determine the correct table name for each column in the result set. In this case, the getTableName() method may return an empty string for each column in the result set.
If set to 1 and the ResultSetMetaData.getTableName() method is called, the driver performs additional processing to determine the correct table name for each column in the result set. The 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.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
{true | false}. Determines whether FLOAT, BINARY_FLOAT, and BINARY_DOUBLE parameters are sent to the database server as a string or as a floating point number.
If set to true, the driver sends FLOAT, BINARY_FLOAT, and BINARY_DOUBLE parameters to the database server as string values.
If set to false (the default), the driver sends FLOAT, BINARY_FLOAT, and BINARY_DOUBLE parameters to the database server as floating point numbers. When Oracle overloaded stored procedures are used, this value ensures that the database server can determine the correct stored procedure to call based on the parameter’s data type.
NOTE: Numbers larger than 1.0E127 or smaller than 1.0E-130 cannot be converted to Oracle’s number format for Oracle 9i databases using floating point numbers. When a number larger than 1.0E127 or smaller than 1.0E-130 is encountered, the driver throws an exception. If your application uses numbers in this range against an Oracle 9i database, set this property to true.
|
|||
Specifies either the IP address in IPv4 or IPv6 format, or the server name (if your network supports named servers) of the Oracle server. For example, 122.23.15.12 or OracleAppServer.
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.
See Using tnsnames.ora Files for information about specifying the server type using a tnsnames.ora file.
See Performance Considerations for information about specifying the server type using a
tnsnames.ora file.
|
|||
The database service name that specifies the database used for the connection. This property is mutually exclusive with the
SID property. 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.
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.
See Performance Considerations for information about specifying an Oracle SID using a tnsnames.ora file.
|
|||
{sysdba | sysoper}. Specifies whether the user is logged on the database with the Oracle system privilege SYSDBA or the Oracle system privilege SYSOPER. For example, you may want the user to be granted the SYSDBA privilege to allow the user to create or drop a database.
Refer to your Oracle documentation for information about which operations are authorized for the SYSDBA and SYSOPER system privileges.
NOTE: The user must be granted SYSDBA or SYSOPER system privileges before the connection is attempted by the driver. If not, the driver throws an exception and the connection attempt fails.
|
|||
{true | false}. Determines whether the driver supports Oracle linked servers, which means a mapping has been defined in one Oracle server to another Oracle server.
|
|||
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.
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 throws 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, 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 throws an exception. See Using tnsnames.ora Files for information about using tnsnames.ora files to connect.
|
|||
Specifies the directory of the truststore file to be used when SSL is enabled using the
EncryptionMethod property and server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts.
|
|||
Specifies the password used to access the truststore file when SSL is enabled using the
EncryptionMethod property and server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts.
|
|||
The case-insensitive default user name used to connect to your Oracle database. A user name is required only if user ID/password authentication is enabled on your database. If so, contact your system administrator to obtain your user name.
SeeAuthentication for information about configuring for authentication.
|
|||
{true | false}. Determines whether the driver validates the certificate sent by the database server when SSL encryption is enabled (
EncryptionMethod=SSL ). When using SSL server authentication, any certificate sent by the server must be issued by a trusted Certificate Authority (CA). Allowing the driver to trust any certificate returned from the server even if the issuer is not a trusted CA is useful in test environments because it eliminates the need to specify truststore information on each client in the test environment.
If set to true (the default), the driver validates the certificate sent by the database server. Any certificate from the server must be issued by a trusted CA in the truststore file. if the
HostNameInCertificate property is specified, the driver also validates the certificate using a host name. The HostNameInCertificate property is optional and provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.
If set to false, the driver does not validate the certificate sent by the database server. The driver ignores any truststore information specified by the TrustStore and TrustStorePassword properties or Java system properties.
Truststore information is specified using the TrustStore and TrustStorePassword properties or by using Java system properties.
See Data Encryption for information about configuring for authentication.
|
|||
{1 | 2}. Specifies whether the driver optimizes network traffic to the Oracle server for result sets for repeating data in some or all columns, and for inserts and updates of images, pictures, long text, or binary data (Blob and Clob data).
If set to 1 (the default), the driver operates in normal wire protocol mode without optimizing network traffic for result sets for repeating data in some or all columns, and inserts and updates of Blob and Clob data.
Result sets containing multiple rows that have repeating data in some or all columns. Specifically, if the same column contains identical data across multiple consecutive rows in the result set, setting this value can improve performance. Setting this value may degrade performance for single row result sets or result sets that do not contain repeating data.
See Performance Considerations for information about configuring this property for optimal performance.
|
Setting the following connection properties for the Oracle driver as described in the following list can improve performance for your applications:
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.
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()
.
Typically, redo changes generated by update transactions are written to disk immediately when the transaction is committed, and the session waits for the disk write to complete before returning control to the application. Oracle 10g R2 can let the log writer write the redo changes to disk in its own time instead of immediately and return control to the application before the disk write is complete instead of waiting. Not waiting for the disk write improves performance for applications that perform update operations and where data integrity is not critical. For example, most banking applications cannot tolerate data loss in the event that the server has a problem writing the redo changes to disk or fails during the process, but many logging applications for diagnostic purposes can.
Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data.
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.
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.
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.
In either of these cases, performance can be improved by setting this property to 2.
When set to 2, the driver optimizes network traffic to the Oracle server for repeating or long data.
then this property should be set to 1; otherwise, performance may be degraded.
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:
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.
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.
To retrieve connection information from an Oracle tnsnames.ora file with the Oracle driver, you must inform the driver which tnsnames.ora file (using the TNSNamesFile
property) and Oracle service name entry (using the TNSServerName
property) to use so that the driver can reference the correct connection information. For example:
<JDBCConnectionPool
DriverName="weblogic.jdbc.oracle.OracleDriver"
Name="myDriver"
PasswordEncrypted="{3DES}r8a+P5qIVJzgiWQDTAN/OA=="
Properties="TNSServerName=myTNSServerName;user=user;TNSNamesFile=/usr/local/network/admin/tnsnames.ora"
Targets="myserver"
TestConnectionsOnReserve="true"
TestTableName="SQL SELECT 1 FROM DUAL"
URL="jdbc:bea:oracle:TNSNamesFile=/usr/local/network/admin/tnsnames.ora"
XAPasswordEncrypted="" />
The URL specifies the path and filename of the tnsnames.ora
file (jdbc:bea:oracle:TNSNamesFile=/usr/local/network/admin/tnsnames.ora
) and the Properties specifies the server name (TNSServerName=myTNSServerName
) to use for the connection.
Notes: |
TNSNamesFile
property.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.
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 throws 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.
Table 6-3 lists the data types supported by the Oracle driver and describes how they are mapped to the JDBC data types.
BINARY_DOUBLE1
|
|
TIMESTAMP2
|
|
1Supported only for Oracle 10g. 2Supported only for Oracle 9i and higher. |
See Returning and Inserting/Updating XML Data for more information about the XMLType data type. See GetTypeInfo, for a description of the data types returned by the getTypeInfo() method.
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.
At connection time, the Oracle driver sets the following date/time session parameters:
The Oracle TIMESTAMP
data type is mapped to the JDBC TIMESTAMP
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:
PreparedStatement.setTimestamp
, for example), the value set is converted to the time zone specified by the TIME_ZONE
session parameter. 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.
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 as:
'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
'
YYYY
is the 4-digit year.MM
is the month.DD
is the day.HH24
is the hour in 24-hour format.MI
is the minutes.SS
is the seconds.FF
is the fractional seconds.TZH
is the time zone hours and TZM
is the time zone minutes. The time zone is represented as the difference in hours and minutes between the time zone and GMT..
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 returned 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:
PreparedStatement.setTimestamp()
, for example), the value set is converted to the time zone specified by the TIME_ZONE
session parameter. 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.
For Oracle 9i and higher, the Oracle driver supports the Oracle XMLType data type. The driver maps the Oracle XMLType data type to the JDBC CLOB data type.
The driver can return XML data as character data. For example, given a database table defined as:
CREATE TABLE xmlTable (id int, xmlCol XMLType NOT NULL)
the driver can return the XML data as character data using the following code:
String sql="SELECT xmlCol FROM xmlTable";
ResultSet rs=stmt.executeQuery(sql)
String charXML=rs.getString(1)
The result set column is described with a column type of CLOB and the column type name is xmlType.
Your application can use the following methods to return data stored in XML columns as character data:
ResultSet.getString()
ResultSet.getCharacterStream()
ResultSet.getClob()
CallableStatement.getString()
CallableStatement.getClob()
The driver converts the XML data returned from the database server from the character set encoding used by the database server to the UTF-16 Java String encoding.
Your application can use the following method to return data stored in XML columns as ASCII data:
ResultSet.getAsciiStream()
The driver converts the XML data returned from the database server from the character set encoding used by the database server to the ISO-8859-1 (latin1) encoding.
Note: | The conversion caused by using the getAsciiStream() method may create XML that is not well-formed because the content encoding is not the default encoding and does not contain an XML declaration specifying the content encoding. Do not use the getAsciiStream() method if your application requires well-formed XML. |
When inserting to or updating XMLType columns, the data to be inserted or updated must be the 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 specified as a parameter marker, the parameter value can be set using the following methods:
PreparedStatement.setString()
PreparedStatement.setCharacterStream()
PreparedStatement.setClob()
PreparedStatement.setAsciiStream()
The driver converts the character representation of the data to the XML character set used by the database server and sends the converted XML data to the server. The driver does not parse or remove any XML processing instructions.
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 XMLTable 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 XMLTable VALUES (?, xmltype(?))";
PreparedStatement prepStmt = con.prepareStatement(sql);
prepStmt.setInt(1, 2);
prepStmt.setString(2, xmlStr);
prepStmt.executeUpdate();
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 returned 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;
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();
}
// 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();
}
Authentication protects the identity of the user so that user credentials cannot be intercepted by malicious hackers when transmitted over the network. See Authentication for an overview.
The Oracle driver supports the following methods of authentication:
This method requires knowledge of how to configure your Kerberos environment and supports Windows Active Directory Kerberos and MIT Kerberos.
Note: | Because the database server does not authenticate the user when client authentication is used, use this method of authentication if you can guarantee that only trusted clients can access the database server. |
Except for NTLM authentication, which provides authentication for Windows clients only, these authentication methods provide authentication when the driver is running on any supported platform.
The AuthenticationMethod
connection property controls which authentication mechanism the driver uses when establishing connections. See Using the AuthenticationMethod Property for information about setting the value for this property.
The AuthenticationMethod
connection property controls which authentication mechanism the driver uses when establishing connections. When AuthenticationMethod=auto
(the default), the driver uses user ID/password, Kerberos, or NTLM authentication when establishing a connection based on the following criteria:
User
property provides the user ID. The Password
property provides the password.
When AuthenticationMethod=kerberos
, the driver uses Kerberos authentication when establishing a connection. The driver ignores any values specified by the User and Password properties.
When AuthenticationMethod=kerberosUIDPassword
, the driver first uses Kerberos when establishing a connection. Next, the driver reauthenticates the user using user ID/password authentication. The User
property provides the user ID. The Password
property provides the password. If a user ID and password are not specified, the driver throws an exception. If either Kerberos or user ID/password authentication fails, the connection attempt fails and the driver throws an exception.
When AuthenticationMethod=ntlm
, the driver uses NTLM authentication when establishing a connection if the driver can load the DLL required for NTLM authentication. If the driver cannot load the DLL, the driver throws an exception. The driver ignores any values specified by the User and Password properties.
When AuthenticationMethod=client
, the driver uses client authentication when establishing a connection. The Oracle database server relies on the client to authenticate the user and does not provide additional authentication. The driver ignores any values specified by the User and Password properties.
When AuthenticationMethod=userIdPassword
, the driver uses user ID/password authentication when establishing a connection. The User
property provides the user ID. The Password
property provides the password. If a user ID is not specified, the driver throws an exception.
AuthenticationMethod
property to auto or userIdPassword. See Using the AuthenticationMethod Property for more information about setting a value for this property.User
property to provide the user ID. Password
property to provide the password.This section provides requirements and instructions for configuring Kerberos authentication for the Oracle driver.
Verify that your environment meets the requirements listed in Table 6-4 before you configure the driver for Kerberos authentication.
During installation, WebLogic Server installs the following files required for Kerberos authentication in the server/lib subdirectory of your WebLogic Server installation directory:
Note: | Do not modify the JDBCDriverLogin.conf file. |
AuthenticationMethod
property to auto (the default) or kerberos. See Using the AuthenticationMethod Property for more information about setting a value for this property.Note: | In Windows Active Directory, the Kerberos realm name is the Windows domain name and the KDC name is the Windows domain controller name. |
For example, if your Kerberos realm name is XYZ.COM and your KDC name is kdc1, your krb5.conf file would look like this:
[libdefaults]
default_realm = XYZ.COM
[realms]
XYZ.COM = {
kdc = kdc1
}
If the krb5.conf file does not contain a valid Kerberos realm and KDC name, the following exception is thrown:
Message:[BEA][Oracle JDBC Driver]Could not establish a connection using integrated security: No valid credentials provided
The krb5.conf file installed by WebLogic Server is configured to load automatically unless the java.security.krb5.conf system property is set to point to another Kerberos configuration file.
By default, when Kerberos authentication is used, the Oracle driver takes advantage of the user name and password maintained by the operating system to authenticate users to the database. By allowing the database to share the user name and password used for the operating system, users with a valid operating system account can log into the database without supplying a user name and password.
There may be times when you want the driver to use a set of user credentials other than the operating system user name and password. For example, many application servers or Web servers act on behalf of the client user logged on the machine on which the application is running, rather than the server user.
If you want the driver to use a set of user credentials other than the operating system user name and password, include code in your application to obtain and pass a jjavax.security.auth.Subject used for authentication as shown in the following example.
import javax.security.auth.Subject;
import javax.security.auth.login.LoginContext;
import java.sql.*;
// The following code creates a javax.security.auth.Subject instance
// used for authentication. Refer to the Java Authentication
// and Authorization Service documentation for details on using a
// LoginContext to obtain a Subject.
LoginContext lc = null;
Subject subject = null;
try {
lc = new LoginContext("JaasSample", new TextCallbackHandler());
lc.login();
subject = lc.getSubject();
}
catch (Exception le) {
... // display login error
}
// This application passes the javax.security.auth.Subject
// to the driver by executing the driver code as the subject
Connection con =
(Connection) Subject.doAs(subject, new PrivilegedExceptionAction() {
public Object run() {
Connection con = null;
try {
Class.forName("com.ddtek.jdbc.oracle.OracleDriver");
String url = "jdbc:bea:oracle://myServer:1521";
con = DriverManager.getConnection(url);
}
catch (Exception except) {
... //log the connection error
Return null;
}
return con;
}
});
// This application now has a connection that was authenticated with
// the subject. The application can now use the connection.
Statement stmt = con.createStatement();
String sql = "SELECT * FROM employee";
ResultSet rs = stmt.executeQuery(sql);
... // do something with the results
To use Kerberos authentication, the application user first must obtain a Kerberos Ticket Granting Ticket (TGT) from the Kerberos server. The Kerberos server verifies the identity of the user and controls access to services using the credentials contained in the TGT.
If the application uses Kerberos authentication from a Windows client and the Kerberos authentication is provided by Windows Active Directory, the application user is not required to log onto the Kerberos server and explicitly obtain a TGT. Windows Active Directory automatically obtains a TGT for the user.
The application user must explicitly obtain a TGT in the following cases:
To explicitly obtain a TGT, the user must log onto the Kerberos server using the kinit command. For example, the following command requests a TGT from the server with a lifetime of 10 hours, which is renewable for 5 days:
kinit -l 10h -r 5d user
where user
is the application user.
Refer to your Kerberos documentation for more information about using the kinit command and obtaining TGTs for users.
This section provides requirements and instructions for configuring NTLM authentication for the Oracle driver.
Verify that your environment meets the requirements listed in Table 6-5 before you configure the driver for NTLM authentication.
WebLogic Type 4 JDBC drivers provide the following NTLM authentication DLLs:
where xx is a two-digit number.
The DLLs are located in the WL_HOME
/server/lib
directory (where WL_HOME
is the directory in which you installed WebLogic Server). If the application using NTLM authentication is running in a 32-bit JVM, the driver automatically uses DDJDBCAuthxx.dll. Similarly, if the application is running in a 64-bit JVM, the driver uses DDJDBC64Authxx.dll or DDJDBCx64Authxx.dll.
AuthenticationMethod
property to auto or ntlm. SeeUsing the AuthenticationMethod Property for more information about setting a value for this property.WL_HOME
/server/lib
directory to the Windows system path, where WL_HOME
is the directory in which you installed WebLogic Server.WL_HOME
/server/lib
to a directory that is on the Windows system path, where WL_HOME
is the directory in which you installed WebLogic Server.LoadLibraryPath
property to specify the location of the NTLM authentication DLLs. For example, if you install the driver in a directory named "DataDirect" that is not on the Windows system path, you can use the LoadLibraryPath
property to specify the directory containing the NTLM authentication DLLs:jdbc:datadirect:oracle://server3:1521;
ServiceName=ORCL;LoadLibraryPath=C:\DataDirect\lib;
User=test;Password=secret
Set the AuthenticationMethod
property to client. See Using the AuthenticationMethod Property for more information about setting a value for this property.
The Oracle driver supports SSL for data encryption. SSL secures the integrity of your data by encrypting information and providing authentication. See Data Encryption Across the Network for an overview.
See Using tnsnames.ora Files for information about configuring a tnsnames.ora file for SSL encryption.
Note: | Connection hangs can occur when the driver is configured for SSL and the database server does not support SSL. You may want to set a login timeout using the LoginTimeout property to avoid problems when connecting to a server that does not support SSL. |
EncryptionMethod
property to SSL.ValidateServerCertificate
property to true.HostNameInCertificate
property to a host name to be used to validate the certificate. The HostNameInCertificate
property provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.KeyPassword
property to the key password.
See SQL Escape Sequences for JDBC, for information about the SQL escape sequences supported by the Oracle driver.
The Oracle driver supports the Read Committed
and Serializable
isolation levels. The default is Read Committed
.
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. |
The Oracle driver provides two mechanisms for supporting batch operations:
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-compliant mechanism is used. The default value of the BatchPerformanceWorkaround
property is false.
The Oracle driver supports returning parameter metadata as described in this section.
The Oracle driver supports returning parameter metadata for the following forms of Insert and Update statements:
where operator
is any of the following SQL operators: =, <, >, <=, >=, and <>.
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:
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.
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 = ?"
When parameter metadata is requested for a column defined as NUMBER with no precision and scale argument, the driver returns a precision of 0 and a scale of 0 to indicate that the precision and scale of the column are unknown.
The Oracle driver does not support returning parameter metadata for stored procedure arguments.
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, EmployeeInfo 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.
For Oracle 8.1.6 and higher, the Oracle driver supports executing Insert, Update, and Delete statements with the RETURNING clause, which allows your application to return inserted, updated, or deleted values of a row into a variable and eliminate the need to execute additional statements to return this information.
The driver returns the values for each column named in the RETURNING clause as an output parameter. Your application must execute the Insert, Update, or Delete statement with the RETURNING clause using a CallableStatement object. In addition, your application must specify the data type of each returned value using the CallableStatement.registerOutParameter() method. The registered data type for a returned value must match the data type of the database column. For example, if the database column is defined with a JDBC type of CHAR, the data type of the returned value for that column must be registered as Types.CHAR.
The RETURNING clause can return a single row or multiple rows. The method your application uses to retrieve the values of returned columns depends on the number of rows the RETURNING clause returns as shown in the following examples.
CREATE TABLE employees (id int, name varchar(30))
You can use the following Insert statement with the RETURNING clause to return the updated ID for Smith:
String sql = "INSERT INTO employees VALUES(100, 'Smith')
RETURNING id INTO ?";
CallableStatement callStmt = con.prepareCall(sql);
callStmt.registerOutParameter(1, Types.INTEGER);
int updateCnt = callStmt.executeUpdate();
int newId = callStmt.getInt(1);
System.out.println("The id of the inserted row is: " + newId);
The database server returns a single result value for the requested column. An application can retrieve the result value using any of the following CallableStatement methods: getInt(), getString(), getObject(), and so on. The object type returned by getObject() is based on the data type specified in the registerOutParameter() call for the returned columns. Refer to the JDBC specification for details about JDBC data type to Java object mapping.
CREATE TABLE employees (id int, name varchar(30))
You can use the following Update statement with the RETURNING clause to return all rows with an updated ID value.
String sql = "UPDATE employees SET id = id + 1000" +
"RETURNING id INTO ?";
CallableStatement callStmt = con.prepareCall(sql);
callStmt.registerOutParameter(1, Types.INTEGER);
int updateCnt = callStmt.executeUpdate();
Integer[] newIds = (int []) callStmt.getArray(1).getArray();
for (int index = 0; index < newIds.length; index++) {
System.out.println("New Id value: " + newIds[index]);
}
The database server returns multiple result values for the requested column. An application can retrieve the result values using the CallableStatement.getArray() method.
Note: | If you use the CallableStatement.getxxx() methods to retrieve the result values, the driver only returns the first result value for the requested column. |
The data type of the returned array, and the data type of the array elements, match the data type specified in the registerOutParameter() call for the returned column. The elements of the array are an object type. For example, if the application registered the data type of the returned value as Types.INTEGER, the elements of the array are returned as Integer objects. The result set generated by the CallableStatement.getArray() method is a forward-only result set with a result set concurrency of read only. It contains a single column and has a row for each entry in the array.
The Oracle driver supports any JSR 114 implementation of the RowSet interface, including:
J2SE 1.4 or higher is required to use rowsets with the driver.
See http://www.jcp.org/en/jsr/detail?id=114 for more information about JSR 114.
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.
An application can return values of auto-generated keys when it executes an Insert statement. How you return these values depends on whether you are using an Insert statement that contains parameters:
Insert
statement that contains no parameters, the Oracle driver supports the following form of the Statement.execute()
and Statement.executeUpdate()
methods to instruct the driver to return values of auto-generated keys: Statement.execute (String
sql, int
autoGeneratedKeys)
Statement.execute(String
sql, int[]
columnIndexes
)
Statement.execute(String
sql, String[]
columnNames
Statement.executeUpdate (String
sql, int
autoGeneratedKeys)
columnNames
)Insert
statement that contains parameters, the Oracle driver supports the following form of the Connection.prepareStatement()
method to instruct the driver to return values of auto-generated keys:
An application can retrieve values of auto-generated keys using the Statement.getGeneratedKeys
method. This method returns a ResultSet object with a column for each auto-generated key.
The Oracle driver now supports server result set caching, a feature introduced in Oracle 11g that allows query results to be cached in memory. To specify that query results be cached in memory, use a result set cache hint in the query. For example:
SELECT /*+ result_cache */ * FROM employees
For more information about server result set caching, refer to your Oracle 11g documentation.
The Oracle driver supports the XQuery functions supported by Oracle 11g.
Refer to your Oracle documentation for more information about Oracle’s XQuery support.