OracleCommand Object

The OracleCommand object represents SQL statements executed on Oracle Database.

Note: Optimizer hint syntax in the form --+ ... is not supported. ODP.NET supports this syntax: /*+ ... */.

This section includes the following topics:

Command Auto-Commit

By default, ODP.NET commits each SQL operation immediately upon execution outside of a transaction. Starting with 26ai, managed ODP.NET and ODP.NET Core can disable auto-commit so that SQL statements are executed only after an explicit commit statement.

Unmanaged ODP.NET cannot disable auto-commit without a transaction.

Developers disable auto-commit by setting the OracleConnection AutoCommit property to false. ODP.NET can then start transactions implicitly. After the AutoCommit property is set, that setting is honored for all subsequent Open() calls on that specific OracleConnection object until the AutoCommit value is modified.

OracleConnection includes APIs to manage local transactions, such as the ability to commit, rollback, and create savepoints. It can check if the application is currently within a transaction. It can set the transaction isolation level.

When AutoCommit is set while enlisted in an explicit (that is, BeginTransaction), local, or distributed (that is, System.Transactions) transaction, an error will occur. Calling any OracleConnection transaction management API while enlisted in System.Transactions will also result in an error.

Transactions

Oracle Database starts a transaction only in the context of a connection. Once a transaction starts, all the successive command execution on that connection run in the context of that transaction. Transactions can be started only on an OracleConnection object, and the read-only Transaction property on the OracleCommand object is implicitly set by the OracleConnection object. Therefore, the application cannot set the Transaction property, nor does it need to.

Explicit transactions are required with SQL statements containing "FOR UPDATE" and "RETURNING" clauses. This is not necessary if global transactions are used.

System.Transactions and Promotable Transactions

ODP.NET supports System.Transactions. A local transaction is created for the first connection opened in the System.Transactions scope to Oracle Database. When a second connection is opened, this transaction is automatically promoted to a distributed transaction. This functionality provides enhanced performance and scalability.

Connections created within a transaction context, such as TransactionScope or ServicedComponent, can be established to different versions of Oracle Database. However, in order to enable the local transaction to be promotable, the following must be true:

Transaction promotion will throw an ORA-24797 error when the database transaction is already distributed due to the use of database links.

If applications use System.Transactions, it is required that the enlist connection string attribute is set to either true (default) or dynamic. However, enlist=dynamic cannot be used with TransactionScope because auto-enlistment requires enlist=true.

ODP.NET supports the following System.Transactions programming models for applications using distributed transactions.

Implicit Transaction Enlistment Using TransactionScope

The TransactionScope class provides a mechanism to write transactional applications where the applications do not need to explicitly enlist in transactions.To accomplish this, the application uses the TransactionScope object to define the transactional code. Connections created within this transactional scope will enlist in a local transaction that can be promoted to a distributed transaction.

Note that the application must call the Complete method on the TransactionScope object to commit the changes. Otherwise, the transaction is aborted by default.

// C#

using System;
using Oracle.DataAccess.Client;
using System.Data;
using System.Data.Common;
using System.Transactions;

class psfTxnScope
{
  static void Main()
  {
    int retVal = 0;
    string providerName = "Oracle.DataAccess.Client";
    string constr =
           @"User Id=scott;Password=tiger;Data Source=oracle;enlist=true";

    // Get the provider factory.
    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

    try
    {
      // Create a TransactionScope object, (It will start an ambient
      // transaction automatically).
      using (TransactionScope scope = new TransactionScope())
      {
        // Create first connection object.
        using (DbConnection conn1 = factory.CreateConnection())
        {
          // Set connection string and open the connection. this connection
          // will be automatically enlisted in a promotable local transaction.
          conn
1.ConnectionString = constr;
          conn1.Open();

          // Create a command to execute the sql statement.
          DbCommand  cmd1 = factory.CreateCommand();
          cmd1.Connection = conn1;
          cmd1.CommandText = @"insert into emp (empno, ename, job) values
                                                     (1234, 'emp1', 'dev1')";

          // Execute the SQL statement to insert one row in DB.
          retVal = cmd1.ExecuteNonQuery();
          Console.WriteLine("Rows to be affected by cmd1: {0}", retVal);

          // Close the connection and dispose the command object.
          conn1.Close();
          conn1.Dispose();
          cmd1.Dispose();
        }

        // The Complete method commits the transaction. If an exception has
        // been thrown or Complete is not called then the transaction is
        // rolled back.
        scope.Complete();
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex.StackTrace);
    }
  }
}

Explicit Transaction Enlistment Using CommittableTransaction

The instantiation of the CommittableTransaction object and the EnlistTransaction method provides an explicit way to create and enlist in a transaction. Note that the application must call Commit or Rollback on the CommittableTransaction object.

// C#

using System;
using Oracle.DataAccess.Client;
using System.Data;
using System.Data.Common;
using System.Transactions;

class psfEnlistTransaction
{
  static void Main()
  {
    int retVal = 0;
    string providerName = "Oracle.DataAccess.Client";
    string constr =
           @"User Id=scott;Password=<PASSWORD>;Data Source=oracle;enlist=dynamic";

    // Get the provider factory.
    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

    try
    {
      // Create a committable transaction object.
      CommittableTransaction cmtTx = new CommittableTransaction();

      // Open a connection to the DB.
      DbConnection conn1 = factory.CreateConnection();
      conn1.ConnectionString = constr;
      conn1.Open();

      // enlist the connection with the commitable transaction.
      conn1.EnlistTransaction(cmtTx);

      // Create a command to execute the sql statement.
      DbCommand cmd1 = factory.CreateCommand();
      cmd1.Connection = conn1;
      cmd1.CommandText = @"insert into emp (empno, ename, job) values
                                                     (1234, 'emp1', 'dev1')";

      // Execute the SQL statement to insert one row in DB.
      retVal = cmd1.ExecuteNonQuery();
      Console.WriteLine("Rows to be affected by cmd1: {0}", retVal);

      // commit/rollback the transaction.
      cmtTx.Commit();   // commits the txn.
      //cmtTx.Rollback(); // rolls back the txn.

      // close and dispose the connection
      conn1.Close();
      conn1.Dispose();
      cmd1.Dispose();
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex.StackTrace);
    }
  }
}

