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
This section 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 |
|
CLOB |
xs:string Pushed down in project list only. |
|
DATE |
xs:date |
|
DOUBLE |
xs:double |
|
DECIMAL(p,s) (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s = 0), 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). |
|
INTEGER |
xs:int |
|
LONG VARCHAR1 |
xs:string |
|
LONG VARCHAR FOR BIT DATA |
xs:hexBinary |
|
REAL |
xs:float |
|
SMALLINT |
xs:short |
|
TIME |
xs:time Accurate to one second. Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations. |
|
TIMESTAMP |
xs:dateTime Precision limited to milliseconds. |
|
VARCHAR |
xs:string |
|
VARCHAR() FOR BIT DATA |
xs:hexBinary |
This section 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 |
|
DATETIME |
xs:dateTime Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. Fractional-second-precision up to 3 digits (milliseconds). No timezone. |
|
DECIMAL(p,s) (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s = 0), 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). |
|
FLOAT |
xs:double |
|
IMAGE |
xs:hexBinary |
|
INTEGER |
xs:int |
|
MONEY |
xs:decimal |
|
NCHAR |
xs:string |
|
NTEXTFoot 1 |
xs:string |
|
NVARCHAR |
xs:string |
|
REAL |
xs:float |
|
SMALLDATETIMEFoot 2 |
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
Pushed down in project list only.
Footnote 2
Accuracy of 1 minute.
This section 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 |
|
CLOB |
xs:string Pushed down in project list only. |
|
DATE |
xs:dateTime Does not support fractional seconds. |
|
FLOAT |
xs:double |
|
LONG |
xs:string Does not support fractional seconds. |
|
LONG RAW |
xs:hexBinary |
|
NCHAR |
xs:string |
|
NCLOB |
xs:string Does not support fractional seconds. |
|
NUMBER |
xs:double |
|
NUMBER(p,s) |
xs:decimal (if s > 0), xs:integer (if s <=0), 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). |
|
NVARCHAR2 |
xs:string |
|
RAW |
xs:hexBinary |
|
ROWID |
xs:string |
|
UROWID |
xs:string |
This section 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 |
|
CLOB |
xs:string Pushed down in project list only. |
|
DATE |
xs:dateTime 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. |
|
FLOAT |
xs:double |
|
INTERVAL DAY TO SECOND |
xdt:dayTimeDuration |
|
INTERVAL YEAR TO MONTH |
xdt:yearMonthDuration |
|
LONG |
xs:string Pushed down in project list only. |
|
LONG RAW |
xs:hexBinary |
|
NCHAR |
xs:string |
|
NCLOB |
xs:string Pushed down in project list only. |
|
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:dateTime 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. |
|
TIMESTAMP WITH LOCAL TIMEZONE |
xs:dateTime |
|
TIMESTAMP WITH TIMEZONE |
xs:dateTime |
|
VARCHAR2 |
xs:string |
|
UROWID |
xs:string |
This section 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 |
|
DATETIME |
xs:dateTime Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information. 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. |
|
DECIMAL(p,s) (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s == 0) 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). |
|
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 |
|
SMALLDATETIME |
xs:dateTime Accurate to 1 minute. |
|
SMALLINT |
xs:short |
|
SMALLMONEY |
xs:decimal |
|
SYSNAME |
xs:string |
|
TEXT |
xs:string Expressions returning text are pushed down in the project list only. |
|
TIME |
xs:time |
|
TINYINT |
xs:short |
|
VARBINARY |
xs:hexBinary |
|
VARCHAR |
xs:string |
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 |
|
CLOB |
2005 |
xs:string Pushed down in project list only. |
|
DATE |
91 |
xs:date Values converted to local time zone (timezone information removed) due to DATE limitations. |
|
DECIMAL (p,s) |
3 |
xs:decimal (if s > 0), xs:integer (if s =0) 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). |
|
DOUBLE |
8 |
xs:double |
|
FLOAT |
6 |
xs:double |
|
INTEGER |
4 |
xs:int |
|
LONGVARBINARY |
-4 |
xs:hexBinary |
|
LONGVARCHAR |
-1 |
xs:string |
|
NUMERIC (p,s) |
2 |
xs:decimal (if s > 0), xs:integer (if s =0) |
|
REAL |
7 |
xs:float |
|
SMALLINT |
5 |
xs:short |
|
TIME |
92 |
xs:time 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. |
|
TIMESTAMP |
93 |
xs:dateTime 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. |
|
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. |