This chapter provides information about the native RDBMS Data Type support and XQuery mappings that the Oracle XQuery engine generates or supports.
This chapter includes the following sections:
Core RDBMS Data Type Mapping:
For information about using these mappings in Oracle Service Bus XQueries, see Section 37.9, "Accessing Databases Using XQuery."
For complete information about database and JDBC drivers support in Oracle Service Bus, see Oracle Fusion Middleware Supported System Configurations at:
http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html
This section identifies the data type mappings that the XQuery engine generates or supports for IBM DB2/NT 8.
Table H-1 IBM DB2 Data Type Mappings
| DB2 Data Type | XQuery Type |
|---|---|
|
BIGINT |
xs:long |
|
BLOB |
xs:hexBinary |
|
CHAR |
xs:string |
|
CHAR() FOR BIT DATA |
xs:hexBinary |
|
CLOBFoot 1 |
xs:string |
|
DATE |
xs:date |
|
DOUBLE |
xs:double |
|
DECIMAL(p,s)Foot 2 (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s = 0) |
|
INTEGER |
xs:int |
|
LONG VARCHAR1 |
xs:string |
|
LONG VARCHAR FOR BIT DATA |
xs:hexBinary |
|
REAL |
xs:float |
|
SMALLINT |
xs:short |
|
TIMEFoot 3 |
xs:timeFoot 4 |
|
TIMESTAMPFoot 5 |
xs:dateTimeFootref 4 |
|
VARCHAR |
xs:stringFootref 4 |
|
VARCHAR() FOR BIT DATA |
xs:hexBinary |
Footnote 1 Pushed down in project list only.
Footnote 2 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
Footnote 3 Accurate to 1 second.
Footnote 4 Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations.
Footnote 5 Precision limited to milliseconds.
This section identifies the data type mappings that the XQuery engine generates or supports for Microsoft SQL Server.
Table H-2 SQL Server 2000 Data Type Mapping
| SQL Data Type | XQuery Type |
|---|---|
|
BIGINT |
xs:long |
|
BINARY |
xs:hexBinary |
|
BIT |
xs:boolean |
|
CHAR |
xs:string |
|
DATETIMEFoot 1 |
xs:dateTimeFoot 2 |
|
DECIMAL(p,s)Foot 3 (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s = 0) |
|
FLOAT |
xs:double |
|
IMAGE |
xs:hexBinary |
|
INTEGER |
xs:int |
|
MONEY |
xs:decimal |
|
NCHAR |
xs:string |
|
NTEXTFoot 4 |
xs:string |
|
NVARCHAR |
xs:string |
|
REAL |
xs:float |
|
SMALLDATETIMEFoot 5 |
xs:dateTime |
|
SMALLINT |
xs:short |
|
SMALLMONEY |
xs:decimal |
|
SQL_VARIANT |
xs:string |
|
TEXT4 |
xs:string |
|
TIMESTAMP |
xs:hexBinary |
|
TINYINT |
xs:short |
|
VARBINARY |
xs:hexBinary |
|
VARCHAR |
xs:string |
|
UNIQUIDENTIFIER |
xs:string |
Footnote 1 Fractional-second-precision up to 3 digits (milliseconds). No timezone.
Footnote 2 Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations.
Footnote 3 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
Footnote 4 Pushed down in project list only.
Footnote 5 Accuracy of 1 minute.
This section identifies the data types that the XQuery engine generates or supports for Oracle 8.1.x (Oracle 8i).
Table H-3 Oracle 8.1.x Data Type Mapping
| Oracle 8 Data Type | XQuery Type |
|---|---|
|
BFILE |
not supported |
|
BLOB |
xs:hexBinary |
|
CHAR |
xs:string |
|
CLOBFoot 1 |
xs:string |
|
DATEFoot 2 |
xs:dateTime |
|
FLOAT |
xs:double |
|
LONG1 |
xs:string |
|
LONG RAW |
xs:hexBinary |
|
NCHAR |
xs:string |
|
NCLOB1 |
xs:string |
|
NUMBER |
xs:double |
|
NUMBER(p,s)Foot 3 |
xs:decimal (if s > 0), xs:integer (if s <=0) |
|
NVARCHAR2 |
xs:string |
|
RAW |
xs:hexBinary |
|
ROWID |
xs:string |
|
UROWID |
xs:string |
Footnote 1 Pushed down in project list only.
Footnote 2 Does not support fractional seconds.
Footnote 3 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
This section identifies the data type and other mappings that the XQuery engine generates or supports for Oracle 9i and Oracle Database 10g. Note that Oracle treats empty strings as NULLs, which deviates from XQuery semantics and may lead to unexpected results for expressions that are pushed down.
Table H-4 Oracle 9i, Oracle Database 10g Data Type Mapping
| Oracle Data Type | XQuery Type |
|---|---|
|
BFILE |
not supported |
|
BLOB |
xs:hexBinary |
|
CHAR |
xs:string |
|
CLOBFoot 1 |
xs:string |
|
DATE |
xs:dateTimeFoot 2 |
|
FLOAT |
xs:double |
|
INTERVAL DAY TO SECOND |
xdt:dayTimeDuration |
|
INTERVAL YEAR TO MONTH |
xdt:yearMonthDuration |
|
LONG1 |
xs:string |
|
LONG RAW |
xs:hexBinary |
|
NCHAR |
xs:string |
|
NCLOB1 |
xs:string |
|
NUMBER |
xs:double |
|
NUMBER(p,s) |
xs:decimal (if s > 0), xs:integer (if s <=0) |
|
NVARCHAR2 |
xs:string |
|
RAW |
xs:hexBinary |
|
ROWID |
xs:string |
|
TIMESTAMP |
xs:dateTimeFoot 3 |
|
TIMESTAMP WITH LOCAL TIMEZONE |
xs:dateTime |
|
TIMESTAMP WITH TIMEZONE |
xs:dateTime |
|
VARCHAR2 |
xs:string |
|
UROWID |
xs:string |
Footnote 1 Pushed down in project list only.
Footnote 2 When SDO stores xs:dateTime value in Oracle DATE type, it is converted to local time zone and fractional seconds are truncated due to DATE limitations.
Footnote 3 XQuery engine maps XQuery xs:dateTime to either TIMESTAMP or TIMESTAMP WITH TIMEZONE data type, depending on presence of timezone information. Storing xs:dateTime using SDO may result in loss of precision for fractional seconds, depending on the SQL type definition.
This section identifies the data types that the XQuery engine generates or supports for Sybase 12.5.2 (and higher).
Note:
Sybase deviates from XQuery semantics (which ignores empty strings) and treats empty strings as a single-space string.
Table H-5 Sybase 12.5.2 Data Type Mapping
| Sybase Data Type | XQuery Type |
|---|---|
|
BINARY |
xs:hexBinary |
|
BIT |
xs:boolean |
|
CHAR |
xs:string |
|
DATE |
xs:date |
|
DATETIMEFoot 1 |
xs:dateTimeFoot 2 |
|
DECIMAL(p,s)Foot 3 (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s == 0) |
|
DOUBLE PRECISION |
xs:double |
|
FLOAT |
xs:double |
|
IMAGE |
xs:hexBinary |
|
INT (INTEGER) |
xs:int |
|
MONEY |
xs:decimal |
|
NCHAR |
xs:string |
|
NVARCHAR |
xs:string |
|
REAL |
xs:float |
|
SMALLDATETIMEFoot 4 |
xs:dateTime |
|
SMALLINT |
xs:short |
|
SMALLMONEY |
xs:decimal |
|
SYSNAME |
xs:string |
|
TEXTFoot 5 |
xs:string |
|
TIME |
xs:time |
|
TINYINT |
xs:short |
|
VARBINARY |
xs:hexBinary |
|
VARCHAR |
xs:string |
Footnote 1 Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information.
Footnote 2 When SDO stores xs:dateTime value in Oracle DATE type, it is converted to local time zone and fractional seconds are truncated due to DATE limitations.
Footnote 3 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
Footnote 4 Accurate to 1 minute.
Footnote 5 Expressions returning text are pushed down in the project list only.
When mapping SQL to XQuery data types, the XQuery engine first checks the JDBC typecode. If the typecode has a corresponding XQuery type, the XQuery engine uses the matching native type name. If no matching typecode or type name is available, the column is ignored. Table H-6 shows this mapping.
Table H-6 RDBMS Data Type Mapping
| JDBC Data Type | Typecode | XQuery Data Type |
|---|---|---|
|
BIGINT |
-5 |
xs:long |
|
BINARY |
-2 |
xs:string |
|
BIT |
-7 |
xs:boolean |
|
BLOB |
2004 |
xs:hexBinary |
|
BOOLEAN |
16 |
xs:boolean |
|
CHAR |
1 |
xs:string |
|
CLOBFoot 1 |
2005 |
xs:string |
|
DATE |
91 |
xs:dateFoot 2 |
|
DECIMAL (p,s)Foot 3 |
3 |
xs:decimal (if s > 0), xs:integer (if s =0) |
|
DOUBLE |
8 |
xs:double |
|
FLOAT |
6 |
xs:double |
|
INTEGER |
4 |
xs:int |
|
LONGVARBINARY |
-4 |
xs:hexBinary |
|
LONGVARCHAR1 |
-1 |
xs:string |
|
NUMERIC (p,s)Footref 3 |
2 |
xs:decimal (if s > 0), xs:integer (if s =0) |
|
REAL |
7 |
xs:float |
|
SMALLINT |
5 |
xs:short |
|
TIMEFoot 4 |
92 |
xs:time4 |
|
TIMESTAMP4 |
93 |
xs:dateTimeFootref 2 |
|
TINYINT |
-6 |
xs:short |
|
VARBINARY |
-3 |
xs:hexBinary |
|
VARCHAR |
12 |
xs:string |
|
OTHER vendor-specific JDBC type codes |
1111 |
Oracle Service Bus uses native data type name to map to an appropriate XQuery data type. |
Footnote 1 Pushed down in project list only.
Footnote 2 Values converted to local time zone (timezone information removed) due to DATE limitations.
Footnote 3 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
Footnote 4 Precision of underlying RDBMS determines the precision of TIME data type and how much truncation, if any, will occur in translating xs:time to TIME.