See Also: “EnlistTransaction”

Distributed Transactions

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database.

ODP.NET Core, Managed, and Unmanaged Drivers integrate with Microsoft Distributed Transaction Coordinator (MSDTC) and Oracle databases. MSDTC coordinates with all the resource managers that are enlisted to the same System.Transactions object, to perform a 2-phrase commit or rollback atomically. With that, Oracle distributed transactions can then be committed or rolled back across networks properly.

ODP.NET only supports the read committed isolation level for distributed transactions.

ODP.NET Core 23.9 and higher supports distributed transactions on Windows operating systems only. .NET Core applications require TransactionManager.ImplicitDistributedTransactions to be set to true to use distributed transactions. Failure to do so will result in an ORA-50130.

Microsoft Distributed Transaction Coordinator Integration

Managed ODP.NET and ODP.NET Core include a native fully managed implementation that supports integration with MSDTC for distributed transactions enlistments, commits, and rollbacks.

For unmanaged ODP.NET, Oracle Services for Microsoft Transaction (OraMTS) provides MSDTC integration that allows client components to participate in Oracle database distributed transactions. OraMTS act as a proxy for the Oracle database to MSDTC to ensure that Oracle distributed database transactions commit or rollback together with the rest of the distributed transaction.

Unmanaged ODP.NET can use either a managed or unmanaged OraMTS implementation. Oracle recommends using the managed OraMTS for applications requiring high availability from Oracle RAC or Data Guard.

If a failure occurs in a distributed transaction, such as a network failure or server hardware failure, then it can leave an in-process transaction in-doubt. All ODP.NET provider types use the OraMTS recovery service to resolve these in-doubt transactions. This recovery service runs as a Windows service. Administrators must install and configure the OraMTS Windows recovery service to manage recovery scenarios whether they use managed or unmanaged ODP.NET. Only one recovery service is needed per machine.

It is required to install the OraMTS Recovery Service on all the client machines where ODP.NET is running and participating in MSDTC. As a machine may have multiple IP addresses, administrators for managed ODP.NET applications can specify the host machine name or IP address that has the running recovery service in the application’s .NET configuration file. ODP.NET, Unmanaged Driver resolves the IP/machine name for the recovery service automatically.

Table 14 ODP.NET Distributed Transaction Support Matrix

ODP.NET Type Minimum Version Distributed Transaction Support
Core .NET 8 NET’s distributed transaction implementation
Managed .NET Framework 4.8 .NET Framework’s native managed implementation for distributed transactions.
Unmanaged .NET Framework 4.8 OraMTS (default) or managed OraMTS implementation. Oracle recommends using managed OraMTS for unmanaged ODP.NET applications requiring high availability from Oracle RAC or Data Guard.

See Also: Manually Creating an Oracle MTS Recovery Service in Oracle Services for Microsoft Transaction Server Developer’s Guide for Microsoft Windows.

ODP.NET Core and Managed Driver Setup

This section explains the setup and configuration steps required for using distributed transactions with ODP.NET Core and Managed Driver. Follow these steps to configure distributed transactions:

  1. Create and setup the OraMTS recovery service or make sure an existing recovery service is running.

  2. Set the port number the OraMTS recovery service listens on to resolve in-doubt transactions. The property can be set on OracleConfiguration, OracleDataSourceBuilder, or .NET configuration file.

ODP.NET, Unmanaged Driver Setup

Note: ODP.NET, Unmanaged Driver is deprecated in 26ai. See Oracle Database Upgrade Guide for more information.

This section explains the setup and configuration steps required for using distributed transactions with ODP.NET, Unmanaged Driver.

ODP.NET, Unmanaged Driver includes managed OraMTS in its assembly. OraMTS is the default option for ODP.NET, Unmanaged Driver, to ensure implementation continuity during upgrades. However, Oracle recommends the managed OraMTS option when using any high availability FAN operations (HA Events = true) with Oracle Real Application Clusters or Oracle Data Guard. The managed OraMTS option support high availability functionality, while the traditional OraMTS does not.

Applications can set OraMTS (default) or managed OraMTS usage through the UseOraMTSManaged parameter in the .NET configuration file.

Install and configure OraMTS, including its recovery service to use traditional OraMTS implementation for ODP.NET, Unmanaged Driver.

To set managed OraMTS up, perform the following steps:

  1. Set UseOraMTSManaged to true in the .NET configuration file.

  2. Create and setup the OraMTS recovery service or make sure an existing recovery service is running. See Also: distributedTransaction section for more information about .NET configuration setup

Sessionless Transactions

A transaction that can perform work and span across multiple database sessions is called a sessionless transaction.

Oracle database sessionless transactions enable apps to start a transaction, submit a unit of work, and suspend the transaction in one session. The transaction can then be resumed in a different database session, any session, and continue to perform work before committing or rolling back.

A local transaction must operate within the same session, which is a limitation sessionless transactions do not have. No transaction manager, such as distributed transaction coordinator (DTC), is required for sessionless transactions that span across database sessions. Transaction manager involvement generally requires higher overhead and slows performance to perform two-phase commit and might result in in-doubt transaction when network failure occurs.

