OracleConnection(String)

This constructor instantiates a new instance of the OracleConnection class with the provided connection string.

Declaration

// C#
public OracleConnection(String connectionString);

Parameters

connectionString

The connection information used to connect to the Oracle database.

Remarks

The ConnectionString property is set to the supplied connectionString. The ConnectionString property is parsed and an exception is thrown if it contains invalid connection string attributes or attribute values.

The properties of the OracleConnection object default to the following values unless they are set by the connection string:

ConnectionString = empty string

ConnectionTimeout = 15 (default value of 0 is used for the implicit database connection)

IsAvailable

This property indicates whether or the implicit database connection is available for use.

Declaration

// C#
public static bool IsAvailable {get;}

Property Value

Returns true if the implicit database connection is available for use.

Remarks

The availability of the implicit database connection can be checked at runtime through this static property. When Oracle Data Provider for .NET is used within a .NET stored procedure, this property always returns true. Otherwise, false is returned.

To obtain an OracleConnection object in a .NET stored procedure that represents the implicit database connection, set the ConnectionString property of the OracleConnection object to "contextconnection=true" and invoke the Open method.

Note that not all features that are available for an explicit user connection are available for an implicit database connection. See "Implicit Database Connection" for details.

ClearPool

This method clears the connection pool that is associated with the provided OracleConnection object.

Declaration

// C#
public static void ClearPool(OracleConnection connection);

Remarks (Oracle.DataAccess.Client only)

When this method is invoked, all idle connections are closed and freed from the pool. Currently used connections remain usable and will be placed back into the pool when the user invokes Close() on the OracleConnection object.

The connection pool is usable once this method is invoked. Therefore, connection requests succeed even after the invocation of this method. Connections created after this method invocation are not cleared unless another invocation is made.

This method can be invoked with an OracleConnection object before opening the connection as well as after, provided the ConnectionString is properly set.

Exceptions

InvalidOperationException – Either the connection pool cannot be found or the provided connection string is invalid.

Example (Oracle.DataAccess.Client only)

// C#
// Sample demonstrating the use of ClearPool API in OracleConnection class
using System;
using Oracle.DataAccess.Client;
class ClearPoolSample
{
static void Main()
{
Console.WriteLine("Running ClearPool sample..." );
// Set the connection string
string strConn = "User Id=scott;Password=tiger;Data Source=oracle;" +
"Min pool size=5;";
OracleConnection conn = new OracleConnection(strConn);
// Open the connection
conn.Open();
// Clears the connection pool associated with connection 'conn'
OracleConnection.ClearPool (conn);
// This connection will be placed back into the pool
conn.Close ();
// Open the connection again to create additional connections in the pool
conn.Open();
// Create a new connection object
OracleConnection connNew = new OracleConnection(strConn);
// Clears the pool associated with Connection 'connNew'
// Since the same connection string is set for both the connections,
// connNew and conn, they will be part of the same connection pool.
// We need not do an Open() on the connection object before calling
// ClearPool
OracleConnection.ClearPool (connNew);
// cleanup
conn.Close();
Console.WriteLine("Done!");
}
}

Setting ClientId to null resets the client identifier for the connection. Setting ClientId to an empty string sets the client identifier for the connection to an empty string. ClientId is set to null when the Close method is called on the OracleConnection object.

Using the ClientId property allows the application to set the client identifier in the application context for every database session using ODP.NET. This enables ODP.NET developers to configure the Oracle Virtual Private Database (VPD) more easily.

ConnectionString

This property specifies connection information used to connect to an Oracle database.

Declaration

// ADO.NET 2.0: C#
public override string ConnectionString{get; set;}

// ADO.NET: 1.x C#
public string ConnectionString{get; set;}

Property Value

If the connection string is supplied through the constructor, this property is set to that string.

Implements

IDbConnection

Exceptions

ArgumentException - An invalid syntax is specified for the connection string.

InvalidOperationException - ConnectionString is being set while the connection is open.

Remarks

The default value is an empty string.

ConnectionString must be a string of attribute name and value pairings, separated by a semi-colon, for example:

