Basic Oracle Data Types

 

This topic describes how the Microsoft BizTalk Adapter for Oracle E-Business Suitesurfaces basic Oracle data types.

The Oracle E-Business adapter 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 enabling the EnableSafeTyping binding property (disabled by default). For more information about the Oracle E-Business adapter binding properties, see Read about the BizTalk Adapter for Oracle E-Business Suite binding properties.

System_CAPS_ICON_note.jpg 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 TypeXSD type.NET typeComments
BFileinput: xsd:string

output: xsd:base64Binary
String

Byte[]
BFile data type is not supported inside complex types (such as RecordType, TableType, UDT, and VArray).
Blobxsd:base64BinaryByte[]-
Charxsd:stringString-
Clobxsd:stringString-
Date*

(No safe typing if inside an UDT)
xsd:dateTimeDateTimeDate 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. Note: The Oracle E-Business adapter exposes Oracle Date data type as xsd:dateTime instead of xsd:date because:
  • Oracle Date data type can also contain time value.
  • There is no .NET equivalent for xsd:date.
Float**xsd:float if prec <=7

xsd:double if prec > 7 and <=15

xsd:string if prec > 15
Float

Double

String
You must specify the value consistent with the format specified for the decimal character and group separator in the NumericCharacters binding property under the MlsSettings binding property. If no value is specified for the NumericCharacters binding property, the adapter uses the MLS settings for the ODP.NET client on the same computer where the adapter is installed.
IntervalDSxsd:string

xsd:duration if inside an UDT
String

Timespan if inside an UDT
The adapter returns the IntervalDS data as a string using the OracleIntervalDS.ToString method.

The value should be expressed in Oracle native format: Day HH:MI:SSxFF (for example, "5 15:30:12.99").
IntervalYMxsd:string

xsd:long if inside an UDT
String

Long if inside an UDT
The adapter returns the IntervalYM data as a string using the OracleIntervalYM.ToString method.

The value should be expressed in Oracle native format: Year-Month; for example, "1-2" (1 year and 2 months).
Longxsd:stringStringStarting with the Oracle database 9i release, the LONG data type is deprecated. Oracle recommends using the Large Object (LOB) data types instead. Therefore, 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.
LongRawxsd:base64BinaryByte[]-
NCharxsd:stringString-
NClobxsd:stringString
Number**xsd:decimal if prec <=28

xsd:string if prec > 28
Decimal
String
-
NVarchar2xsd:stringString-
Rawxsd:base64BinaryByte[]
RowIDxsd:stringString-
TimeStamp*

(No safe typing if inside an UDT)
xsd:dateTime if prec <= 7

xsd:string if prec > 7
DateTime

String
When exposed as string (prec > 7), the value should be expressed in Oracle NLS_TIMESTAMP_FORMAT. You can specify the string format for TimeStamp data types in the TimeStampFormat binding property under the MlsSettings binding property. If no value is specified for the TimeStampFormat binding property, the adapter uses the MLS settings for the ODP.NET client on the same computer where the adapter is installed.

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.
TimeStampLTZxsd:stringStringTimeStampLTZ is not supported inside UDTs.

 Outside an UDT: The value should be expressed in Oracle NLS_TIMESTAMP_TZ_FORMAT. You can specify the string format for TimeStampLTZ data types in the TimeStampTZFormat binding property under the MlsSettings binding property. If no value is specified for the TimeStampTZFormat binding property, the adapter uses the MLS settings for the ODP.NET client on the same computer where the adapter is installed.
TimeStampTZxsd:string

xsd:dateTime if inside an UDT
String

DateTime if inside an UDT
Outside an UDT: The value should be expressed in Oracle NLS_TIMESTAMP_TZ_FORMAT. You can specify the string format for TimeStampTZ data types in the TimeStampTZFormat binding property under the MlsSettings binding property. If no value is specified for the TimeStampTZFormat binding property, the adapter uses the MLS settings for the ODP.NET client on the same computer where the adapter is installed.
Decimal**xsd:decimal if prec <=28

xsd:string if prec > 28
Decimal

String
-
Varchar2xsd:stringString-
Binary Float**xsd:float if prec <=7

xsd:string if prec > 7
Float

String
You must specify the value consistent with the format specified for the decimal character and group separator in the NumericCharacters binding property under the MlsSettings binding property. If no value is specified for the NumericCharacters binding property, the adapter uses the MLS settings for the ODP.NET client on the same computer where the adapter is installed.
Binary Double**xsd:double if prec <=15

xsd:string if prec > 15
Double

String
-
Binary Integer**xsd:integerInt32
Booleanxsd:booleanNullable boolean
XMLTYPExsd:stringStringSupported 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.

System_CAPS_ICON_important.jpg Important

  • The maximum length of the value in an Oracle data type in the Oracle E-Business adapter is bound by the maximum length of the value supported by ODP.NET for the Oracle data type.
  • The Oracle E-Business adapter internally treats the Oracle numeric data types inside UDTs as .NET Decimal. However, in general (that is outside UDTs), the Oracle E-Business 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.

System_CAPS_ICON_note.jpg Note

Oracle data types that are not in this table are surfaced in the same way whether safe typing is enabled or disabled.

Oracle Data TypeXSD type.NET typeComment
Datexsd:stringStringThe value should be expressed in Oracle NLS_DATE_FORMAT. You can specify the format for the Date data types in the DateFormat binding property under the MlsSettings binding property. If no value is specified for the DateFormat binding property, the adapter uses the MLS settings for the ODP.NET client on the same computer where the adapter is installed.
TimeStampxsd:stringStringThe value should be expressed in Oracle NLS_TIMESTAMP_FORMAT. You can specify the string format for TimeStamp data types in the TimeStampFormat binding property under the MlsSettings binding property. If no value is specified for the TimeStampFormat binding property, the adapter uses the MLS settings for the ODP.NET client on the same computer where the adapter is installed.
System_CAPS_ICON_important.jpg Important

If safe typing is enabled, the Oracle numeric data types inside DataSets and weakly-typed REF CURSORS are always exposed as strings.

Validation

The Oracle E-Business adapter 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.

Technical reference
Messages and Message Schemas for BizTalk Adapter for Oracle E-Business Suite

Show: