Oracle Data Provider for .NET: Part V

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

Continue article...