About Basic Oracle Data Types
This topic describes how the Microsoft BizTalk Adapter for Oracle Database surfaces basic Oracle data types.
Supported Oracle Data Types
The Microsoft BizTalk Adapter for Oracle Database supports safe typing for some Oracle data types. When safe typing is enabled, these data types are represented as strings. You configure safe typing by setting the EnableSafeTyping binding property. Safe typing is disabled by default. For more information about the Oracle Database adapter binding properties, see Read about the Oracle Database adapter binding properties.
Note
Safe typing is not supported if data types are inside User Defined Types (UDTs).
The following table shows how the Oracle data types are surfaced with safe typing disabled (EnableSafeTyping is false). Oracle data types that are affected by the EnableSafeTyping binding property are marked with an asterisk (*).
| Oracle Data Type | XSD type | .NET type | Comments |
|---|---|---|---|
| BFile | input: xsd:string output: xsd:base64Binary |
String Byte[] |
BFile data type is not supported inside complex types (such as RecordType, TableType, UDT, and VArray). |
| Blob | xsd:base64Binary | Byte[] | Supported for table operations and procedures. |
| Char | xsd:string | String | Supported for table operations and procedures. |
| Clob | xsd:string | String | Supported for table operations and procedures. |
| Date* (No safe typing if inside an UDT) |
xsd:dateTime | DateTime | Date values cannot contain time zone information (UTC or UTC offsets): - xsd:dateTime values must not contain UTC or UTC offsets - DateTime.Kind must be DateTimeKind.Unspecified If time zone information is specified, the adapter throws an XmlReaderParsingException exception with a message that indicates the field. |
| Float** | xsd:float if prec <=7 xsd:double if prec > 7 and <=15 xsd:string if prec > 15 |
Float Double String |
- |
| IntervalYM | xsd:string xsd:long if inside an UDT |
String Long if inside an UDT |
The value should be expressed in Oracle native format: Year-Month; For example, "1-2" (1 year and 2 months). |
| IntervalDS | xsd:string xsd:duration if inside an UDT |
String Timespan if inside an UDT |
The value should be expressed in Oracle native format: Day HH:MI:SSxFF; for example, "5 15:30:12.99" |
| Long | xsd:string | String | Supported for all table operations, stored procedures, and functions. Note: Starting with the Oracle database 9i release, the LONG data type is deprecated. Oracle recommends using the LOB data types instead. Hence, when performing operations on the Oracle database using the Oracle Database adapter, we recommend using Oracle database artifacts that operate on LOB data types and not the LONG data type. |
| LongRaw | xsd:base64Binary | Byte[] | - |
| NChar | xsd:string | String | - |
| NClob | xsd:string | String | Supported for table operations and procedures. |
| Number** | xsd:decimal if prec <=28 xsd:string if prec > 28 |
Decimal String |
- |
| NVarchar2 | xsd:string | String | - |
| Raw | xsd:base64Binary | Byte[] | Supported for table operations and procedures. |
| RowID | xsd:string | String | - |
| TimeStamp* (No safe typing if inside an UDT) |
xsd:dateTime if prec <= 7 xsd:string if prec > 7 |
DateTime String |
TimeStamp values cannot contain time zone information (UTC or UTC offsets): - xsd:dateTime values must not contain UTC or UTC offsets - DateTime.Kind must be DateTimeKind.Unspecified If time zone information is specified, the adapter throws an XmlReaderParsingException exception with a message that indicates the field. |
| TimeStampLTZ | xsd:string | String | TimeStampLTZ is not supported inside UDTs. Outside an UDT: The value should be expressed in NLS_TIMESTAMP_TZ_FORMAT. |
| TimeStampTZ | xsd:string xsd:dateTime if inside an UDT |
String DateTime if inside an UDT |
Outside an UDT: The value should be expressed in NLS_TIMESTAMP_TZ_FORMAT. |
| Decimal** | xsd:decimal if prec <=28 xsd:string if prec > 28 |
Decimal String |
- |
| Varchar2 | xsd:string | String | - |
| Binary Float** | xsd:float if prec <=7 xsd:string if prec > 7 |
Float String |
You must specify the value in a form consistent with your locale (System.Globalization.CultureInfo.CurrentCulture). For example, for English locale use a period character (‘.’) to specify the decimal; for French locale, use a comma character (‘,’). |
| Binary Double** | xsd:double if prec <=15 xsd:string if prec > 15 |
Double String |
- |
| Binary Integer** | xsd:integer | Int32 | Supported for procedures, functions, and packages. |
| Boolean | xsd:boolean | Nullable boolean | |
| XMLTYPE | xsd:string | String | Supported for top level procedure parameters. Reserved XML characters like ‘<’, ‘>’ must be replaced with their entity representation (<, >) when developing applications in BizTalk, and when using WCF channel Model. This is not required in the case of WCF Service Model. |
*The way in which these Oracle data types are surfaced is affected by the EnableSafeTyping binding property.
**The way in which these Oracle numeric data types inside DataSets and weakly-typed REF CURSORS are surfaced is affected by the EnableSafeTyping binding property.
Important
- The maximum length of the value in an Oracle data type in the Oracle Database adapter is bound by the maximum length of the value supported by ODP.NET for the Oracle data type.
- The Oracle Database adapter internally treats the Oracle numeric data types inside UDTs as .NET Decimal. However, in general (that is outside UDTs), the Oracle Database adapter internally treats the Oracle numeric data types as OracleDecimal.
Safe Typing Enabled
The following table shows how the Oracle data types that are affected by safe typing are changed when the EnableSafeTyping binding property is true.
| Oracle Data Type | XSD type | .NET type | Comment |
|---|---|---|---|
| Date | xsd:string | String | The value should be expressed in Oracle NLS_DATE_FORMAT. |
| TimeStamp | xsd:string | String | The value should be expressed in Oracle NLS_TIMESTAMP_FORMAT. |
Important
If safe typing is enabled, the Oracle numeric data types inside DataSets and weakly-typed REF CURSORS are always exposed as strings.
Oracle data types that are not in this table are surfaced in the same way whether safe typing is enabled or disabled.
Validation
The Microsoft BizTalk Adapter for Oracle Database performs no explicit validation on the values that you specify for Oracle data types. However, depending on the Oracle data type and whether safe typing is enabled or disabled, implicit validation may be performed:
When de-serializing between the XML passed in a message and the .NET types that are used internally by the adapter.
By ODP.NET for some data types.