With sessionless transactions, a transaction manager, like DTC, is not needed. The database internally coordinates the two-phase commit (2PC) protocol without the need for any application-side logic. Therefore, there is no risk of in-doubt transactions and no need for any recovery mechanism.

With sessionless transactions, neither keeping a connection open for long periods of time to support a long running transaction nor a transaction manager is necessary. This flexibility gives transactional apps more possibilities to make design choices that are efficient, easier to manage, and better performing.

Oracle Database release 23.6 introduced support for sessionless transactions on the server side. Managed ODP.NET and ODP.NET Core 23.7 established client side sessionless transaction support.

ODP.NET sessionless transactions do not support promotable nor distributed transactions. They do not support transaction savepoints nor Transparent Application Failover. They also do not support temporary tables as temporary table data is not allowed to span sessions.

Sessionless transaction support with Application Continuity and Transparent Application Continuity will be enabled in a future ODP.NET release.

Sessionless Transaction Operations

An ODP.NET sessionless transaction can have the following operations:

Sessionless Transaction ODP.NET Code Sample

OracleConnection conn1 = new OracleConnection(ConnStr1);
conn1.Open();

//Specify sessionless transaction options.
//Parameter 1 (timeout): Specify transaction timeout, such as 100 seconds
//Parameter 2 (start behavior): Specify the transaction start behavior, such as start before the command execution
//Parameter 3 (resume behavior): Specify the resume transaction behavior, such as resume before the command execution
//Parameter 4 (suspend sessionless transaction after execution): Specify whether to suspend the transaction after command execution, such as true to suspend
conn1.SessionlessTransactionOptions = new OracleSessionlessTransactionOptions(100, OracleSessionlessTransactionStartBehavior.StartBeforeExecution, OracleSessionlessTransactionStartBehavior.StartBeforeExecution, true);

//Begin sessionless transaction. The transaction ID can be used to resume in separate session when the transaction is suspended.
byte[] txnId = conn1.BeginSessionlessTransaction();

//Execute data update in first connection.
//The sessionless transaction is started, the insert is executed under the transaction context and the sessionless transaction is suspended after the execution.
OracleCommand cmd = new OracleCommand("insert into emp values (23, 'abc')", conn1);
cmd.ExecuteNonQuery();

//Open new connection to same database
OracleConnection conn2 = new OracleConnection(connStr);
conn2.Open();

//Resume the sessionless transaction using the transaction ID
conn2.ResumeSessionlessTransaction(txnId);

//Execute a data update with the new connection in the existing sessionless transaction
OracleCommand cmd2 = new OracleCommand("insert into dept values (2, 'dept1')", conn2);

//The sessionless transaction resumes and the second DML is executed under the same sessionless transaction context.
cmd2.ExecuteNonQuery();

//Execute another command, the third DML is executed under the same sessionless transaction context
cmd2.CommandText = "insert into emp values (25, 'def')";
cmd2.ExecuteNonQuery();

//Commit the sessionless transaction. All three DMLs commit.
conn2.Commit();

See Also: Developing Applications with Sessionless Transactions

Priority Transactions

Transactions can inadvertently hold database row locks for long durations. For example, an app modifies some rows but doesn’t commit or terminate the transaction due to an exception. Historically, if a transaction is blocked on a row lock from another transaction, the database administrator had to manually terminate the blocking transaction.

Starting with Oracle AI Database 26ai version 23.26.2, Oracle database transactions that hold row locks blocking higher priority transactions can now be automatically rolled back. After the rollback, the lower priority transaction’s session remains active. However, the application must explicitly acknowledge the rollback-typically by calling the ODP.NET Rollback() method. Until this acknowledgment is made, the session will continually receive ORA-63302 and ORA-63300 errors when executing any commands.

As a best practice, .NET apps should catch these two Oracle exceptions and issue a rollback on any incomplete transaction. Additionally, consider notifying the end user of the rollback and retrying the lower priority transaction.

For ODP.NET to determine transaction priority, apps assign a value of high (default), medium, or low. High priority transactions can roll back medium and low priority transactions. Medium transactions can roll back low priority transactions.

In most cases, once the transaction priority is set, its value applies to all future transactions in that session.

Low priority transactions have a wait time before roll back commences. The database determines how long a high priority transaction waits using the PRIORITY_TXNS_HIGH_WAIT_TARGET value. Medium priority transactions have a similar timeout setting in PRIORITY_TXNS_MEDIUM_WAIT_TARGET.

When more than one low priority transaction blocks a high priority one, wait time is enforced individually for each blocking transaction. Thus, the total high priority transaction wait time may exceed the wait target in this scenario.

Priority levels must be set when there are no active (uncommitted) transactions in the session. Once there is an active transaction, its priority cannot be modified. An ORA-50024 error occurs when trying to change the priority of an active transaction.

ODP.NET transaction priority can be set on the OracleConfiguration, OracleConnection, OracleDataSourceBuilder classes, and in the .NET Framework .NET configuration file using the TransactionPriority property. It can also be set using OracleConnection.BeginTransaction method.

The priority value is set with the OracleTransactionPriority enumeration. Its possible values are High (default), Medium, or Low.

A local transaction’s priority carries over to the distributed transaction when promoted.

A sessionless transaction maintains its priority level throughout its lifetime.

ODP.NET priority transaction code sample

using Oracle.ManagedDataAccess.Client;

namespace ODPNET_Txn_Priority
{
	class Program
	{

