Connecting to the Oracle Database

This section describes OracleConnection provider-specific features, including:

Connection String Attributes

Table 3-1 lists the supported connection string attributes.

Table 3-1 Supported Connection String Attributes

Connection String Attribute	Default value	Description
`Connection Lifetime`	`0`	Maximum life time (in seconds) of the connection
`Connection Timeout`	`15`	Maximum time (in seconds) to wait for a free connection from the pool
`Data Source`	empty string	Oracle Net Service Name that identifies the database to connect to
`DBA Privilege`	empty string	Administrative privileges: `SYSDBA` or `SYSOPER`
`Decr Pool Size`	1	Controls the number of connections that are closed when an excessive amount of established connections are unused
`Enlist`	`true`	Enables or disables serviced components to automatically enlist in distributed transactions
`Incr Pool Size`	5	Controls the number of connections that are established when all the connections in the pool are used
`Max Pool Size`	`100`	Maximum number of connections in a pool
`Min` `Pool` `Size`	`1`	Minimum number of connections in a pool
`Password`	empty string	Password for the user specified by `User` `Id`
`Persist` `Security` `Info`	`false`	Enables or disables the retrieval of password in the connection string
`Pooling`	`true`	Enables or disables connection pooling
`Proxy` `User` `Id`	empty string	User name of the proxy user
`Proxy` `Password`	empty string	Password of the proxy user
`User` `Id`	empty string	Oracle user name

The following example uses connection string attributes to connect to an Oracle Database:

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
con.Open();
...

See Also:
"OracleConnection Properties" for detailed information on connection attributes

Connection Pooling

ODP.NET connection pooling is enabled and disabled using the Pooling connection string attribute. By default, connection pooling is enabled. The following are ConnectionString attributes that control the behavior of the connection pooling service:

Pooling
Connection Lifetime
Connection Timeout
Max Pool Size
Min Pool Size
Incr Pool Size
Decr Pool Size

Connection Pooling Example
The following code opens a connection using ConnectionString attributes related to connection pooling.

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;" +
         "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
         "Incr Pool Size=5; Decr Pool Size=2";
con.Open();
...

With connection pooling enabled (the default), the Open and Close methods of the OracleConnection object implicitly use the connection pooling service. In the preceding code, the Open call uses the connection pooling service, which is responsible for returning a connection to the application.
Connection pools are created by the connection pooling service using the ConnectionString as a signature to uniquely identify a pool.
If no pool with the exact attribute values in the ConnectionString exists, the connection pooling service creates a new connection pool. If a pool already exists with the requested signature, a connection is returned to the application from that pool.
When a connection pool is created, the connection-pooling service initially creates the number of connections defined by the Min Pool Size attribute of the ConnectionString. This number of connections is always maintained by the connection pooling service for the connection pool.
At any given time, these connections are available in the pool or used by the application.
The Incr Pool Size attribute of the ConnectionString defines the number of new connections to be created by the connection pooling service when more connections are needed in the connection pool.
When the application closes a connection, the connection pooling service determines whether the connection lifetime has exceeded the Connection Lifetime attribute; if so, the connection pooling service closes the connection; otherwise, the connection goes back to the connection pool. The connection pooling service only enforces the Connection Lifetime when a connection is going back to the connection pool.
The Max Pool Size attribute of the ConnectionString sets the maximum number of connections for a connection pool. If a new connection is requested, no connections are available, and Max Pool Size has been reached, then the connection pooling service waits for the time defined by Connection Timeout. If the Connection Timeout has been reached and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the pooled connection request has timed-out.
The connection pooling service closes connections when they are not used; connections are closed every three minutes. The Decr Pool Size attribute of the ConnectionString provides connection pooling service for the maximum number of connections that can be closed in one run.

Operating System Authentication

The Oracle Database can use Windows user login credentials to authenticate database users. To open a connection using Windows user login credentials, the User Id ConnectionString attribute must be set to /. IfPassword is provided, it is ignored.

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=/;Data Source=oracle;";
con.Open();
...

