Connecting to Oracle Database

Connection String Attributes

Table 3-1 lists the supported connection string attributes.

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

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class ConnectionSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //using connection string attributes to connect to Oracle Database
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}
 

Specifying the Data Source Attribute

This section describes different ways of specifying the data source attribute.

The following example shows a connect descriptor mapped to a TNS alias called sales in the tnsnames.ora file:

sales=
 (DESCRIPTION= 
  (ADDRESS= (PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA= 
     (SERVICE_NAME=sales.us.acme.com)))

"user id=scott;password=tiger;data source=sales";

"user id=scott;password=tiger;data source=" + 
     "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" + 
     "(HOST=sales-server)(PORT=1521))(CONNECT_DATA="+
     "(SERVICE_NAME=sales.us.acme.com)))"

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

//host:[port]/[service_name]

"user id=scott;password=tiger;data source=//sales-server:1521/sales.us.acme.com"
"user id=scott;password=tiger;data source=//sales-server/sales.us.acme.com" 
"user id=scott;password=tiger;data source=sales-server/sales.us.acme.com"

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:

• Connection Lifetime

• Connection Timeout

• Decr Pool Size

• HA Events

• Incr Pool Size

• Load Balancing

• Max Pool Size

• Min Pool Size

• Pooling

• Validate Connection

Connection Pooling Example

The following example opens a connection using ConnectionString attributes related to connection pooling.

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
 
class ConnectionPoolingSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Open a connection using ConnectionString attributes
    //related to connection pooling.
    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();
    Console.WriteLine("Connection pool successfully created");
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Connection is placed back into the pool.");
  }
}

Connection Pooling for a Real Application Clusters (RAC) Database

This section discusses optimization and other aspects of connection pooling for a Real Application Clusters (RAC) database. RAC is the technology that makes grids possible for Oracle database by providing the ability to access the database from multiple instances, each running on nodes in a cluster.

Operating System Authentication

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 a slash (/). If the Password attribute is provided, it is ignored.

The following example shows the use of operating system authentication:

/* Create an OS-authenticated user in the database
   Assume init.ora has OS_AUTHENT_PREFIX set to "" and <OS_USER>
   is any valid OS or DOMAIN user.
 
     create user <OS_USER> identified externally;
     grant connect, resource to <OS_USER>;
 
   Login through OS Authentication and execute the sample.  See Oracle 
   documentation for details on how to configure an OS-Authenticated user
*/
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class OSAuthenticationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Establish connection using OS Authentication   
    con.ConnectionString = "User Id=/;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

Privileged Connections

Oracle allows database administrators to connect to Oracle Database with either SYSDBA or SYSOPER privileges. This is done through the DBA Privilege attribute of the ConnectionString property.

The following example connects scott/tiger as SYSDBA:

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class PrivilegedConnectionSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Connect scott/tiger as SYSDBA   
    con.ConnectionString = "User Id=scott;Password=tiger;" + 
      "DBA Privilege=SYSDBA;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

Password Expiration

Oracle allows users passwords 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 example uses the OracleConnection OpenWithNewPassword method to connect with a new password of panther:

/* Database Setup
connect / as sysdba;
drop user testexpire cascade;
-- create user "testexpire" with password "testexpire"
grant connect , resource to testexpire identified by testexpire; 
alter user testexpire password expire;
*/
 
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class PasswordExpirationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    try
    {
      con.ConnectionString = 
        "User Id=testexpire;Password=testexpire;Data Source=oracle";
      con.Open();
      Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    }
    catch (OracleException ex)
    {
      Console.WriteLine(ex.Message);
 
      //check the error number 
      //ORA-28001 : the password has expired
      if (ex.Number == 28001)
      {
        Console.WriteLine("\nChanging password to panther");
        con.OpenWithNewPassword("panther");
        Console.WriteLine("Connected with new password.");
      }
    }
    finally
    {
      // Close and Dispose OracleConnection object
      con.Close();
      con.Dispose();
      Console.WriteLine("Disconnected");
    }
  }
}

Proxy Authentication

