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

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

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0.

Connection Timeout

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

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

Describe Stored Procedures

This parameter is ignored. Do not use this parameter.

Direct

If true, 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. Please see the Connection Dialog section above.

Enlist

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

Home

The Oracle Client that will be used.

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. Default value is 0, which means 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

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

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

If true, enables the OCI Session Pooling feature.

Oci Session Pool Allow Waiting

If true, new connections wait for an existing one to close if Max Size is reached; otherwise an exception is thrown.

Oci Session Pool Connection Lifetime

This attribute 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

If set, defines password for proxy user.

Oci Session Pool User Id

If set, defines user name for proxy user. Available only when OCI Session Pooling is enabled.

OraMts

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

Pass Parameters By Name

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

Password

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

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Port

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

Proxy Password

The password for Oracle proxy authentication.

Proxy User Id

The proxy user id for Oracle proxy authentication

Server

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.

In the Client mode (Direct is false): the name of TNS alias of Oracle database to which to connect.

Service Name

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

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

SSH Authentication Type

Client authentication methods. 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

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

The name or ip address of SSH server.

SSH Host Key

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

SSH Passphrase

The client key passphrase.

SSH Password

User password on SSH server.

SSH Port

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

SSH Private Key

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

User id on SSH server.

SSL Cert

The SSL Certificate contents as a base64 encoded string.

SSL Key

The SSL Key contents as a base64 encoded string.

SSL ServerCertDN

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

SSL WalletPath

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

This attribute 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

If there are several connections with the same connection string (which includes "Transaction Scope Local=true;") within a scope of TransactionScope, our provider will use only one connection internally. 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.

User ID

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.