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,