Oracle® Data Provider for .NET Developer's Guide 10g Release 1 (10.1) Part Number B10117-01 |
|
|
View PDF |
The OracleCommand
object represents SQL statements or stored procedures executed on the Oracle Database.
This section includes the following topics:
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.
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:
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
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 |
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.
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 |
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 |
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 forOracleParameter 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 |
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.
This property must be set to OracleCollectionType.PLSQLAssociativeArray
to bind a PL/SQL Associative Array.
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.
This property specifies the execution status of each element in the OracleParameter.Value
property.
This property specifies the maximum number of elements to be bound in the PL/SQL Associative Array.
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 ... }
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();
The OracleParameter
object provides two properties for granular control when using the array bind feature:
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.
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.
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); }
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. |