Export (0) Print
Expand All

Basic Oracle Data Types

This topic describes how the Microsoft BizTalk Adapter for Oracle Database surfaces basic 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 Working with BizTalk Adapter for Oracle Database Binding Properties.

Dd787930.note(en-US,BTS.10).gifNote
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.

Dd787930.note(en-US,BTS.10).gifNote
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

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

Dd787930.Important(en-US,BTS.10).gifImportant
  • 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.

Dd787930.Important(en-US,BTS.10).gifImportant
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.

Show:
© 2014 Microsoft