Connection strings for Oracle

Article: "Oracle Data Provider for .NET" by Rama Mohan

Port 1521 is used if no port number is specified in the connection string.

Make sure that EZCONNECT is enabled in the sqlnet.ora file. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

'//' in data source is optional and is there to enable URL style hostname values

Other server options: SHARED, POOLED (to use instead of DEDICATED). Dedicated is the default.

The first connection opened creates the connection pool. The service initially creates the number of connections defined by the Min Pool Size parameter.

The Incr Pool Size attribute defines the number of new connections to be created by the connection pooling service when more connections are needed.

When a connection is closed, the connection pooling service determines whether the connection lifetime has exceeded the value of the Connection Lifetime attribute. If so, the connection is closed; otherwise, the connection goes back to the connection pool.

The connection pooling service closes unused connections every 3 minutes. The Decr Pool Size attribute specifies the maximum number of connections that can be closed every 3 minutes.

The Max Pool Size attribute sets the maximum number of connections for the connection pool. If a new connection is requested, but no connections are available and the limit for Max Pool Size has been reached the connection pooling service waits for the time defined by the Connection Timeout attribute. If the Connection Timeout time has been reached, and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the request has timed-out.

If the Password attribute is provided, it is ignored.

Operating System Authentication is not supported in a .NET stored procedure.

SYSOPER is also valid for the DBA Privilege attribute.

This feature can only be used against a RAC database and only if pooling is enabled (default).

This one works only with Oracle 8i release 3 or later

This one works only with Oracle 8i release 3 or later

The first connection opened creates the connection pool. The service initially creates the number of connections defined by the Min Pool Size parameter.

The Incr Pool Size attribute defines the number of new connections to be created by the connection pooling service when more connections are needed.

When a connection is closed, the connection pooling service determines whether the connection lifetime has exceeded the value of the Connection Lifetime attribute. If so, the connection is closed; otherwise, the connection goes back to the connection pool.

The connection pooling service closes unused connections every 3 minutes. The Decr Pool Size attribute specifies the maximum number of connections that can be closed every 3 minutes.

Memory is the default value. All the rowset data is stored in-memory which provides better performance at the expense of higher memory utilization.

File = All the rowset data is stored on disk. This caching mechanism limits the memory consumption at the expense of performance.

The FetchSize value must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.

Providing a high value for this attribute improves performance, but requires more memory to store the data in the rowset. Valid values are 1 to 65535. The default is 100.

The OLEDB.NET connection string attribute must not be used in ADO applications.

Using ADO, these properties would have been set as a property on the command. This is not possible if using the Microsofts OLE DB .NET Data Provider. So the properties are specified in the connection string instead.

PLSQLRSet: If the stored procedure, provided by the consumer, returns a rowset, PLSQLRSet must be set to TRUE (enabled).

NDatatype: This property allows the consumers to specify whether any of the parameters bound to the command are of Oracle's N datatypes (NCHAR, NVARCHAR or NCLOB). This information is required by OraOLEDB to detect and bind the parameters appropriately. This property should not be set for commands executing SELECT statements. However, this property must be set for all other SQLs such as INSERT, UPDATE, and DELETE.

SPPrmsLOB: This property allows the consumer to specify whether one or more of the parameters bound to the stored procedures are of Oracle's LOB datatype (CLOB, BLOB, or NCLOB). OraOLEDB requires this property to be set to TRUE, in order to fetch the parameter list of the stored procedure prior to execution. The use of this property limits the processing overhead to stored procedures having one or more LOB datatype parameters.

Valid values are 0 (disabled) and 1 (enabled).

See the respective OLEDB provider's connection strings options. The .net OleDbConnection will just pass on the connection string to the specified OLEDB provider. Read more here.

Note! The keyword "OLEDB.NET" must be set to "True" for the OraOLEDB.Oracle provider to function with the .NET Framework Data Provider for OLE DB.

See the respective ODBC driver's connection strings options. The .net OdbcConnection will just pass on the connection string to the specified ODBC driver. Read more here.