		static void Main()
		{
			//Connect with HR schema credentials
			using (OracleConnection connLow = new OracleConnection(<CONNECTION STRING>))
			using (OracleConnection connHigh = new OracleConnection(<CONNECTION STRING>))
			{
				connLow.TransactionPriority = OracleTransactionPriority.Low;
				connHigh.TransactionPriority = OracleTransactionPriority.High;

				connLow.Open();
				connHigh.Open();

				OracleCommand cmdlow = connLow.CreateCommand();
				OracleCommand cmdHigh = connHigh.CreateCommand();

				OracleTransaction txnLow = connLow.BeginTransaction();
				OracleTransaction txnHigh = connHigh.BeginTransaction();

				cmdlow.Transaction = txnLow;
				cmdHigh.Transaction = txnHigh;

				cmdlow.CommandText = $"UPDATE employees SET salary = salary + 1 WHERE employee_id = 100";
				cmdHigh.CommandText = $"UPDATE employees SET salary = salary + 10 WHERE employee_id = 100";

				try
				{
					//Low priority transaction blocks update for same row as high priority transaction
					cmdlow.ExecuteNonQuery();
					cmdHigh.ExecuteNonQuery(); //Waits for the time PRIORITY_TXNS_HIGH_WAIT_TARGET is set to
					txnHigh.Commit();
				}
				catch (OracleException oe)
				{
		                    //Exception occurs without lower priority transaction automatic rollback acknowledgement
		                    // for an active session executing SQL or next SQL in session
		                    if (oe.Number == 63300)
						Console.WriteLine($"Expected error message for automatic rollback: {oe}");
					else
						Console.WriteLine($"Unexpected error for block: {oe}");
				}
				catch (Exception e)
				{
					Console.WriteLine(e.ToString());
				}

				//Try to execute new low priority transaction without automatic rollback acknowledgement
				OracleCommand commandRolledBack = connLow.CreateCommand();
				commandRolledBack.CommandText = $"select 1 from dual";
				try
				{
					var reader = commandRolledBack.ExecuteReader();
				}
				catch (OracleException oex)
				{
					//Expect following errors:
					//ORA - 63302: Transaction must roll back
					//ORA - 63300: Transaction is automatically rolled back since it is blocking a higher priority transaction from another session.
					if (oex.Number == 63302)
					{
						Console.WriteLine($"Expected error message for subsequent SQL: {oex}");
						//Acknowledge automatic rollback
						txnLow.Rollback();
					}
					else
						Console.WriteLine($"Unexpected error for subsequent SQL: {oex}");
				}

				//Verify high priority transaction committed and low priority transaction rolled back
				cmdlow.CommandText = $"select salary from employees WHERE employee_id = 100";
				try
				{
					using (OracleDataReader reader = cmdlow.ExecuteReader())
					{
						while (reader.Read())
						{
							int salary_amount = reader.GetInt32(0);

							Console.WriteLine();
							Console.WriteLine("Verify high priority transaction committed.");
							Console.WriteLine($"New salary amount: {salary_amount}");
						}
					}
				}
				catch (Exception ex)
				{
					Console.WriteLine(ex);
				}
			}
		}
	}
}

See Also: Oracle AI Database Managing Transactions

Parameter Binding

When the DbType property of an OracleParameter object is set, the OracleDbType property of the OracleParameter object changes accordingly, or vice versa. The parameter set last prevails.An application can bind the data and have ODP.NET infer both the DbType and OracleDbType properties from the .NET type of the parameter value.ODP.NET allows applications to obtain an output parameter as either a .NET Framework type or an ODP.NET type. The application can specify which type to return for an output parameter by setting the DbType property of the output parameter (.NET type) or the OracleDbType property (ODP.NET type) of the OracleParameter object. For example, if the output parameter is set as a DbType.String type by setting the DbType property, the output data is returned as a .NET String type. On the other hand, if the parameter is set as an OracleDbType.Char type by setting the OracleDbType property, the output data is returned as an OracleString type. If both DbType and OracleDbType properties are set before the command execution, the last setting takes affect.

ODP.NET populates InputOutput, Output, and ReturnValue parameters with the Oracle data, through the execution of the following OracleCommand methods:

An application should not bind a value for output parameters; it is the responsibility of ODP.NET to create the value object and populate the OracleParameter Value property with the object.

When binding by position (default) to a function, ODP.NET expects the return value to be bound first, before any other parameters.

This section describes the following:

Command Timeouts

The OracleCommand CommandTimeout property limits how long a command is allowed to execute before terminating with an exception. This setting prevents long running commands from consuming excessive resources or from blocking other necessary operations from occurring.

The database server can be interrupted via either TCP/IP urgent data or normal TCP/IP data, called out of band (OOB) or in band data, respectively. Windows-based database servers only support in band breaks, whereas all other (predominantly UNIX-based) database servers can support OOB or in band breaks.

ODP.NET, Managed Driver uses OOB breaks by default with database servers that support it. For certain network topologies, the routers or firewalls involved in the route to the database may have been configured to drop urgent data or in band the data. If the routers or firewalls can not be changed to handle urgent data appropriately, then the ODP.NET, Managed Driver can be configured to utilize in band breaks by setting the .NET configuration parameter Disable_Oob to on.

See Also: settings section in the Oracle Data Provider for .NET, Managed Driver Configuration section for more information.

OracleDbType Enumeration Type

OracleDbType enumerated values are used to explicitly specify the OracleDbType value of an OracleParameter object. Table 3-15 lists all the OracleDbType enumeration values with a description of each enumerated value.

Table 15 OracleDbType Enumeration Values

Member Name Description
Array Oracle Collection (VArray or Nested Table)
BFile Oracle BFILE type
BinaryFloat Oracle BINARY_FLOAT type
BinaryDouble Oracle BINARY_DOUBLE type
Blob Oracle BLOB type
Boolean Oracle BOOLEAN type
Byte byte type
Char Oracle CHAR type
Clob Oracle CLOB type
Date Oracle DATE type
Decimal Oracle NUMBER type
Double 8-byte FLOAT type
Int16 2-byte INTEGER type
Int32 4-byte INTEGER type
Int64 8-byte INTEGER type
IntervalDS Oracle INTERVAL DAY TO SECOND type
IntervalYM Oracle INTERVAL YEAR TO MONTH type
Json Oracle JSON type
Long Oracle LONG type
LongRaw Oracle LONG RAW type
NChar Oracle NCHAR type
Object Oracle Object type
NClob Oracle NCLOB type
NVarchar2 Oracle NVARCHAR2 type
Raw Oracle RAW type
Ref Oracle REF type
RefCursor Oracle REF CURSOR type
Single 4-byte FLOAT type
TimeStamp Oracle TIMESTAMP type
TimeStampLTZ Oracle TIMESTAMP WITH LOCAL TIME ZONE type
TimeStampTZ Oracle TIMESTAMP WITH TIME ZONE type
Varchar2 Oracle VARCHAR2 type
XmlType Oracle XMLType type

Note: PL/SQL LONG, LONG RAW, RAW, and VARCHAR data types can be bound with a size up to 32512 bytes.

Inference of DbType, OracleDbType, and .NET Types

This section explains the inference from the System.Data.DbType, OracleDbType, and Value properties in the OracleParameter class.

In the OracleParameter class, DbType, OracleDbType, and Value properties are linked. Specifying the value of any of these properties infers the value of one or more of the other properties.

Inference of DbType from OracleDbType

In the OracleParameter class, specifying the value of OracleDbType infers the value of DbType as shown in Table 3-16.

Table 16 Inference of System.Data.DbType from OracleDbType

OracleDbType System.Data.DbType
Array Object
BFile Object
Blob Object
BinaryFloat Single
BinaryDouble Double
Boolean Boolean
Byte Byte
Char StringFixedLength
Clob Object
Date Date
Decimal Decimal
Double Double
Int16 Int16
Int32 Int32
Int64 Int64
IntervalDS Object
IntervalYM Int64
Json String
Long String
LongRaw Binary
NChar StringFixedLength
NClob Object
NVarchar2 String
Object Object
Raw Binary
Ref Object
RefCursor Object
Single Single
TimeStamp DateTime
TimeStampLTZ DateTime
TimeStampTZ DateTimeOffset
Varchar2 String
XmlType String

Inference of OracleDbType from DbType

In the OracleParameter class, specifying the value of DbType infers the value of OracleDbType as shown in Table 3-17.

Table 17 Inference of OracleDbType from DbType

System.Data.DbType OracleDbType
Binary Raw
Boolean Boolean
Byte Byte
Currency Not Supported
Date Date
DateTime TimeStamp
DateTimeOffset TimeStampTZ
Decimal Decimal
Double Double
Guid Blob
Int16 Int16
Int32 Int32
Int64 Int64
Object Object
Sbyte Not Supported
Single Single
String Varchar2
StringFixedLength Char
Time TimeStamp
UInt16 Not Supported
UInt32 Not Supported
Uint64 Not Supported
VarNumeric Not Supported

Inference of DbType and OracleDbType from Value

In the OracleParameter class, Value is an object type that can be of any .NET Framework data type or ODP.NET type. If the OracleDbType and DbType properties of the OracleParameter class are not specified, the OracleDbType property is inferred from the type of the Value property. Table 3-18 shows the inference of DbType and OracleDbType properties from the Value property when the type of Value is one of the .NET Framework data types.

Table 18 Inference of DbType and OracleDbType from Value (.NET Datatypes)

Value (.NET Datatypes) System.Data.DbType OracleDbType
Boolean Boolean Boolean
Byte Byte Byte
Byte[] Binary Raw
Char / Char [] String Varchar2
DateTime DateTime TimeStamp
DateTimeOffset DateTimeOffset TimeStampTZ
Decimal Decimal Decimal
Double Double Double
Float Single Single
Guid Guid Blob
Int16 Int16 Int16
Int32 Int32 Int32
Int64 Int64 Int64
IOracleCustomType Object Object
Single Single Single
String String Varchar2
TimeSpan Object IntervalDS

Note: Using other .NET Framework data types as values for the OracleParameter class without specifying either the DbType or the OracleDbType properties raises an exception because inferring DbType and OracleDbType properties from other .NET Framework data types is not supported.

Table 3-19 shows the inference of DbType and OracleDbType properties from the Value property when type of Value is one of Oracle.DataAccess.Types.

Table 19 Inference of DbType and OracleDbType from Value (ODP.NET Types)

Value (Oracle.DataAccess.Types) System.Data.DbType OracleDbType
OracleBFile Object BFile
OracleBinary Binary Raw
OracleBlob Object Blob
OracleBoolean Boolean Boolean
OracleClob Object Clob
OracleDate Date Date
OracleDecimal Decimal Decimal
OracleIntervalDS Object IntervalDS
OracleIntervalYM Int64 IntervalYM
OracleRef Object Ref
OracleRefCursor Object RefCursor
OracleString String Varchar2
OracleTimeStamp DateTime TimeStamp
OracleTimeStampLTZ DateTime TimeStampLTZ
OracleTimeStampTZ DateTimeOffset TimeStampTZ
OracleXmlType String XmlType

PL/SQL Associative Array Binding

ODP.NET supports PL/SQL Associative Arrays (formerly known as PL/SQL Index-By Tables) binding.

An application can bind an OracleParameter object, as a PL/SQL Associative Array, to a PL/SQL stored procedure. The following OracleParameter properties are used for this feature:

ODP.NET supports binding parameters of PL/SQL Associative Arrays which contain the following data types.

Using unsupported data types with associative arrays can cause an ORA-600 error.

Example of PL/SQL Associative Arrays

This example binds three OracleParameter objects as PL/SQL Associative Arrays: Param1 as an In parameter, Param2 as an InputOutput parameter, and Param3 as an Output parameter.

PL/SQL Package: MYPACK

/* Setup the tables and required PL/SQL:

   connect scott/<PASSWORD>@oracle
   CREATE TABLE T1(COL1 number, COL2 varchar2(20));

   CREATE or replace PACKAGE MYPACK AS
     TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;
     PROCEDURE TestVarchar2(
       Param1 IN     AssocArrayVarchar2_t,
       Param2 IN OUT AssocArrayVarchar2_t,
       Param3    OUT AssocArrayVarchar2_t);
     END MYPACK;
/

   CREATE or REPLACE package body MYPACK as
     PROCEDURE TestVarchar2(
       Param1 IN     AssocArrayVarchar2_t,
       Param2 IN OUT AssocArrayVarchar2_t,
       Param3    OUT AssocArrayVarchar2_t)
     IS
     i integer;
     BEGIN
       -- copy a few elements from Param2 to Param1\n
       Param3(1) := Param2(1);
       Param3(2) := NULL;
       Param3(3) := Param2(3);
       -- copy all elements from Param1 to Param2\n
       Param2(1) := Param1(1);
       Param2(2) := Param1(2);
       Param2(3) := Param1(3);
       -- insert some values to db\n
       FOR i IN
1..3 LOOP
         insert into T1 values(i,Param2(i));
       END LOOP;
     END TestVarchar2;
   END MYPACK;
/
 */

// C#

using System;
using System.Data;
using Oracle.DataAccess.Client;

class AssociativeArraySample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();

    con.ConnectionString = "User Id=scott;Password=<PASSWORD>;Data Source=oracle";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);

    OracleCommand cmd = new OracleCommand(
      "begin MyPack.TestVarchar2(:1, :2, :3); end;", con);

    OracleParameter Param1 = cmd.Parameters.Add("1", OracleDbType.Varchar2);
    OracleParameter Param2 = cmd.Parameters.Add("2", OracleDbType.Varchar2);
    OracleParameter Param3 = cmd.Parameters.Add("3", OracleDbType.Varchar2);

    Param1.Direction = ParameterDirection.Input;
    Param2.Direction = ParameterDirection.InputOutput;
    Param3.Direction = ParameterDirection.Output;

    // Specify that we are binding PL/SQL Associative Array
    Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

    // Setup the values for PL/SQL Associative Array
    Param1.Value = new string[3] {
      "First Element", "Second Element ", "Third Element "
    };
    Param2.Value = new string[3] {
      "First Element", "Second Element ", "Third Element "
    };
    Param3.Value = null;

    // Specify the maximum number of elements in the PL/SQL Associative Array
    Param1.Size = 3;
    Param2.Size = 3;
    Param3.Size = 3;

    // Setup the ArrayBindSize for Param1
    Param1.ArrayBindSize = new int[3] { 13, 14, 13 };

    // Setup the ArrayBindStatus for Param1
    Param1.ArrayBindStatus = new OracleParameterStatus[3] {
      OracleParameterStatus.Success, OracleParameterStatus.Success,
      OracleParameterStatus.Success};

    // Setup the ArrayBindSize for Param2
    Param2.ArrayBindSize = new int[3] { 20, 20, 20 };

    // Setup the ArrayBindSize for Param3
    Param3.ArrayBindSize = new int[3] { 20, 20, 20 };

    // execute the cmd
    cmd.ExecuteNonQuery();

    //print out the parameter's values
    Console.WriteLine("parameter values after executing the PL/SQL block");
    for (int i = 0; i < 3; i++)
      Console.WriteLine("Param2[{0}] = {1} ", i,
        (cmd.Parameters[1].Value as Array).GetValue(i));

    for (int i = 0; i < 3; i++)
      Console.WriteLine("Param3[{0}] = {1} ", i,
        (cmd.Parameters[2].Value as Array).GetValue(i));

    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

Array Binding

The array bind feature enables applications to bind arrays of a type using the OracleParameter class. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip.

The following example inserts three rows into the Dept table with a single database round-trip. The OracleCommand ArrayBindCount property defines the number of elements of the array to use when executing the statement.

// C#

using System;
using System.Data;
using Oracle.DataAccess.Client;

class ArrayBindSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
    con.ConnectionString = "User Id=scott;Password=<PASSWORD>;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected successfully");

    int[] myArrayDeptNo = new int[3] { 10, 20, 30 };
    OracleCommand cmd = new OracleCommand();

    // Set the command text on an OracleCommand object
    cmd.CommandText = "insert into dept(deptno) values (:deptno)";
    cmd.Connection = con;

    // Set the ArrayBindCount to indicate the number of values
    cmd.ArrayBindCount = 3;

    // Create a parameter for the array operations
    OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32);

    prm.Direction = ParameterDirection.Input;
    prm.Value = myArrayDeptNo;

    // Add the parameter to the parameter collection
    cmd.Parameters.Add(prm);

    // Execute the command
    cmd.ExecuteNonQuery();
    Console.WriteLine("Insert Completed Successfully");

    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
  }
}

See Also: “Value” for more information

OracleParameter Array Bind Properties

The OracleParameter class provides two properties for granular control when using the array bind feature:

Error Handling for Array Binding

If an error occurs during an array bind execution, it can be difficult to determine which element in the Value property caused the error. ODP.NET provides a way to determine the row where the error occurred, making it easier to find the element in the row that caused the error.

When an OracleException object is thrown during an array bind execution, the OracleErrorCollection object contains one or more OracleError objects. Each of these OracleError objects represents an individual error that occurred during the execution, and contains a provider-specific property, ArrayBindIndex, which indicates the row number at which the error occurred.

