Skip Headers

Oracle® Data Provider for .NET Developer's Guide
10g Release 1 (10.1)

Part Number B10117-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

OracleCommand Object

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

This section includes the following topics:

Transaction

The 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 only be started on a 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.

Parameter Binding

ODP.NET allows applications to retrieve data as either a .NET Framework type or an ODP.NET type.

How the data is retrieved depends on whether application sets the OUT parameter to the DbType property (.NET type) or OracleDbType property (ODP.NET type) of the OracleParameter.

For example, if the output parameter is bound as an DbType.String, the output data is returned as a .NET String. On the other hand, if the parameter is bound as OracleDbType.Char, the output data is returned as OracleString type.

When the DbType of an OracleParameter is set, the OracleDbType of the OracleParameter changes accordingly, and vice versa. The parameter set last prevails.

Lastly, an application can simply bind the data and have ODP.NET infer both the DbType and OracleDbType from the .NET type of the parameter value.

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.

This section describes the following:

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.

Table 3-7 OracleDbType Enumeration Values

Member Name Description
BFile Oracle BFILE 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
NClob Oracle NCLOB type
NVarchar2 Oracle NVARCHAR2 type
Raw Oracle RAW 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

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

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

OracleDbType System.Data.DbType
BFile Object
Blob Object
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
NVarchar2 String
Raw Binary
RefCursor Object
Single Single
TimeStamp DateTime
TimeStampLTZ DateTime
TimeStampTZ DateTime
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-9.

Table 3-9 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 Not Supported
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 which can be of any .NET Framework datatype or ODP.NET type. If the OracleDbType and DbType in the OracleParameter object are not specified, OracleDbType is inferred from the type of the Value property.

Table 3-10 shows the inference of DbType and OracleDbType from Value when the type of Value is one of the .NET Framework datatypes.

Table 3-10 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 datatypes as values for OracleParameter without specifying either the DbType or the OracleDbType raises an exception because inferring DbType and OracleDbType from other .NET Framework datatypes is not supported.

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

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

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.


Code Example

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


See Also:

"Value " for more information

OracleParameter Array Bind Properties

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

  • ArrayBindSize Property

    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 except ArrayBindSize specifies the size for each value in an array.

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

    ArrayBindSize is used only for parameter types that have variable length such as Clob, Blob and Varchar2. The size is represented in bytes for binary datatypes 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 datatypes.

  • ArrayBindStatus Property

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

    Before the execution, the application must populate the ArrayBindStatus property and after the execution, ODP.NET populates it. Before the execution, an application using ArrayBindStatus can specify a NULL value for the corresponding element in the array for a parameter. After the execution, ODP.NET populates the ArrayBindStatus array, 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 is thrown during an Array Bind execution, the OracleErrorCollection 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.

Code Snippet

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


See Also:

"ArrayBindIndex " for more information

OracleParameterStatus Enumeration Types

Table 3-12 provides different values for OracleParameterStatus enumeration.

Table 3-12 OracleParameterStatus Members

Member Names Description
Success For input parameters, it indicates that the input value has been assigned to the column.

For output parameters, it 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.