Table 3.2 lists all the Oracle native types supported by ODP.NET and their corresponding ODP.NET type. The third column lists the .NET Framework datatype that corresponds to the Value property of each ODP.NET Type.
Table 3-2 Oracle Native Types Supported by ODP.NET
Oracle Native Type | ODP.NET Type | .NET Framework Datatypes |
BFILE | OracleBFile class | System.Byte[] |
BLOB | OracleBlob class | System.Byte[] |
CHAR | OracleString structure | System.String |
CLOB | OracleClob class | System.String |
DATE | OracleDate structure | System.DateTime |
INTERVAL DAY TO SECOND | OracleIntervalDS structure | System.TimeSpan |
INTERVAL YEAR TO MONTH | OracleIntervalYM structure | System.Int64 |
LONG | OracleString structure | System.String |
LONG RAW | OracleBinary structure | System.Byte[] |
NCLOB | OracleClob class | System.String |
NCHAR | OracleString structure | System.String |
NUMBER | OracleDecimal structure | System.Decimal |
NVARCHAR2 | OracleString structure | System.String |
RAW | OracleBinary structure | System.Byte[] |
REF CURSOR | OracleRefCursor class | Not Applicable |
TIMESTAMP | OracleTimeStamp structure | System.DateTime |
TIMESTAMP WITH LOCAL TIME ZONE | OracleTimeStampLTZ structure | System.DateTime |
TIMESTAMP WITH TIME ZONE | OracleTimeStampTZ structure | System.DateTime |
UROWID | OracleString structure | System.String |
VARCHAR2 | OracleString structure | System.String |
XMLType | OracleXmlType class | System.String |
Obtaining Data From an OracleDataReader
The ExecuteReader method of the OracleCommand object returns an OracleDataReader object, which is a read-only, forward-only result set.
Typed OracleDataReader Accessors
The OracleDataReader provides two types of typed accessors:
.NET Type Accessors
Table 3.3 lists all the Oracle native database types that ODP.NET supports and the corresponding .NET Type that best represents the Oracle native type. The third column indicates the valid typed accessor that can be invoked for an Oracle native type to be obtained as a .NET type. If an invalid typed accessor is used for a column, an InvalidCastException is thrown. Oracle native datatypes depend on the version of the database; therefore, some datatypes are not available in earlier versions of Oracle.
Table 3-3 .NET Type Accessors
Oracle Native Datatype | .NET Type | Typed Accessor |
BFILE | System.Byte[ ] | GetBytes |
BLOB | System.Byte[ ] | GetBytes |
CHAR | System.String | GetString GetChars |
CLOB | System.String | GetString GetChars |
DATE | System.DateTime | GetDateTime |
INTERVAL (DS) | System.Interval | GetTimeSpan |
INTERVAL (YM) | System.Interval | GetTimeSpan |
LONG | System.String | GetString GetChars |
LONG RAW | System.Byte[ ] | GetBytes |
NCHAR | System.String | GetString GetChars |
NCLOB | System.String | GetString GetChars |
NUMBER | System.Decimal | GetDecimal |
NVARCHAR2 | System.String | GetString GetChars |
RAW | System.Byte[ ] | GetBytes |
ROWID | System.String | GetString GetChars |
TIMESTAMP | System.TimeStamp | GetTimeStamp |
TIMESTAMP WITH LOCAL TIME ZONE | System.TimeStamp | GetTimeStamp |
TIMESTAMP WITH TIME ZONE | System.TimeStamp | GetTimeStamp |
UROWID | System.String | GetString GetChars |
VARCHAR2 | System.String | GetString GetChars |
XMLType | System.String System.Xml.XmlReader | GetString GetXmlReader |
ODP.NET Type Accessors
ODP.NET exposes provider-specific types that natively represent the datatypes in Oracle. In some cases, these ODP.NET types provide better performance and functionality that is not available to the corresponding .NET types. The ODP.NET types can be obtained from the OracleDataReader by calling their respective typed accessor.
Table 3.4 lists the valid type accessors that ODP.NET uses to obtain ODP.NET Types for an Oracle native type.
Table 3-4 ODP.NET Type Accessors
Oracle Native Database Type | ODP.NET Type | Typed Accessor |
BFILE | OracleBFile | GetOracleBFile |
BLOB | OracleBlob OracleBlob OracleBinary | GetOracleBlob GetOracleBlobForUpdate GetOracleBinary |
CHAR | OracleString | GetOracleString |
CLOB | OracleClob OracleClob OracleString | GetOracleClob GetOracleClobForUpdate GetOracleString |
DATE | OracleDate | GetOracleDate |
INTERVAL (DS) | OracleIntervalDS | GetOracleIntervalDS |
INTERVAL (YM) | OracleIntervalYM | GetOracleIntervalYM |
LONG | OracleString | GetOracleString |
LONG RAW | OracleBinary | GetOracleBinary |
NCHAR | OracleString | GetOracleString |
NCLOB | OracleString | GetOracleString |
NUMBER | OracleDecimal | GetOracleDecimal |
NVARCHAR2 | OracleString | GetOracleString |
RAW | OracleBinary | GetOracleBinary |
ROWID | OracleString | GetOracleString |
TIMESTAMP | OracleTimeStamp | GetOracleTimeStamp |
TIMESTAMP WITH LOCAL TIME ZONE | OracleTimeStampLTZ | GetOracleTimeStampLTZ |
TIMESTAMP WITH TIME ZONE | OracleTimeStampTZ | GetOracleTimeStampTZ |
UROWID | OracleString | GetOracleString |
VARCHAR2 | OracleString | GetOracleString |
XMLType | OracleString OracleXmlType | GetOracleString GetOracleXmlType |
Obtaining LONG and LONG RAW Data
When an OracleDataReader is created containing a LONG or LONG RAW column type, OracleDataReader determines whether this column data needs to be fetched immediately or not, by checking the value of the InitialLONGFetchSize property of the OracleCommand that created the OracleDataReader.
By default, InitialLONGFetchSize is set to 0. If the InitialLONGFetchSize property value of the OracleCommand is left as 0, the entire LONG or LONG RAW data retrieval is deferred until that data is explicitly requested by the application. If the InitialLONGFetchSize property is set to a nonzero value, the LONG or LONG RAW data is immediately fetched up to the number of characters or bytes that the InitialLONGFetchSize property specifies.
ODP.NET does not support CommandBehavior.SequentialAccess. Therefore, LONG and LONG RAW data can be fetched in a random fashion.
Obtain data beyond InitialLONGFetchSize bytes or characters, one of the following must be in the select list:
- primary key
- ROWID
- unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT NULL constraint defined on it).
The requested data is fetched from the database when the appropriate typed accessor method (GetOracleString or GetString for LONG or GetOracleBinary or GetBytes for LONG RAW) is called on the OracleDataReader object.
In order to fetch the data in a non-defer mode or when the columns in the select list do not have a primary key column, a ROWID, or unique columns, set the size of the InitialLONGFetchSize property on the OracleCommand object to equal or greater than the amount of bytes or characters needed to be retrieved.
OracleDbType Enumeration Type
OracleDbType enumerated values are used to explicitly specify the OracleDbType of an OracleParameter.
Table 3.5 lists all the OracleDbType enumeration values with a description of each enumerated value.
Table 3-5 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.6.
Table 3-6 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 inTable 3.7.
Table 3-7 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.8 shows the inference of DbType and OracleDbType from Value when the type of Value is one of the .NET Framework datatypes.
Table 3-8 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 |
Table 3.9 shows the inference of DbType and OracleDbType from Value when type of Value is one of Oracle.DataAccess.Types.
Table 3-9 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.
- CollectionType
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();
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.
- ArrayBindSize Property
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);
}