With proper setup in the database, proxy authentication enables middle-tier applications to control the security by preserving database user identities and privileges, and auditing actions taken on behalf of these users. This is accomplished by creating and using a proxy database user that connects and authenticates against the database on behalf a database user (that is, the real user) or database users.

Proxy authentication also provides better scalability. If connection pooling is used in conjunction with proxy authentication, the proxy authenticated connections can be shared among different real users. This is because only the connection and session established for the proxy is cached. An additional session is created for the real user when a proxy authenticated connection is requested, but it will be destroyed appropriately when the proxy authenticated connection is placed back into the pool. This design enables the application to scale well without sacrificing security.

ODP.NET applications can use proxy authentication by setting the "Proxy User Id" and "Proxy Password" attributes in the connection string. The real user is specified by the "User Id" attribute. Optionally, to enforce greater security, the real user's password can be provided through the "Password" connection string attribute.

The following example illustrates the use of ODP.NET proxy authentication:

/*  Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.
    Create a proxy user and modified scott to allow proxy connection.
  
     create user appserver identified by eagle;
     grant connect, resource to appserver;
     alter user scott grant connect through appserver;
*/
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class ProxyAuthenticationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    // Connecting using proxy authentication
    con.ConnectionString = "User Id=scott;Password=tiger;" + 
      "Data Source=oracle;Proxy User Id=appserver;Proxy Password=eagle; ";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

Client Identifier

The client identifier is a predefined attribute from the Oracle application context namespace USERENV. It is similar to proxy authentication because it can enable tracking of user identities. However, client identifier does not require the creation of two sessions (one for the proxy user and another for the end user) as proxy authentication does. In addition, the client identifier does not have to be a database user. It can be set to any string. But most importantly, by using client identifier, ODP.NET developers can use application context and Oracle Label Security, and configure Oracle Virtual Private Database (VPD) more easily. To set the client identifier, ODP.NET applications can set the ClientId property on the OracleConnection object after opening a connection. If connection pooling is enabled, the ClientId is reset to null whenever a connection is placed back into the pool.

ODP.NET exposes the ClientId property on the OracleConnection object. Setting the ClientId property internally sets the CLIENT_IDENTIFIER attribute on the session. To clear the ClientId property, simply set it to "" or string.Empty. The ClientId property is write-only.

Transparent Application Failover (TAF) Callback Support

Transparent Application Failover (TAF) is a feature in Oracle Database that provides high availability.

TAF enables an application connection to automatically reconnect to another database instance if the connection gets severed. 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 TAF, 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.

When a session fails over to another database, the NLS settings that were initially set on the original session are not carried over to the new session. Therefore, it is the responsibility of the application to set these NLS settings on the new session.

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class TAFCallBackSample
{
  public static FailoverReturnCode OnFailover(object sender, 
                                              OracleFailoverEventArgs eventArgs)
  {
    switch (eventArgs.FailoverEvent)
    {
      case FailoverEvent.Begin :
        Console.WriteLine(
          " \nFailover Begin - Failing Over ... Please standby \n");
        Console.WriteLine(
          " Failover type was found to be " + eventArgs.FailoverType);
        break;
 
      case FailoverEvent.Abort :
        Console.WriteLine(" Failover aborted. Failover will not take place.\n");
        break;
 
      case FailoverEvent.End :
        Console.WriteLine(" Failover ended ...resuming services\n");
        break;
 
      case FailoverEvent.Reauth :
        Console.WriteLine(" Failed over user. Resuming services\n");
        break;
 
      case FailoverEvent.Error :
        Console.WriteLine(" Failover error gotten. Sleeping...\n");
        return FailoverReturnCode.Retry;
 
      default :
        Console.WriteLine("Bad Failover Event: %d.\n", eventArgs.FailoverEvent);
        break;
    }
    return FailoverReturnCode.Success;
  } /* OnFailover */
 
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
    con.Open();
    con.Failover += new OracleFailoverEventHandler(OnFailover);
    Console.WriteLine("Event Handler is successfully registered");
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
  }
}