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