Datatypes BINARY_FLOAT and BINARY_DOUBLE

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

BINARY_FLOAT and BINARY_DOUBLE datatypes represent single-precision and double-precision floating point values respectively.

In OracleParameter binding, an application should use the enumerations OracleDbType.Float and OracleDbType.Double for BINARY_FLOAT and BINARY_DOUBLE datatypes.

OracleDbType Enumeration Type

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

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

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

An application can bind an OracleParameter, 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 either be set to an array of values or null or DBNull.Value.

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 : My Pack

CREATE 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;    

PL/SQL Package Body : My Pack

CREATE 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 y to z
      Param3(1) := Param2(1);
      Param3(2) := NULL;
      Param3(3) := Param2(3);

-- copy all elements from x to y
      Param2(1) := Param1(1);
      Param2(2) := Param1(2);
      Param2(3) := Param1(3);
      
      FOR i IN 1..3 LOOP
        insert into T1 values(i, Param2(i));
      END LOOP;
           
      FOR i IN 1..3 LOOP
        select COL2 into Param2(i) from T2 where COL1 = i;
      END LOOP;
    END TestVarchar2;
END MYPACK;

ODP.NET Example

public void BindAssocArray(){
... 
 
      OracleCommand cmd = new OracleCommand(
        "begin MyPack.TestVarchar2(:1, :2, :3); end;", con);
 
      OracleParameter Param1 = cmd.Parameters.Add(...);
      OracleParameter Param2 = cmd.Parameters.Add(...);
      OracleParameter Param3 = cmd.Parameters.Add(...);
 
      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
... 
    }

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 code 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# 
... 
// Create an array of values that need to be inserted 
int[] myArrayDeptNo = new int[3]{10, 20, 30}; 

// Set the command text on an OracleCommand object 
cmd.CommandText = "insert into dept(deptno) values (:deptno)"; 
// 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(); 

try { 
  / An Array Bind execution errors out}
 
catch (OracleException e){
  Console.WriteLine ("OracleException {0} occured", e.Message);
  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);
}