Type 4 JDBC Drivers

      

The Oracle Driver (Deprecated)

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 Configure WebLogic JDBC.

The following sections describe how to configure and use the WebLogic Type 4 JDBC Oracle driver:

 


Oracle Database Version Support

The WebLogic Type 4 JDBC Oracle driver (the "Oracle driver") supports:

 


Oracle Driver Classes

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.

 


Oracle URL

The connection URL format for the Oracle driver is:

jdbc:bea:oracle://hostname:port[;property=value[;...]]

where:

For example:

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.

 


Oracle Connection Properties

Table 6-1 lists the JDBC connection properties supported by the Oracle driver, and describes each property. You can use these connection properties in a JDBC data source configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC data source configuration:

   property=value

All connection property names are case-insensitive. For example, Password is the same as password. Required properties are noted as such.

Table 6-1 Oracle Connection String Properties
Property Description
AuthenticationMethod {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. The User property provides the user ID. The Password property provides the password. See Authentication for more information. The default is auto.
BatchPerformanceWorkaround OPTIONAL {true | false}. Determines the method used to execute batch operations. If set to true, the native Oracle batch mechanism is used. The native Oracle batch mechanism does not return individual update counts for each statement or parameter set in the batch. For this reason, the driver returns a value of SUCCESS_NO_INFO (-2) for each entry in the returned update count array. If an application can accept not receiving update count information, setting this property to true can significantly improve performance. If set to false, the JDBC 3.0-compliant batch mechanism is used. The default is false. See Batch Inserts and Updates for details. See Performance Considerations for information about configuring this property for optimal performance.
CatalogIncludesSynonyms DEPRECATED 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.
CatalogOptions OPTIONAL {0 | 1 | 2 | 3}. Determines the type of information included in result sets returned from catalog functions. If set to 0, result sets contain neither synonyms or remarks. 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(). If set to 3, result sets contain remarks and synonyms (as described in options 1 and 2). The default is 2. See Performance Considerations for information about configuring this property for optimal performance.
CodePageOverride {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.
CodePageOverride (cont.) 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.
CommitBehavior {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:

  • Applications that perform update operations.

  • Applications 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 log changes to disk or fails during the process, but many logging applications for diagnostic purposes can.
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
CommitBehavior (cont.) 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.
ConnectionRetryCount OPTIONAL 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 set to 0, the driver does not try to reconnect after the initial unsuccessful attempt. 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 default is 5.
ConnectionRetryDelay OPTIONAL 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. The default is 1.
ConvertNull {1 | 0}. Controls how data conversions are handled for null values. 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. If set to 0, the driver does not perform the data type check if the value of the column is null. This allows null values to be returned even though a conversion between the requested type and the column type is undefined. The default is 1.
EnableCancelTimeout {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. If set to false (the default), the cancel request does not time out.
EncryptionMethod {noEncryption | SSL}. Determines whether SSL encryption is used to encrypt and decrypt data transmitted over the network between the driver and database server. If set to noEncryption (the default), data is not encrypted or decrypted. 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: HostNameInCertificate KeyStore (for SSL client authentication) KeyStorePassword (for SSL client authentication) KeyPassword (for SSL client authentication) TrustStore TrustStorePassword ValidateServerCertificate See Using tnsnames.ora Files for information about enabling SSL encryption using a tnsnames.ora file.

Connection hangs can occur if the driver attempts to connect to a database server that 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.

See Data Encryption for more information about configuring data encryption. See Performance Considerations for information about configuring this property for optimal performance. FetchTSWTZasTimestamp OPTIONAL {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. The default is false. See TIMESTAMP WITH TIME ZONE Data Type for more information. HostNameInCertificate {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. If SSL encryption or certificate validation is not enabled, this property is ignored. See Data Encryption for information about configuring for authentication. The default is an empty string. InitializationString 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: InitializationString=command Multiple commands must be separated by semicolons. In addition, if this property is specified in a connection URL, the entire value must be enclosed in parentheses when multiple commands are specified. For example: jdbc:bea:oracle://server1:1521;
ServiceName=ORCL;InitializationString=(command1;
command2) If the execution of a SQL command fails, the connection attempt also fails and the driver throws an exception indicating which SQL command or commands failed. InsensitiveResultSetBufferSize OPTIONAL {-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. The default is 2048 (KB). See Performance Considerations for information about configuring this property for optimal performance. JavaDoubleToString {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. The default is false. KeyStore 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. Override the directory of the keystore file specified by the javax.net.ssl.keyStore Java system property. If this property is not specified, the keystore directory is specified by the javax.net.ssl.keyStore Java system property. NOTE: The keystore and truststore files can be the same file. KeyStorePassword 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. Override the password of the keystore file specified by the javax.net.ssl.keyStorePassword Java system property. If this property is not specified, the keystore password is specified by the javax.net.ssl.keyStorePassword Java system property. NOTE: The keystore and truststore files can be the same file. KeyPassword 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. LoginTimeout OPTIONAL The amount of time, in seconds, the driver waits for a connection to be established before returning control to the application and throwing a timeout exception. If set to 0 (the default), the driver does not time out a connection request. Password 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. PortNumber OPTIONAL 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. This property is supported only for data source connections. 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. QueryTimeout {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. If set to -1, the query timeout functionality is disabled. The driver silently ignores calls to the Statement.setQueryTimeout() method. If set to 0 (the default), the default query timeout is infinite (the query does not time out). ResultSetMetaDataOptions {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. The default is 0. SendFloatParametersAsString {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. The default is false. ServerName OPTIONAL 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. If using a tnsnames.ora file to provide connection information, do not specify this property. This property is supported only for data source connections. See Performance Considerations for information about specifying a server name using a tnsnames.ora file. ServerType OPTIONAL {Shared | Dedicated}. Whether the connection is established using a shared or dedicated server process (UNIX) or thread (Windows). If set to Shared, the server process to be used is retrieved from a pool. The socket connection between the client and server is made to a dispatcher process on the server. This setting allows there to be fewer processes than the number of connections, reducing the need for server resources. Use this value when a server must handle many users with fewer server resources. If set to Dedicated, a server process is created to service only that connection. When that connection ends, so does the process (UNIX) or thread (Windows). The socket connection is made directly between the application and the dedicated server process or thread. When connecting to UNIX servers, a dedicated server process can provide significant performance improvement, but uses more resources on the server. When connecting to Windows servers, the server resource penalty is insignificant. Use this value if you have a batch environment with low numbers of users. If unspecified, the driver uses the server type set on the server. If using a tnsnames.ora file to provide connection information, do not specify this property. See Using tnsnames.ora Files for information about specifying the server type using a tnsnames.ora file. See Performance Considerations for information about specifying the server type using a tnsnames.ora file. ServiceName OPTIONAL 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: 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. See Performance Considerations for information about specifying the database service name using a tnsnames.ora file. SID OPTIONAL The Oracle System Identifier that refers to the instance of the Oracle database running on the server. This property is mutually exclusive with the ServiceName property. The default is ORCL, which is the default SID that is configured when installing your Oracle database. If using a tnsnames.ora file to provide connection information, do not specify this property. See Performance Considerations for information about specifying an Oracle SID using a tnsnames.ora file. SysLoginRole {sysdba | sysoper}. 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. If set to sysdba, the user is logged on the database with the Oracle system privilege SYSDBA. If set to sysoper, the user is logged on the database with the Oracle system privilege SYSOPER. 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. If this property is set to an empty string or is not specified, the user is logged in without SYSDBA or SYSOPER privileges. The default is an empty string. SupportLinks {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. If set to true, the driver supports Oracle linked servers. When Oracle linked servers are supported, the driver does not support distributed transactions. If set to false (the default), the driver does not support Oracle linked servers. In addition, the driver supports distributed transactions. In most cases, setting this property to false provides the best performance. TNSNamesFile OPTIONAL 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: PortNumber ServerName ServerType ServiceName SID 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. 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: PortNumber ServerName ServerType ServiceName SID 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. TrustStore 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. Override the directory of the truststore file specified by the javax.net.ssl.trustStore Java system property. If this property is not specified, the truststore directory is specified by the javax.net.ssl.trustStore Java system property. This property is ignored if ValidateServerCertificate=false. TrustStorePassword 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. Override the password of the truststore file specified by the javax.net.ssl.trustStorePassword Java system property. If this property is not specified, the truststore password is specified by the javax.net.ssl.trustStorePassword Java system property. This property is ignored if ValidateServerCertificate=false. User 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. ValidateServerCertificate {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. The default is true. WireProtocolMode {1 | 2}. 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. If set to 2, the driver optimizes network traffic to the Oracle server for: 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. Inserts and updates of Blob and Clob data. See Performance Considerations for information about configuring this property for optimal performance. The default is1.

 


Performance Considerations

Setting the following connection properties for the Oracle driver as described in the following list can improve performance for your applications:

 

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().

 

CommitBehavior

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.

 

EncryptionMethod

Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data.

 

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.

 

ResultSetMetaDataOptions

By default, the Oracle driver skips the additional processing required to return the correct table name for each column in the result set when the ResultSetMetaData.getTableName() method is called. Because of this, the getTableName() method may return an empty string for each column in the result set. If you know that your application does not require table name information, this setting provides the best performance.

See ResultSet MetaData Support for more information about returning ResultSet metadata.

 

ServerType

When using a dedicated server connection, a server process on UNIX (a thread on Windows) is created to serve only your application connection. When you disconnect, the process goes away. The socket connection is made directly between your application and this dedicated server process. This can provide considerable performance improvements, but will use significantly more resources on UNIX servers. Because this is a thread on Oracle servers running on Windows platforms, the additional resource usage on the server is significantly less. The ServerType property should be set to dedicated when you have a batch environment with lower numbers of connections, your Oracle server has excess processing capacity and memory available when at maximum load, or if you have a performance-sensitive application that would be degraded by sharing Oracle resources with other applications.

 

WireProtocolMode

Set this property to 2 if:

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.

If your application:

then this property should be set to 1; otherwise, performance may be degraded.

 


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:

In a tnsnames.ora file, connection information for an Oracle service is associated with an alias, or Oracle net service name. Each net service name entry contains connect descriptors that define listener and service information. The following example in Listing 6-1 shows connection information in a tnsnames.ora file configured for the net service name entries, FITZGERALD.SALES and ARMSTRONG.ACCT. Listing 6-1 tnsnames.ora Example

FITZGERALD.SALES =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
(CONNECT_DATA =
(SID = ORCL)
)
)
ARMSTRONG.ACCT =
(DESCRIPTION =
(ADDRESS_LIST=
(FAILOVER = on)
(LOAD_BALANCE = on)
(ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
(ADDRESS= (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
(ADDRESS= (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))
)
(CONNECT_DATA=
(SERVICE_NAME = acct.us.yourcompany.com)
)
)

Using this example, if the Oracle driver referenced the Oracle net service name entry FITGERALD.SALES, the driver would connect to the Oracle database instance identified by the Oracle SID ORCL (SID=ORCL). Similarly, if the Oracle driver referenced ARMSTRONG.ACCT, the driver would connect to the Oracle database identified by the service name acct.us.yourcompany.com (SERVICE_NAME=acct.us.yourcompany.com). In addition, the driver would enable connection failover (FAILOVER=on) and client load balancing (LOAD_BALANCE=on).

Typically, a tnsnames.ora file is installed when you install an Oracle database. By default, the tnsnames.ora file is located in the ORACLE_HOME\network\admin directory on Windows and the $ORACLE_HOME/network/admin directory on UNIX.

 

Connecting to the Database

To retrieve connection information from an Oracle tnsnames.ora file with the Oracle driver, 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:

If using tnsnames.ora files with a Security Manager on a Java 2 Platform, read permission must be granted to the tnsnames.ora file. See Granting Access to Oracle tnsnames.ora Files for an example.

 

Configuring the tnsnames.ora File

If using a tnsnames.ora file to retrieve connection information, do not specify the following connection properties to prevent connection information conflicts:

If any of these properties are specified in addition to the TNSNamesFile and TNSServerName properties, the driver 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.

Table 6-2 Oracle Driver Property Mappings to tnsnames.ora Connect Descriptor Parameters
Oracle Driver Property tnsnames.ora Attribute
PortNumber = port PORT = port 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.
EncryptionMethod={noEncryption | SSL} PROTOCOL={TCP | TCPS} The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The PROTOCOL parameter is used within the ADDRESS parameter to specify the network protocol to be used. It also is used to specify whether data is encrypted and decrypted when transmitted over the network between the driver and the server. For example, the following entry specifies that the TCP/IP protocol will be used with no encryption: (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. The following entry specifies that the TCP/IP protocol will be used with SSL encryption: (ADDRESS_LIST=
(ADDRESS= (PROTOCOL = TCPS)(HOST = server1)
(PORT = 2484))
...
) A port of 2484, the port number recommended by Oracle for SSL, is specified for server1. NOTE: Truststore information must still be specified using either the TrustStore and TrustStorePassword properties or Java system properties. Optionally, you can specify the ValidateServerCertificate and HostNameInCertificate properties.
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.
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.

 


Data Types

Table 6-3 lists the data types supported by the Oracle driver and describes how they are mapped to the JDBC data types.

Table 6-3 Oracle Data Types
Oracle Data Type JDBC Data Type
BFILE BLOB
BINARY_DOUBLE1 DOUBLE
BINARY_FLOAT1 REAL
BLOB BLOB
CHAR CHAR
CLOB CLOB
DATE TIMESTAMP
FLOAT(n) DOUBLE
LONG LONGVARCHAR
LONG RAW LONGVARBINARY
NCHAR CHAR
NCLOB CLOB
NUMBER DECIMAL
NUMBER (p, s) DECIMAL
NVARCHAR2 VARCHAR
RAW VARBINARY
TIMESTAMP2 TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE2 TIMESTAMP
TIMESTAMP WITH TIME ZONE2 TIMESTAMP
UROWID2 VARCHAR
VARCHAR22 VARCHAR
XMLType2 CLOB

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.

 

Using 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 JVM.
NLS_TIMESTAMP_FORMAT The default timestamp format. The Oracle driver uses the JDBC timestamp escape format: YYYY-MM_DD HH24:MI:SS.FF
NLS_TIMESTAMP_TZ_FORMAT The default timestamp with time zone format. The Oracle driver uses the JDBC timestamp escape format with the time zone field appended: YYYY-MM_DD HH24:MI:SS.FF TZH:TZM

TIMESTAMP Data Type

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

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

'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'

where:

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:

 


Returning and Inserting/Updating XML Data

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.

 

Returning XML Data

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.

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.

 

Inserting/Updating XML Data

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 Data Type

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

Example 2: Calling a Stored Procedure that Returns Multiple REF CURSORs

// Call the stored procedure that accepts an input parameter 

// and returns two REF CURSORs. Omit the placeholder for
// REF CURSOR parameters. The REF CURSORs are returned as
// result sets.
sql = "{call foo_pkg.selectEmployeeManager(?)}";

callStmt = con.prepareCall(sql);
callStmt.setInt(1, 2);
moreResults = callStmt.execute();

while (true) {

if (moreResults) {

// Get the result set that represents the REF CURSOR
resultSet = callStmt.getResultSet();
displayResults(resultSet);
resultSet.close();
}
else {

updateCnt = callStmt.getUpdateCount();
if (updateCnt == -1) {
break;
}
}

moreResults = callStmt.getMoreResults();
}

 


Authentication

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:

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.

 

Using the AuthenticationMethod 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:

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.

 

Configuring User ID/Password Authentication

  1. Set the AuthenticationMethod property to auto or userIdPassword. See Using the AuthenticationMethod Property for more information about setting a value for this property.

  2. Set the User property to provide the user ID.

  3. Set the Password property to provide the password.

 

Configuring Kerberos Authentication

This section provides requirements and instructions for configuring Kerberos authentication for the Oracle driver.

Product Requirements

Verify that your environment meets the requirements listed in Table 6-4 before you configure the driver for Kerberos authentication.

Table 6-4 Kerberos Authentication Requirements for the Oracle Driver
Component Requirements
Database server The database server must be administered by the same domain controller that administers the client and must be running one of the following databases:

  • Oracle 10g (R1 and R2)

  • Oracle 9i (R2)
Kerberos server The Kerberos server is the machine where the user IDs for authentication are administered. The Kerberos server is also the location of the Kerberos KDC. Network authentication must be provided by one of the following methods:

  • Windows Active Directory on one of the following operating systems:

    • Windows Server 2003

    • Windows 2000 Server Service Pack 3 or higher

  • MIT Kerberos 1.4.2 or higher
Client The client must be administered by the same domain controller that administers the database server. In addition, J2SE 1.4.2 or higher must be installed.

Configuring the Driver

During installation, WebLogic Server installs the following files required for Kerberos authentication in the server/lib subdirectory of your WebLogic Server installation directory:

To configure the driver:

  1. Set the driver's AuthenticationMethod property to auto (the default) or kerberos. See Using the AuthenticationMethod Property for more information about setting a value for this property.

  2. Modify the krb5.conf file to contain your Kerberos realm name and the KDC name for that Kerberos realm by editing the file with a text editor or by specifying the system properties, java.security.krb5.realm and java.security.krb5.kdc.

    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.

  1. If using Kerberos authentication with a Security Manager on a Java 2 Platform, grant security permissions to the application and driver. See Permissions for Kerberos Authentication for an example.

 

Specifying User Credentials for Kerberos Authentication (Delegation of Credentials)

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

 

Obtaining a Kerberos Ticket Granting Ticket

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.

 

Configuring NTLM Authentication

This section provides requirements and instructions for configuring NTLM authentication for the Oracle driver.

Product Requirements

Verify that your environment meets the requirements listed in Table 6-5 before you configure the driver for NTLM authentication.

Table 6-5 NTLM Authentication Requirements for the Oracle Driver
Component Requirements
Database server The database server must be administered by the same domain controller that administers the client and must be running one of the following databases:

  • Oracle 10g (R1 and R2)

  • Oracle 9i (R1 and R2)
Domain controller The domain controller must administer both the database server and the client. Network authentication must be provided by NTLM on one of the following operating systems:

  • Windows Server 2003

  • Windows 2000 Server Service Pack 3 or higher
Client The client must be administered by the same domain controller that administers the database server and must be running on one of the following operating systems:

  • Windows Vista

  • Windows Server 2003

  • Windows XP Service Pack 1 or higher

  • Windows 2000 Service Pack 4 or higher

  • Windows NT 4.0
In addition, J2SE 1.3 or higher must be installed.

Configuring the Driver

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.

To configure the driver:

  1. Set the AuthenticationMethod property to auto or ntlm. SeeUsing the AuthenticationMethod Property for more information about setting a value for this property.

  2. By default, the driver looks for the NTLM authentication DLLs in a directory on the Windows system path defined by the PATH environment variable. If you install the driver in a directory that is not on the Windows system path, perform one of the following actions to ensure the driver can load the DLLs:

    • Add the WL_HOME/server/lib directory to the Windows system path, where WL_HOME is the directory in which you installed WebLogic Server.

    • Copy the NTLM authentication DLLs from 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.

    • Set the 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

  3. If using NTLM authentication with a Security Manager on a Java 2 Platform, security permissions must be granted to allow the driver to establish connections. See Permissions for Establishing Connections for an example.

 

Configuring Client Authentication

Set the AuthenticationMethod property to client. See Using the AuthenticationMethod Property for more information about setting a value for this property.

 


Data Encryption

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.

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.

To configure SSL encryption:

  1. Set the EncryptionMethod property to SSL.

  2. Specify the location and password of the truststore file used for SSL server authentication. Either set the TrustStore and TrustStore properties or their corresponding Java system properties (javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword, respectively).

  3. To validate certificates sent by the database server, set the ValidateServerCertificate property to true.

  4. Optionally, set the 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.

  5. If your database server is configured for SSL client authentication, configure your keystore information:

    1. Specify the location and password of the keystore file. Either set the KeyStore and KeyStore properties or their corresponding Java system properties (javax.net.ssl.keyStore and javax.net.ssl.keyStorePassword, respectively).

    2. If any key entry in the keystore file is password-protected, set the KeyPassword property to the key password.

 


SQL Escape Sequences

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

 


Isolation Levels

The Oracle driver supports the Read Committed and Serializable isolation levels. The default is Read Committed.

 


Using Scrollable Cursors

The Oracle driver supports scroll-sensitive result sets, scroll-insensitive result sets, and updatable result sets.

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

 


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:

where operator is any of the following SQL operators: =, <, >, <=, >=, and <>.

 

Select Statements

The Oracle driver supports returning parameter metadata for Select statements that contain parameters in ANSI SQL 92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTS predicate constructs. Refer to the ANSI SQL reference for detailed syntax.

Parameter metadata can be returned for a Select statement if one of the following conditions is true:

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

 

Stored Procedures

The Oracle driver does not support returning parameter metadata for stored procedure arguments.

 


ResultSet MetaData Support

If your application requires table name information, the Oracle driver can return table name information in ResultSet metadata for Select statements. By setting the ResultSetMetaDataOptions property to 1, the Oracle driver performs additional processing to determine the correct table name for each column in the result set when the ResultSetMetaData.getTableName() method is called. Otherwise, the getTableName() method may return an empty string for each column in the result set.

When the ResultSetMetaDataOptions property is set to 1 and the ResultSetMetaData.getTableName() method is called, the table name information that is returned by the Oracle driver depends on whether the column in a result set maps to a column in a table in the database. For each column in a result set that maps to a column in a table in the database, the Oracle driver returns the table name associated with that column. For columns in a result set that do not map to a column in a table (for example, aggregates and literals), the Oracle driver returns an empty string.

The Select statements for which ResultSet metadata is returned may contain aliases, joins, and fully qualified names. The following queries are examples of Select statements for which the ResultSetMetaData.getTableName() method returns the correct table name for columns in the Select list:

   SELECT id, name FROM Employee
   SELECT E.id, E.name FROM Employee E 
   SELECT E.id, E.name AS EmployeeName FROM Employee E
   SELECT E.id, E.name, I.location, I.phone FROM Employee E, 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.

 


Executing Insert/Update/Delete Statements with a RETURNING Clause

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.

Example A: Retrieving a Result Value From an Insert/Update/Delete of a Single Row

Given the table defined by:

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.

Example B: Retrieving Result Values From an Insert/Update/Delete of Multiple Rows

Given the table defined by:

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.

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.

 


Rowset Support

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.

 


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.

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:

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.

 


Server Result Set Caching

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.

 


XQuery Support

The Oracle driver supports the XQuery functions supported by Oracle 11g.

Refer to your Oracle documentation for more information about Oracle's XQuery support.