OracleCommand Object

The OracleCommand object represents SQL statements or stored procedures executed on Oracle Database.

This section includes the following topics:

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.

Note:

Transactions are not supported in a .NET stored procedure.

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:

  • ExecuteReader

  • ExecuteNonQuery

  • ExecuteScalar

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:

Data Types BINARY_FLOAT and BINARY_DOUBLE

Starting from Oracle Database 10g, the database supports two new native data types, BINARY_FLOAT and BINARY_DOUBLE.

The BINARY_FLOAT and BINARY_DOUBLE data types represent single-precision and double-precision, floating-point values respectively.

In OracleParameter binding, an application should use the enumerations OracleDbType.BinaryFloat and OracleDbType.BinaryDouble for BINARY_FLOAT and BINARY_DOUBLE data types.

OracleDbType Enumeration Type

OracleDbType enumerated values are used to explicitly specify the OracleDbType value of an OracleParameter object.

Table 3-3 lists all the OracleDbType enumeration values with a description of each enumerated value.

Table 3-3 OracleDbType Enumeration Values

Member Name Description

BFile

Oracle BFILE type

BinaryFloat

Oracle BINARY_FLOAT type

BinaryDouble

Oracle BINARY_DOUBLE type

Blob

Oracle BLOB 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

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

NestedTable

Oracle Nested Table 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

Varray

Oracle VARRAY type

XmlType

Oracle XMLType type


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-4.

Table 3-4 Inference of System.Data.DbType from OracleDbType

OracleDbType System.Data.DbType

BFile

Object

Blob

Object

BinaryFloat

Single

BinaryDouble

Double

Byte

Byte

Char

StringFixedLength

Clob

Object

Date

Date

Decimal

Decimal

Double

Double

Int16

Int16

Int32

Int32

Int64

Int64

IntervalDS

TimeSpan

IntervalYM

Int64

Long

String

LongRaw

Binary

NChar

StringFixedLength

NClob

Object

NestedTable

Object

NVarchar2

String

Object

Object

Raw

Binary

Ref

Object

RefCursor

Object

Single

Single

TimeStamp

DateTime

TimeStampLTZ

DateTime

TimeStampTZ

DateTime

Varchar2

String

Varray

Object

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-5.

Table 3-5 Inference of OracleDbType from DbType

System.Data.DbType OracleDbType

Binary

Raw

Boolean

Not Supported

Byte

Byte

Currency

Not Supported

Date

Date

DateTime

TimeStamp

Decimal

Decimal

Double

Double

Guid

Not Supported

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-6 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 3-6 Inference of DbType and OracleDbType from Value (.NET Datatypes)

Value (.NET Datatypes) System.Data.DbType OracleDbType

Byte

Byte

Byte

Byte[]

Binary

Raw

Char / Char []

String

Varchar2

DateTime

DateTime

TimeStamp

Decimal

Decimal

Decimal

Double

Double

Double

Float

Single

Single

Int16

Int16

Int16

Int32

Int32

Int32

Int64

Int64

Int64

Single

Single

Single

String

String

Varchar2

TimeSpan

TimeSpan

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-7 shows the inference of DbType and OracleDbType properties from the Value property when type of Value is one of Oracle.DataAccess.Types.

Table 3-7 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

OracleClob

Object

Clob

OracleDate

Date

Date

OracleDecimal

Decimal

Decimal

OracleIntervalDS

Object

IntervalDS

OracleIntervalYM

Int64

IntervalYM

OracleRefCursor

Object

RefCursor

OracleString

String

Varchar2

OracleTimeStamp

DateTime

TimeStamp

OracleTimeStampLTZ

DateTime

TimeStampLTZ

OracleTimeStampTZ

DateTime

TimeStampTZ

OracleXmlType

String

XmlType


PL/SQL Associative Array Binding

ODP.NET supports PL/SQL Associative Array (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:

  • CollectionType

    This property must be set to OracleCollectionType.PLSQLAssociativeArray to bind a PL/SQL Associative Array.

  • ArrayBindSize

    This property is ignored for the fixed-length element types (such as Int32).

    For variable-length element types (such as Varchar2), each element in the ArrayBindSize property specifies the size of the corresponding element in the Value property.

    For Output parameters, InputOutput parameters, and return values, this property must be set for variable-length variables.

  • ArrayBindStatus

    This property specifies the execution status of each element in the OracleParameter.Value property.

  • Size

    This property specifies the maximum number of elements to be bound in the PL/SQL Associative Array.

  • Value

    This property must be set to an array of values, null, or the DBNull.Value property.

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/tiger@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=tiger;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=tiger;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:

  • ArrayBindSize

    The ArrayBindSize property is an array of integers specifying the maximum size for each corresponding value in an array. The ArrayBindSize property is similar to the Size property of an OracleParameter object, except the ArrayBindSize property specifies the size for each value in an array.

    Before the execution, the application must populate the ArrayBindSize property; after the execution, ODP.NET populates it.

    The ArrayBindSize property is used only for parameter types that have variable length such as Clob, Blob, and Varchar2. The size is represented in bytes for binary data types, and characters for the Unicode string types. The count for string types does not include the terminating character. The size is inferred from the actual size of the value, if it is not explicitly set. For an output parameter, the size of each value is set by ODP.NET. The ArrayBindSize property is ignored for fixed-length data types.

  • ArrayBindStatus

    The ArrayBindStatus property is an array of OracleParameterStatus values that specify the status of each corresponding value in an array for a parameter. This property is similar to the Status property of the OracleParameter object, except that the ArrayBindStatus property specifies the status for each array value.

    Before the execution, the application must populate the ArrayBindStatus property. After the execution, ODP.NET populates the property. Before the execution, an application using the ArrayBindStatus property can specify a NULL value for the corresponding element in the array for a parameter. After the execution, ODP.NET populates the ArrayBindStatus property, indicating whether the corresponding element in the array has a null value, or if data truncation occurred when the value was fetched.

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/tiger@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=tiger;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-8 lists OracleParameterStatus enumeration values.

Table 3-8 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.


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:

  • Statement Cache Size

    This attribute enables or disables ODP.NET statement caching. By default, this attribute is set to 10 (enabled). If it is set to 0, the attribute is disabled. If it is set to a value greater than 0, ODP.NET statement caching is enabled and the value specifies the maximum number of statements that can be cached for a connection. Once a connection has cached up to the specified maximum cache size, the least recently used cursor is freed to make room to cache the newly created cursor.

  • Statement Cache Purge

    This attribute provides a way for connections to purge all statements that are cached when a connection is closed or placed back into the connection pool. By default, this attribute is set to false, which means that cursors are not freed when connections are placed back into the pool.

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. By default, it is set to 10.

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.

Statement Caching Methods and Properties

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

  • OracleCommand.AddToStatementCache property

    If statement caching is enabled, having this property set to true (default) adds statements to the cache when they are executed. If statement caching is disabled or if this property is set to false, the executed statement is not cached.

  • OracleConnection.PurgeStatementCache method

    This method purges all the cached statements by closing all open cursors on the database that are associated with the particular connection. Note that statement caching remains enabled after this call.

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.