The following example demonstrates error handling for array binding:

/* Database Setup
connect scott/<PASSWORD>@oracle
drop table depttest;
create table depttest(deptno number(2));
*/

// C#

using System;
using System.Data;
using Oracle.DataAccess.Client;

class ArrayBindExceptionSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
    con.ConnectionString = "User Id=scott;Password=<PASSWORD>;Data Source=oracle;";
    con.Open();

    OracleCommand cmd = new OracleCommand();

    // Start a transaction
    OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

    try
    {
      int[] myArrayDeptNo = new int[3] { 10, 200000, 30 };
      // int[] myArrayDeptNo = new int[3]{ 10,20,30};

      // Set the command text on an OracleCommand object
      cmd.CommandText = "insert into depttest(deptno) values (:deptno)";
      cmd.Connection = con;

      // Set the ArrayBindCount to indicate the number of values
      cmd.ArrayBindCount = 3;

      // Create a parameter for the array operations
      OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32);

      prm.Direction = ParameterDirection.Input;
      prm.Value = myArrayDeptNo;

      // Add the parameter to the parameter collection
      cmd.Parameters.Add(prm);

      // Execute the command
      cmd.ExecuteNonQuery();
    }
    catch (OracleException e)
    {
      Console.WriteLine("OracleException {0} occured", e.Message);
      if (e.Number == 24381)
        for (int i = 0; i < e.Errors.Count; i++)
          Console.WriteLine("Array Bind Error {0} occured at Row Number {1}",
            e.Errors[i].Message, e.Errors[i].ArrayBindIndex);

      txn.Commit();
    }
    cmd.Parameters.Clear();
    cmd.CommandText = "select count(*) from depttest";

    decimal rows = (decimal)cmd.ExecuteScalar();

    Console.WriteLine("{0} row have been inserted", rows);
    con.Close();
    con.Dispose();
  }
}

See Also: “ArrayBindIndex” for more information

OracleParameterStatus Enumeration Types

Table 3-20 lists OracleParameterStatus enumeration values.

Table 20 OracleParameterStatus Members

Member Names Description
Success

For input parameters, indicates that the input value has been assigned to the column.

For output parameters, indicates that the provider assigned an intact value to the parameter.
NullFetched Indicates that a NULL value has been fetched from a column or an OUT parameter.
NullInsert Indicates that a NULL value is to be inserted into a column.
Truncation Indicates that truncation has occurred when fetching the data from the column.

Batch Processing

The OracleDataAdapter UpdateBatchSize property enables batch processing when the OracleDataAdapter.Update method is called. UpdateBatchSize is a numeric property that indicates how many DataSet rows to update the Oracle database for each round-trip.

This enables the developer to reduce the number of round-trips to the database.

See Also: “UpdateBatchSize”

Statement Caching

Statement caching eliminates the need to parse each SQL or PL/SQL statement before execution by caching server cursors created during the initial statement execution. Subsequent executions of the same statement can reuse the parsed information from the cursor, and then execute the statement without reparsing, for better performance.

In order to see performance gains from statement caching, Oracle recommends caching only those statements that will be repeatedly executed. Furthermore, SQL or PL/SQL statements should use parameters rather than literal values. Doing so takes full advantage of statement caching, because parsed information from parameterized statements can be reused even if the parameter values change in subsequent executions. However, if the literal values in the statements are different, the parsed information cannot be reused unless the subsequent statements also have the same literal values.

Statement Caching Connection String Attributes

The following connection string attributes control the behavior of the ODP.NET statement caching feature:

Enabling Statement Caching through the Registry

To enable statement caching by default for all ODP.NET applications running in a system, without changing the application, set the registry key of HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version \StatementCacheSize to a value greater than 0. This value specifies the number of cursors that are to be cached on the server.

The default value for the system can be overridden at the connection pool level. The Statement Cache Size attribute can be set to a different size than the registry value or it can be turned off. The Statement Cache Size can also be configured through an XML configuration file.

See Also: Configuring Oracle Data Provider for .NET for more details.

Statement Caching Methods and Properties

The following property and method are relevant only when statement caching is enabled:

Connections and Statement Caching

Statement caching is managed separately for each connection. Therefore, executing the same statement on different connections requires parsing once for each connection and caching a separate cursor for each connection.

Pooling and Statement Caching

Pooling and statement caching can be used in conjunction. If connection pooling is enabled and the Statement Cache Purge attribute is set to false, statements executed on each separate connection are cached throughout the lifetime of the pooled connection.

If the Statement Cache Purge attribute is set to true, all the cached cursors are freed when the connection is placed back into the pool. When connection pooling is disabled, cursors are cached during the lifetime of the connection, but the cursors are closed when the OracleConnection object is closed or disposed of.

If schema object metadata is changed, such as dropping a table column, while the .NET app is running, ODP.NET should not use the cached schema information. The cache is now out of date and returns an “ORA-01007 variable not in select list” or similar error. If your app changes schema objects during the .NET app’s running lifetime, then disable both statement caching and metadata pooling for ODP.NET commands that use those schema objects to avoid the error.

Self-Tuning

ODP.NET applications can be self-tuned for performance optimization. ODP.NET dynamically monitors application queries during runtime. Note: Self-tuning for applications does not take place if the Pooling connection string attribute is set to false.

The statement cache size (StatementCacheSize) is tuned automatically by monitoring the statements that are executed by the application. The following sections discuss self-tuning in applications:

Self-Tuning Statement Caching

Statement caching helps improve performance by eliminating the need to re-parse each SQL or PL/SQL statement before execution.