"User Id=scott;password=tiger;data source=oracle"

If the ConnectionString is not in a proper format, an exception is thrown. All spaces are ignored unless they are within double quotes.

When the ConnectionString property is set, the OracleConnection object immediately parses the string for errors. An ArgumentException is thrown if the ConnectionString contains invalid attributes or invalid values. Attribute values for UserId, Password, ProxyUserId, ProxyPassword, and DataSource (if provided) are not validated until the Open method is called.

The connection must be closed to set the ConnectionString property. When the ConnectionString property is reset, all previously set values are reinitialized to their default values before the new values are applied.

Oracle database supports case-sensitive user names. To connect as a user whose name is of mixed case, for example, "MySchema", the UserId attribute value must be surrounded by double quotes, as follows:

"User Id=\"MySchema\";Password=MYPASSWORD;Data Source=oracle"

However, if the Oracle user name is all upper case, the UserId connection string attribute can be set to that user name without the use of the double quotes since UserIds that are not doubled-quoted are converted to all upper case when connecting. Single quotes are not supported.

If a connection string attribute is set more than once, the last setting takes effect and no exceptions are thrown.

Boolean connection string attributes can be set to either true, false, yes, or no.

Remarks (.NET Stored Procedure)

To obtain an OracleConnection object in a .NET stored procedure that represents the implicit database connection, set the ConnectionString property of the OracleConnection object to "contextconnection=true" and invoke the Open method. Other connection string attributes cannot be used in conjunction with "contextconnection" when it is set to true.

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 of. If this property value is 0, the connection lifetime is never checked. Connections that have exceeded their lifetimes are not closed and disposed of, if doing so brings the number of connections in the pool below the MinPoolSize.

Connection Timeout

15

Maximum time (in seconds) to wait for a free connection from the pool.

This attribute specifies the maximum amount of time (in seconds) that the Open() method can take to obtain a pooled connection before it terminates the request. This value comes into effect only if no free connection is available from the connection pool and the MaxPoolSize is reached. If a free connection is not available within the specified time, an exception is thrown. ConnectionTimeout does not limit the time required to open new connections.

This attribute value takes effect for pooled connection requests and not for new connection requests.

(The default value is 0 for the implicit database connection in a .NET stored procedure)

ContextConnection

false

Returns an implicit database connection if set to true.

An implicit database connection can only be obtained from within a .NET stored procedure. Other connection string attributes cannot be used in conjunction with "contextconnection" when it is set to true.

Supported in a .NET stored procedure only

Data Source

empty string

Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect.

DBA Privilege

empty string

Administrative privileges SYSDBA or SYSOPER.

This connection string attribute only accepts SYSDBA or SYSOPER as the attribute value. It is case insensitive.

Decr Pool Size

1

Number of connections that are closed when an excessive amount of established connections are unused.

This connection string attribute controls the maximum number of unused connections that are closed when the pool regulator makes periodic checks. The regulator thread is spawned every 3 minutes and closes up to DecrPoolSize amount of pooled connections if they are not used. The pool regulator never takes the total number of connections below the MinPoolSize by closing pooled connections.

Enlist

true

Controls the enlistment behavior and capabilities of a connection in context of COM+ transactions or System.Transactions.

If this attribute is set to true, the connection is automatically enlisted in the thread's transaction context. If this attribute is false, no enlistments are made. If this attribute is set to dynamic, applications can dynamically enlist in distributed transactions. This attribute can be set to true, false, yes, no, or dynamic.

HAEvents

false

Enables ODP.NET connection pool to proactively remove connections from the pool when a RAC service, service member, or node goes down.

This feature can only used against a RAC database and only if "pooling=true".

This attribute can be set to true, false, yes, or no.

LoadBalancing

false

Enables ODP.NET connection pool to balance work requests across RAC instances based on the load balancing advisory and service goal.

This feature can only used against a RAC database and only if "pooling=true".

This attribute can be set to true, false, yes, or no.

Incr Pool Size

5

Number of new connections to be created when all connections in the pool are in use.

