OracleCommand Object

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-5 lists all the OracleDbType enumeration values with a description of each enumerated value.

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.

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:

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

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

• BINARY_FLOAT

• CHAR

• DATE

• NCHAR

• NUMBER

• NVARCHAR2

• RAW

• ROWID

• UROWID

• VARCHAR2

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/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();
  }
}

/* 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();
  }
}

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 0 (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.

  If self tuning is enabled, then statement caching is enabled as well. The Statement Cache Size is configured automatically in such cases.

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

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. For details, see "Configuring Oracle Data Provider for .NET".

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.

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.

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 MAX_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.Int32.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.

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:

• Self-Tuning Connection String Attribute

  An application can modify the Self Tuning connection string attribute to enable or disable self-tuning for a particular connection pool. The default value for Self Tuning is true.

• Windows Registry

  An application can enable or disable self-tuning for a particular version of ODP.NET by modifying the following registry entry:

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

• Configuration File

  An ODP.NET application can modify the application configuration file (app.config) or Web configuration file (web.config) to enable or disable self-tuning.

  The following example shows how to enable self-tuning in an ADO.NET 2.0 application configuration file:

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