Oracle Data Provider for .NET: Part V

Before reading this article, please go through the following articles

OracleDbType Enumeration Type

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

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

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

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

References

To gain a more comprehensive understanding of the subject, please read the next part,

Oracle Data Provider for .NET : Part VI