This connection string attribute determines the number of new connections that are established when a pooled connection is requested, but no unused connections are available and MaxPoolSize is not reached. If new connections have been created for a pool, the regulator thread skips a cycle and does not have an opportunity to close any connections for 6 minutes. Note, however, that some connections can be still be closed during this time if their lifetime has been exceeded.

Max Pool Size

100

Maximum number of connections in a pool.

This attribute specifies the maximum number of connections allowed in the particular pool used by that OracleConnection. Simply changing this attribute in the connection string does not change the MaxPoolSize restriction on a currently existing pool. Doing so simply creates a new pool with a different MaxPoolSize restriction. This attribute must be set to a value greater than the MinPoolSize. This value is ignored unless Pooling is turned on.

MetadataPooling

True

Caches metadata information.

This attribute indicates whether or not metadata information for executed queries are cached for improved performance.

Min Pool Size

1

Minimum number of connections in a pool.

This attribute specifies the minimum number of connections to be maintained by the pool during its entire lifetime. Simply changing this attribute in the connection string does not change the MinPoolSize restriction on a currently existing pool. Doing so simply creates a new pool with a different MinPoolSize restriction. This value is ignored unless Pooling is turned on.

If this attribute is set to false, the Password value setting is not returned when the application requests the ConnectionString after the connection is successfully opened by the Open() method. This attribute can be set to either true, false, yes, or no.

Pooling

true

Connection pooling.

This attribute specifies whether or not connection pooling is to be used. Pools are created using an attribute value matching algorithm. This means that connection strings which only differ in the number of spaces in the connection string use the same pool. If two connection strings are identical except that one sets an attribute to a default value while the other does not set that attribute, both requests obtain connections from the same pool. This attribute can be set to either true, false, yes, or no.

Proxy User Id

empty string

User name of the proxy user.

This connection string attribute specifies the middle-tier user, or the proxy user, who establishes a connection on behalf of a client user specified by the UserId attribute. ODP.NET attempts to establish a proxy connection if either the ProxyUserId or the ProxyPassword attribute is set to a non-empty string.

For the proxy user to connect to an Oracle database using operating system authentication, the ProxyUserId must be set to "/". The ProxyPassword is ignored in this case. The UserId cannot be set to "/" when establishing proxy connections. The case of this attribute value is preserved if it is surrounded by double quotes.

ProxyPassword

empty string

Password of the proxy user.

This connection string attribute specifies the password of the middle-tier user or the proxy user. This user establishes a connection on behalf of a client user specified by the UserId attribute. ODP.NET attempts to establish a proxy connection if either the ProxyUserId or the ProxyPassword attribute is set to a non-empty string.

StatementCachePurge

false

Statement cache purged when the connection goes back to the pool.

If statement caching is enabled, setting this attribute to true purges the Statement Cache when the connection goes back to the pool.

StatementCacheSize

10

Statement cache enabled and cache size set size, that is, the maximum number of statements that can be cached.

A value greater than zero enables statement caching and sets the cache size to itself. This value should not be greater than the value of the OPEN_CURSORS parameter set in the init.ora database configuration file.

UserId

empty string

Oracle user name.

This attribute specifies the Oracle user name. The case of this attribute value is preserved if it is surrounded by double quotes. For the user to connect to an Oracle database using operating system authentication, set the UserId to "/". Any Password attribute setting is ignored in this case.

ValidateConnection

false

Validation of connections coming from the pool.

Validation causes a round-trip to the database for each connection. Therefore, it should only be used when necessary.

Example

This code example shows that the case of the UserId attribute value is not preserved unless it is surrounded by double quotes. The example also demonstrates when connection pools are created and when connections are drawn from the connection pool.

/* Database Setup: Log on as SYS or SYSTEM that has CREATE USER privilege.
grant connect, resource to "MYSCHEMA" identified by MYPWD;
grant connect, resource to "MySchema" identified by MYPWD;
*/
// C#
using System;
using Oracle.DataAccess.Client;
class ConnectionStringSample
{
static long GetSID(OracleConnection con)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "select SYS_CONTEXT('USERENV','SID') from dual";
object sid = cmd.ExecuteScalar();
return Convert.ToInt32(sid);
}
static void Main()
{
// To demonstrate whether a connection is obtained from the same
// connection pool or not, we check the SID (session id). If the
// session IDs of two connections are the same, that would indicate
// that the connection is the same and hence the connection was
// obtained from the same connection pool.
// If the session IDs are different, it could potentially mean that a
// different connection was obtained from the same connection pool or
// a different connection is obtained from a different connection pool.
// However, since the sample always places the connection
// back into the pool by disposing it before requesting another
// connection, a connection with the same ID will be obtained again
// if a connection is requested from the same connection pool.
string constr1 = "User Id=myschema;Password=mypwd;Data Source=oracle";
string constr2 = "User Id=MySchema;Password=MyPwd;Data Source=oracle";
string constr3 = "User Id=\"MYSCHEMA\";Password=MYPWD;Data Source=oracle";
string constr4 = "User Id=\"MySchema\";Password=MYPWD;Data Source=oracle";
string constr5 = " User Id=myschema;Password=mypwd;Data Source=oracle; ";
string constr6 =
"User Id=myschema;Password=mypwd;Data Source=oracle;pooling=true";
long sid0, sid1;
// Connect as "MYSCHEMA/MYPWD"
// NOTE: the password is case insensitive
// A new connection and a new connection pool X is created
OracleConnection con1 = new OracleConnection(constr1);
con1.Open();
sid0 = GetSID(con1);
// Place connection back into connection pool X
con1.Dispose();
// Connect as "MYSCHEMA/MYPWD"
// The connection from pool X is obtained. No new connection created.
OracleConnection con2 = new OracleConnection(constr2);
con2.Open();
sid1 = GetSID(con2);
if (sid1 == sid0)
Console.WriteLine("con1 and con2 are from the same connection pool");
else
Console.WriteLine("con1 and con2 are from different connection pools");
// Place connection back into connection pool X
con2.Dispose();
// Connect as "MYSCHEMA/MYPWD"
// The connection from pool X is obtained. No new connection created.
OracleConnection con3 = new OracleConnection(constr3);
con3.Open();
sid1 = GetSID(con3);
if (sid1 == sid0)
Console.WriteLine("con1 and con3 are from the same connection pool");
else
Console.WriteLine("con1 and con3 are from different connection pools");
// Place connection back into connection pool X
con3.Dispose();
// Connect as "MySchema/MYPWD"
// A new connection and connection pool Y is created
OracleConnection con4 = new OracleConnection(constr4);
con4.Open();
sid1 = GetSID(con4);
if (sid1 == sid0)
Console.WriteLine("con1 and con4 are from the same connection pool");
else
Console.WriteLine("con1 and con4 are from different connection pools");
// Place connection back into connection pool Y
con4.Dispose();
// Connect as "MYSCHEMA/MYPWD"
// The connection from pool X is obtained
// Extra spaces or semi-colons in the connection string do not force
// new pools to be created
OracleConnection con5 = new OracleConnection(constr5);
con5.Open();
sid1 = GetSID(con5);
if (sid1 == sid0)
Console.WriteLine("con1 and con5 are from the same connection pool");
else
Console.WriteLine("con1 and con5 are from different connection pools");
// Place connection back into connection pool X
con5.Dispose();
// Connect as "MYSCHEMA/MYPWD"
// A connection is obtained from Connection Pool X.
// It's important to note that different connection strings do
// not necessarily mean that ODP.NET will create different
// connection pools for them. In other words, ODP.NET does not
// use exact string matching algorithm to determine whether
// a new connection pool needs to be created or not.
// Instead, it creates connection pools based on the uniqueness
// of attribute values settings in the connection string.
OracleConnection con6 = new OracleConnection(constr6);
con6.Open();
sid1 = GetSID(con6);
if (sid1 == sid0)
Console.WriteLine("con1 and con6 are from the same connection pool");
else
Console.WriteLine("con1 and con6 are from different connection pools");
// Place connection back into connection pool X
con6.Dispose();
}
}

ConnectionTimeout

This property specifies the maximum amount of time that the Open method can take to obtain a pooled connection before the request is terminated.

Declaration

