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,


Similar Articles