If self-tuning is enabled for an application, then ODP.NET continuously monitors application behavior in order to determine the optimum value for the statement cache size. Any statement cache size value specified in the connection string, configuration file, or registry is ignored.

When the application first initializes, it uses the default value of statement cache size. As the application executes statements, ODP.NET collects statistics that are used to self-tune the value of statement cache size. Self-tuning of statement cache size results in increased performance.

Note: To take full advantage of statement caching, you should not dynamically generate statements, with different inline values, for every statement execution. Instead, use parameterized commands to minimize the number of unique statements that need to be executed and cached. This is because only one statement needs to be cached for every unique command text, regardless of the parameter values and the number of times that the statement is executed.

The maximum number of statements that can be cached per connection is determined by the MaxStatementCacheSize configuration attribute. The MaxStatementCacheSize value can be specified in the Windows registry or XML configuration file.

The MaxStatementCacheSize setting is useful in limiting the number of cached statements, as well as the number of open cursors. This is because a cached statement equates to a cursor being opened on the server. For this reason, you should not set MaxStatementCacheSize to a value that is greater than the database OPEN_CURSORS setting.

The following Windows registry key is used to configure the MaxStatementCacheSize configuration attribute:

HKLM\Software\Oracle\ODP.NET\version\MaxStatementCacheSize

The MaxStatementCacheSize key is of type REG_SZ. It can be set to an integer value between 0 and System.Int 32.MaxValue.

The following example sets the MaxStatementCacheSize property in an ADO.NET 2.0, or above, configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.dataaccess.client>
    <settings>
      <add name="MaxStatementCacheSize" value="300"/>
    </settings>
  </oracle.dataaccess.client>
</configuration>

If self-tuning is disabled for an application, then the value of statement cache size is determined by the settings in the connection string, configuration file, or the registry. If statement cache size is not specified in any of these sources, then the default value of statement cache size is set to 0. To have ODP.NET configured with the same default settings as previous releases of ODP.NET, disable self-tuning and set the StatementCacheSize value to 10.

See Also: “Statement Caching”

Enabling or Disabling Self-Tuning for Applications

Self-tuning for ODP.NET applications is enabled by default. An application can enable or disable self-tuning using one of the following methods:

HKLM\Software\Oracle\ODP.NET\version\SelfTuning

The SelfTuning key is of type REG_SZ. It can be set to either 1 (enabled) or 0 (disabled).

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.dataaccess.client>
    <settings>
      <add name="SelfTuning" value="1"/>
    </settings>
  </oracle.dataaccess.client>
</configuration>

Note: If the optimal statement cache size is known for an application, then you can disable self-tuning and set StatementCacheSize to its optimum value in the registry, configuration file, or the application. If self-tuning is disabled and StatementCacheSize is not set at all, then the default value of 0 is used for StatementCacheSize.

Tracing Optimization Changes

Applications can trace optimization changes made by self-tuning. All changes to StatementCacheSize are traced. Errors, if any, are also traced.

The TraceLevel used for tracing self-tuning is 64 for unmanaged ODP.NET. Managed ODP.NET and ODP.NET Core uses TraceLevel to 7.

See Also: Table 2-3 for details on TraceLevel values

Data Transmission Performance

ODP.NET data transmission performance can be optimized via the Session Data Unit (SDU) size setting. The SDU is a buffer that Oracle uses to place data before transmitting it across the network. Oracle sends the data in the buffer either when requested or when it is full.

Adjusting the size of the SDU buffers relative to the amount of data to send at any one time can improve performance, network utilization, and memory consumption. When large amounts of data are being transmitted, increasing the SDU size can improve performance and network throughput. SDU size can be adjusted lower or higher to achieve higher throughput for a specific deployment. A larger SDU size allows more data to be sent per buffer, reducing number of database round trips for large amounts of data. To accommodate the larger buffer, more memory consumption occurs.

This buffer size can be set on either the ODP.NET client side or database server side. ODP.NET supports SDU sizes between 256 and 2,097,152 bytes. In ODP.NET, this can be set in the .NET configuration file (.NET Framework), tnsnames.ora, or sqlnet.ora.

For managed ODP.NET and ODP.NET Core, if the application does not provide a client side SDU value, then connections will use the database server’s SDU size. If the client side SDU size is set, then connections will compare it against the DB server’s SDU size, and then choose the lower of the two values for the SDU size to use.

Unmanaged ODP.NET SDU setting behavior is the same as the Oracle database client’s.

See Also: Optimizing Performance in Oracle AI Database Net Services Administrator’s Guide for more SDU details.

Securing Identifiers and Literals from SQL Injection Attacks

ODP.NET command parameter binding is recommended to protect against many SQL injection attack types. In cases in which literal or database identifier values cannot be bound as parameters, developers historically had to then validate those input values programmatically. In essence, their code took on the onus of SQL injection protection.

Managed ODP.NET and ODP.NET Core 23.8 introduce methods to make securing ODP.NET app code easier when using these literal and identifier values. These methods sanitize the input values to prevent their usage as SQL injection vectors.

The new methods validate literals and database identifiers, and then add quotes around them if they are missing.

Database identifiers include Oracle SQL reserved words.

These capabilities were also introduced in managed ODP.NET and ODP.NET Core 19c and 21c, starting with version 19.32 and 21.23, respectively.

  <div class="infoboxnote" markdown="1">

  **Note:**

  - [OracleDBMSAssert Class](OracleDBMSAssertClass.html#GUID-7BC39F56-04AE-47E8-ACE1-156F4B809B6F)

  - [Oracle SQL Reserved Words](/pls/topic/lookup?ctx=en/database/oracle/oracle-database/26/odpnt&id=SQLRF-GUID-55C49D1E-BE08-4C50-A9DD-8593EB925612)

  </div>