See Also:
Oracle Database Platform Guide for Windows for information on how to set up an Oracle Database to authenticate database users using Windows user login credentials

Privileged Connections

Oracle allows database administrators to connect to an Oracle Database with either SYSDBA or SYSOPER privileges. This is done through the DBA Privilege attribute of the ConnectionString.
The following example connects SYS/SYS as SYSDBA:

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=SYS;Password=SYS;" +
    "DBA Privilege=SYSDBA;Data Source=oracle;";
con.Open();
...

See Also:
"DBA Privilege " for further information on privileged connections in the database server

Password Expiration

Oracle allows users' password to expire. ODP.NET lets applications handle the password expiration by providing a new method, OpenWithNewPassword, that opens the connection with a new password.
The following code snippet uses the OracleConnection OpenWithNewPassword method to connect with a new password of panther:

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
//  Here the con.Open() fails if the password has expired.
//  An application catches this and attempts to reconnect with a new password 
//  of "panther". The initial connection string must remain intact.
 
try {
con.Open();}
 
catch {
con.OpenWithNewPassword("panther");}
 
...

Note:
OpenWithNewPassword should only be used when the user password has expired, not for changing the password.

See Also:
"OpenWithNewPassword" for further information on connecting after the password expires

Proxy Authentication

The Oracle Database allows a middle-tier server to connect to proxy clients in a secure fashion.
In multitier environments, proxy authentication allows control of middle-tier application security by preserving client identities and privileges through all tiers, and by auditing actions taken on behalf of clients. The proxy authentication feature allows the identity of a user using a Web application to be passed through the application to the database server.
ODP.NET supports proxy authentication with or without a client password by providing the Proxy User Id and Proxy Password attributes of the ConnectionString property.

// C#
...
OracleConnection con = new OracleConnection();
// Connecting using proxy authentication
con.ConnectionString = "User Id=customer;Password=lion;" + 
     "Data Source=oracle;Proxy User Id=appserver;Proxy Password=eagle; ";
con.Open();
...

See Also:

Oracle Database Application Developer's Guide - Fundamentals for details on designing a middle-tier server using proxy users
Oracle Database SQL Reference for the description and syntax of the proxy clause for ALTER USER
Oracle Database Administrator's Guide section "Auditing in a Multi-Tier Environment"

Transparent Application Failover (TAF) Callback Support

Transparent Application Failover (TAF) is a feature in the Oracle Database that provides high availability.
TAF enables an application connection to automatically reconnect to a database if the connection fails. Active transactions roll back, but the new database connection, made by way of a different node, is identical to the original. This is true regardless of how the connection fails.
With Transparent Application Failover, a client notices no loss of connection as long as there is one instance left serving the application. The database administrator controls which applications run on which instances and also creates a failover order for each application.
Given the delays that failovers can cause, applications may wish to be notified by a TAF callback. ODP.NET supports TAF callback through the Failover event of the OracleConnection object, which allows applications to be notified whenever a failover occurs. To receive TAF callbacks, an event handler function must be registered with the Failover event.
When a failover occurs, the Failover event is raised and the registered event handler is invoked several times during the course of reestablishing the connection to another Oracle instance.
The first call to the event handler occurs when the Oracle Database first detects an instance connection loss. This allows the application to act accordingly for the upcoming delay for the failover.
If the failover is successful, the Failover event is raised again when the connection is reestablished and usable. At this time, the application can resynchronize the OracleGlobalization session setting and inform the application user that a failover has occurred.
If failover is unsuccessful, the Failover event is raised to inform the application that a failover did not take place.
The application can determine whether or not the failover is successful by checking the OracleFailoverEventArgs that is passed to the event handler.
The following code example registers an event handler method called OnFailover:

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
con.Open();
con.Failover += new OracleFailoverEventHandler(OnFailover);
...

The Failover event only invokes one event handler. If multiple Failover event handlers are registered with the Failover event, only the event handler registered last is invoked.

Note:
Distributed transactions are not supported in an environment where failover is enabled.

See Also:

23 Plus 81 ToolS

Monday, August 6, 2012