// ADO.NET 2.0: C#
public override int ConnectionTimeout {get;}

// ADO.NET 1.x: C#
public int ConnectionTimeout {get;}

Property Value

The maximum time allowed for a pooled connection request, in seconds.

Implements

IDbConnection

Remarks

The default value is 15.

Setting this property to 0 allows the pooled connection request to wait for a free connection without a time limit. The timeout takes effect only for pooled connection requests and not for new connection requests.

Remarks (.NET Stored Procedure)

There is no connection string specified by the application and a connection on the implicit database is always available, therefore, this property is set to 0.

BeginTransaction

This method begins a local transaction with the specified isolation level.

BeginTransaction()

This method begins a local transaction.

Declaration

// C#
public OracleTransaction BeginTransaction();

Return Value

An OracleTransaction object representing the new transaction.

Implements

IDbConnection

Exceptions

InvalidOperationException - A transaction has already been started.

Remarks

The transaction is created with its isolation level set to its default value of IsolationLevel.ReadCommitted. All further operations related to the transaction must be performed on the returned OracleTransaction object.

Places the connection to the connection pool if connection pooling is enabled. Even if connection pooling is enabled, the connection can be closed if it exceeds the connection lifetime specified in the connection string. If connection pooling is disabled, the connection is closed.

InvalidOperationException - The connection is part of a local transaction or the connection is closed.

Remarks

EnlistDistributedTransaction enables objects to enlist in a specific transaction that is passed to the method. The ITransaction interface can be obtained by applying an (ITransaction) cast to the ContexUtil.Transaction property within the component that started the distributed transaction.

The connection must be open before calling this method or an InvalidOperationException is thrown.

If a connection is part of a local transaction that was started implicitly or explicitly while attempting to enlist in a distributed transaction, the local transaction is rolled back and an exception is thrown.

Invoking the rollback on the ITransaction method and calling ContextUtil.SetComplete on the same distributed transaction raises an exception.

Remarks (.NET Stored Procedure)

Using this method causes a Not Supported exception.

Example

Application:

// C#
/* This is the class that will utilize the Enterprise Services
component. This module needs to be built as an executable.
The Enterprise Services Component DLL must be built first
before building this module.
In addition, the DLL needs to be referenced appropriately
when building this application.
*/
using System;
using System.EnterpriseServices;
using DistribTxnSample;
class DistribTxnSample_App
{
static void Main()
{
DistribTxnSample_Comp comp = new DistribTxnSample_Comp();
comp.DoWork();
}
}

InvalidOperationException - The connection is part of a local transaction or the connection is closed.

Remarks

Invocation of this method immediately enlists the connection to a distributed transaction that is specified by the provided transaction parameter.

If OracleConnection is still associated with a distributed transaction that has not completed from a previous EnlistTransaction method invocation, calling this method will cause an exception to be thrown.

In general, for distributed transaction enlistments to succeed, the "enlist" connection string attribute must be set to either "true" or "dynamic" before invoking the Open method. Setting the "enlist" connection string attribute to "true" will implicitly enlist the connection when the Open method is called, if the connection is within a transaction context. Setting it to "dynamic" allows the connection to dynamically enlist in distributed transactions when an EnlistTransaction or EnlistDistributedTransaction method is called. The "enlist" attribute should be set to "false" only if the connection will never enlist in a distributed transaction.

The requested collection is not supported by the current version of Oracle database.

More restrictions were provided than the requested collection supports.

No population string is specified for requested collection.

Remarks

This method takes the name of a metadata collection and an array of String values that specify the restrictions for filtering the rows in the returned DataTable. This returns a DataTable that contains only rows from the specified metadata collection that match the specified restrictions.

For example, if the Columns collection has three restrictions (owner, tablename, and columnname), to retrieve all the columns for the EMP table regardless of schema, the GetSchema method must pass in at least these values: null, EMP.

If no restriction value is passed in, default values are used for that restriction, which is the same as passing in null. This differs from passing in an empty string for the parameter value. In this case, the empty string ("") is considered the value for the specified parameter.

collectionName is not case-sensitive, but restrictions (string values) are.