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 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
The following table lists the data type mappings that the XQuery engine generates or supports for IBM DB2/NT 8.
Table F-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 |
|
VARCHAR |
xs:stringFootref |
|
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.
The following table lists the data type mappings that the XQuery engine generates or supports for Microsoft SQL Server.
Table F-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 6 |
xs:dateTimeFoot 7 |
|
DECIMAL(p,s)Foot 8 (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 9 |
xs:string |
|
NVARCHAR |
xs:string |
|
REAL |
xs:float |
|
SMALLDATETIMEFoot 10 |
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 6
Fractional-second-precision up to 3 digits (milliseconds). No timezone.
Footnote 7
Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations.
Footnote 8
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 9
Pushed down in project list only.
Footnote 10
Accuracy of 1 minute.
The following table lists the data types that the XQuery engine generates or supports for Oracle 8.1.x (Oracle 8i).
Table F-3 Oracle 8.1.x Data Type Mapping
| Oracle 8 Data Type | XQuery Type |
|---|---|
|
BFILE |
not supported |
|
BLOB |
xs:hexBinary |
|
CHAR |
xs:string |
|
CLOBFoot 11 |
xs:string |
|
DATEFoot 12 |
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 13 |
xs:decimal (if s > 0), xs:integer (if s <=0) |
|
NVARCHAR2 |
xs:string |
|
RAW |
xs:hexBinary |
|
ROWID |
xs:string |
|
UROWID |
xs:string |
Footnote 11
Pushed down in project list only.
Footnote 12
Does not support fractional seconds.
Footnote 13
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).
The following table lists the data type and other mappings that the XQuery engine generates or supports for Oracle Database 9i, 10g, 11g, and 12c. 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 F-4 Oracle 9i and later Data Type Mapping
| Oracle Data Type | XQuery Type |
|---|---|
|
BFILE |
not supported |
|
BLOB |
xs:hexBinary |
|
CHAR |
xs:string |
|
CLOBFoot 14 |
xs:string |
|
DATE |
xs:dateTimeFoot 15 |
|
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 16 |
|
TIMESTAMP WITH LOCAL TIMEZONE |
xs:dateTime |
|
TIMESTAMP WITH TIMEZONE |
xs:dateTime |
|
VARCHAR2 |
xs:string |
|
UROWID |
xs:string |
Footnote 14
Pushed down in project list only.
Footnote 15
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 16
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.
The following table lists 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 F-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 17 |
xs:dateTimeFoot 18 |
|
DECIMAL(p,s)Foot 19 (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 20 |
xs:dateTime |
|
SMALLINT |
xs:short |
|
SMALLMONEY |
xs:decimal |
|
SYSNAME |
xs:string |
|
TEXTFoot 21 |
xs:string |
|
TIME |
xs:time |
|
TINYINT |
xs:short |
|
VARBINARY |
xs:hexBinary |
|
VARCHAR |
xs:string |
Footnote 17
Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information.
Footnote 18
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 19
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 20
Accurate to 1 minute.
Footnote 21
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 F-6 shows this mapping.
Table F-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 22 |
2005 |
xs:string |
|
DATE |
91 |
xs:dateFoot 23 |
|
DECIMAL (p,s)Foot 24 |
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 |
2 |
xs:decimal (if s > 0), xs:integer (if s =0) |
|
REAL |
7 |
xs:float |
|
SMALLINT |
5 |
xs:short |
|
TIMEFoot 25 |
92 |
xs:time4 |
|
TIMESTAMP4 |
93 |
xs:dateTimeFootref |
|
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 22
Pushed down in project list only.
Footnote 23
Values converted to local time zone (timezone information removed) due to DATE limitations.
Footnote 24
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 25
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.