Oracle

Connection Dialog

To connect to an Oracle database, required connection parameters must be set. There are two connection modes for Oracle: Direct and using Oracle Client.

Direct Mode

The Direct mode allows you to connect to Oracle database without Oracle Client software. It means thet you may have no Oracle Client software installed on your PC and connect to Oracle directly via TCP\IP (or SSL or SSH). To enable this connection mode, select the Direct check box.

In the Direct mode, the following connection options are required:

Server - IP address or DNS name of the Oracle server to which to connect. You can also enable secure SSL and SSH protocols here by adding "tcps://" or "ssh://" protocol prefixes respectively to the Host parameter value. (You will also need to set up parameters for the corresponding protocol in the Advanced connection options).

2.	SID - Unique name for an Oracle database instance;

3.	Port - Number of a port to communicate with listener on the server. The default value is 1521;

4.	User Id - The Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication);

5.	Password - The password for the Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication);

6.	Connect mode - Allows to open a session with administrative privileges.

You can

Oracle Client Mode

To use Oracle Client for connection to an Oracle database, you should have Oracle Client software installed on your PC. Clear the Direct check box to work with Oracle Client.

In this mode the SID and Port settings are not used, and you need to set the Oracle Home to use instead. Besides, in the Client mode, the Host parameter must specify the name of TNS alias of Oracle database to which to connect instead of the IP address or DNS name of the server. Specify the Oracle Client you want to be used in the Home connection option.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option

Description

ClientId

Specifies the client identifier for the connection.

Connect Mode

Allows to open a session with administrative privileges SYSDBA or SYSOPER.

Connection Class

Specifies the connection class for Oracle's Database Resident Connection Pooling (DRCP). You need to also set OCI Session Pooling = true for using DRCP. DRCP is supported starting with Oracle 11g.

Connection Lifetime

Compares the connection's creation time with the current time when it is returned to the pool, and destroys the connection if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0.

Connection Timeout

Specifies the time (in seconds) to wait while trying to establish a connection before terminating the attempt and generating an error. A value of 0 indicates no limit. The default value is 15 seconds.

Default Command Timeout

Specifies the time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Default Fetch Size

Specifies the default number of rows to fetch from the server in a single operation.

Describe Stored Procedures

Is ignored. Do not use this parameter.

Direct

Specifies whether Devart SSIS Data Flow Components can operate without an Oracle Client installed. The default value is false. Note that parameters, required for Direct and Client connection modes, are different. See the Connection Dialog section above.

Enlist

Determines whether the connection is automatically enlisted in the current distributed transaction. The default value is true.

HA Events

Enables Oracle HA event notifications, including node status changes and load balancing advisories, so invalid connections can be removed from the pool immediately.

Home

Specifies the Oracle Client that will be used.

Initialization Command

Specifies a database-specific command to execute immediately after establishing the connection.

Lob Block Size

Specifies the size of a block (in megabytes) to read/write CLOB or NCLOB data from/to database. This option is supported in OCI mode only. Applicable only for Unicode Oracle servers. The default value is 0, indicating that the whole value is sent in one block. If you get errors when reading/writing huge LOB values, try setting this property to 8, it is the safest value.

Max Pool Size

Specifies the maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

Specifies the minimum number of connections allowed in the pool. The default value is 0.

Number Mappings

Sets the number mappings used for this connection. Each mapping is set as (OracleNumberType, FromPrecision, ToPrecision, SystemType), where:

- OracleNumberType is one of the values from OracleNumberType enumeration;

- FromPrecision and ToPrecision set the minimal and maximal precisions of the Oracle number type to map with this mapping;

- SystemType is a .NET type to which Oracle number type should be mapped.

Several mappings should be separated by commas and covered by a common parentheses. For example, this property may be set as:

Number Mappings = (Integer, 1, 1, System.Boolean)

Number Mappings = ((Integer, 1, 5, System.Int16), (FLOAT,12,15,System.Decimal), (NUMBER,7,9,System.Single))

Oci Session Pooling

Enables the OCI Session Pooling feature if set to true.

Oci Session Pool Allow Waiting

Determines whether new connections wait for an existing one to close when Max Size is reached, or an exception is thrown otherwise.

Oci Session Pool Connection Lifetime

Specifies the lifetime of the connection in seconds. Before the connection is placed back into the pool, the lifetime of the connection is checked. If the lifetime of the connection exceeds this property value, the connection is closed and disposed. If this property value is 0, the connection lifetime is never checked.

Oci Session Pool Increment

Allows applications to set the next increment for sessions to be started if the current number of sessions is less than Max Size. The valid values are 1 and above.

Oci Session Pool Max Size

Specifies the maximum number of sessions that can be opened in the session pool. Once this value is reached, no more sessions are opened. The valid values are 1 and above.

Oci Session Pool Min Size

Specifies the minimum number of sessions in the session pool. This number of sessions are started initially. After this, sessions are opened only when necessary.

Oci Session Pool Password

Defines the password for proxy user, if set.

Oci Session Pool User Id

Defines the user name for proxy user, if set. Available only when OCI Session Pooling is enabled.

OraMts

Uses Oracle Services for Microsoft Transaction Server (OraMTS) for distributed transactions when set to true; otherwise, OraMTS is not used.

Pass Parameters By Name

Pass parameters by name to the stored procedure calls. The default value is false.

Password

Specifies the password for the Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication)

Pooling

Draws the connection from the appropriate pool, or creates one and adds it to the appropriate pool, when set to true.

Port

Specifies the number of a port to communicate with listener on the server to use in the Direct mode. The default value is 1521.

Proxy Password

Specifies the password for Oracle proxy authentication.

Proxy User Id

Specifies the proxy user id for Oracle proxy authentication

Run Once Command

Specifies a SQL statement that must be executed immediately after establishing the connection. Unlike Initialization Command, it is not executed for connections obtained from the connection pool.

Server

Specifies, in the Direct mode (Direct is true), the IP address or DNS name of the Oracle server to which to connect. You can also specify a TNS descriptor here.

Specifies, in the Client mode (Direct is false), the name of TNS alias of Oracle database to which to connect.

Service Name

Specifies an alias to an Oracle database instance (or many instances) to use in the Direct mode. The default value is empty string. When Service Name is specified and implies several database instances, add-in connects to the first instance provided by Oracle listener. Note that RAC is not supported in the Direct mode, and provider works only with this one database instance.

SID

Specifies the unique name for an Oracle database instance to use in the Direct mode. The default value is empty string.

SSH Authentication Type

Specifies the client authentication method(s). Can include the following values:

•	PublicKey - Public-key authentication will be used.

•	Password - Password will be used for authentication.

•	KeyboardInteractive - Keyboard-interactive authentication will be used.

SSH Cipher List

Specifies the list of ciphers that client agrees to use. The following ciphers are available for SSH connections:

•

3DES

•

Blowfish

•

AES(128)

You can use ALL keyword to indicate whole set of ciphers. To exclude certain cipher from the set use "-" sign. For instance, "ALL-Blowfish" means that any cipher but Blowfish can be used.

SSH Host

Specifies the name or ip address of SSH server.

SSH Host Key

Specifies the location of the public key on the client side to verify the server host key during establishing connection.

SSH KeyExchange Algorithms

Specifies the algorithms to securely establish a shared encryption key between the client and the server.

SSH MAC Algorithms

Specifies the algorithms to verify the integrity and authenticity of data exchanged between the client and the server.

SSH Passphrase

Specifies the client key passphrase.

SSH Password

Specifies the user password on SSH server.

SSH Port

Specifies the number of the port on the SSH server to connect.

SSH Private Key

Specifies the location of the client private key.

SSH Strict Host Key Checking

Determines whether the host key is verified during establishing connection. The default value is false.

When this option is set to true, the server public key is verified. The verification is successfull if the server key is identical to the key, supplied by user. This key should be specified in the SSH Host Key parameter. If this property is not set or the keys are not equal, the connection is not established. You can get the public host key from system administrator.

SSH User

Specifies the user id on SSH server.

SSL Cert

Specifies the SSL Certificate contents as a base64 encoded string.

SSL Key

Specifies the SSL Key contents as a base64 encoded string.

SSL ServerCertDN

Specifies a set of parameters for Oracle server certificate check, for example "SslServerCertDN=\"C=UA,O=Devart,OU=DevartSSL,CN=TestSSL\"".

SSL WalletPath

Specifies the location of the Oracle Wallet to get a certificate for connecting to Oracle in the Direct mode.

Statement Cache Purge

Determines whether to clear the OCI statement cache when closing the connection or putting it to the pool. The default value is false.

Statement Cache Size

Enables or disables statement caching. Its value specifies the maximum number of statements that can be cached for a connection. By default this attribute is set to 0 (disabled). Statement caching starts if this parameter is set to a value more than 0. It should not be more than MAX_OPEN_CURSORS parameter in an Oracle database.

Transaction Scope Local

Uses only one connection internally when there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope. The default value is false.

Trim Fixed Char

Specifies whether to trim trailing spaces when reading data from fixed-length string data types (CHAR, NCHAR). It affects all command objects of this connection. Behavior of data reader depends on Trim Fixed Char value at the moment of opening data reader. The default value is true.

Unicode

Specifies whether the add-in uses UTF16 mode API calls. The default value is false.

Use Performance Monitor

Enables performance counters that allows monitoring provider activity, such as the number of active and pooled connections, using Windows Performance Monitor. The default value is false.

User ID

Specifies the Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication).

Validate Connection

Specifies whether to validate connections that are being got from the pool.