B Oracle REST Data Services Database Type Mappings

This appendix describes the REST Data Services database type mappings along with the structural database types.

B.1 Oracle Built-in Types

Data Type JSON Data Type REST Version Value Example Description

NUMBER

number

v1

"big" : 1234567890

"bigger" : 1.2345678901e10

Represented with all significant digits. An exponent is used when the number exceeds 10 digits.

RAW

string

Custom

"code" : "SEVMTE8gV09STEQh"

Base64 bit encoding is used

DATE

string

v1.2

"start" : "1995-06-02T04:29:11Z"

Represented using ISO 8601 format in UTC time zone

TIMESTAMP

string

v1.2

when : "1995-06-02T04:29:11.002Z"

Represented using ISO 8601 format in UTC time zone

TIMESTAMP WITH LOCAL TIME ZONE

string

v1.2

"at" : "1995-06-02T04:29:11.002Z"

Represented using ISO 8601 format. The local time zone is converted to UTC time zone as the local time zone specification does not apply for a transfer encoding.

CHAR

string

v1

"message" : "Hello World! "

Represented with trailing spaces. This may be required as padding for PUT or POST methods. For example, "abc ". 

ROWID

string

Custom

"id" : "AAAGq9AAEAAAA0bAAA"

Output as the native Oracle textual representation. For example, equivalent to the following conversion: SELECT ROWIDTOCHAR(id) id FROM DUAL.

UROWID

string

Custom

"uid" : "AAAGq9AAEAAAA0bAAA"

Output as the native Oracle textual representation. For example, equivalent to the following conversion: SELECT CAST(uid as VARCHAR(4000)) id FROM DUAL.

FLOAT

number

v1

*as NUMBER

 

NCHAR

string

v1

"message" : "Hello World! "

Represented using unicode character where the character is not supported by the body character set.

NVARCHAR2

string

v1

"message" : "Hello World!"

Represented using unicode character where the character is not supported by the body character set.

VARCHAR2

string

v1

"message" : "Hello World!"

 

BINARY_FLOAT

number

v1

*as NUMBER

 

BINARY_DOUBLE

number

v1

*as NUMBER

 

TIMESTAMP WITH TIME ZONE

object

v1.2

"event" : "1995-06-02T04:29:11.002Z"
"when" : "1995-06-02T04:29:11.002Z"

Represented using ISO 8601 format in UTC time zone. The value represents the same point in time but the original time zone is lost.

INTERVAL YEAR TO MONTH

object

Custom

"until" : "P-123Y3M"
"until" : "P3M"

Represented using ISO 8601 "Duration" format. Zero duration components are considered optional.

INTERVAL DAY TO SECOND

object

Custom

"until" : "P-5DT3H55M"
"until" : "PT3H55M"

Represented using ISO 8601 "Duration" format. Zero duration components are considered optional

LONG

string

v1

*as VARCHAR

 

LONG RAW

string

Custom

"long_code" : { "SEVMTE8gV09STEQh"

 

BLOB

string

Custom

"bin" : {
"base64_value" : "bGVhc3VyZS4="
}
 
 

CLOB

string

Custom

"text" : {
"value" : "Hello World!
"
}
 

BFILE

Object

Custom

"file" : {
"locator" : "TARGET_DIR",
"filename" : "myfile"
}
 

BOOLEAN

true|false

v1

"right" : true
"wrong" : false
 

B.2 Handling Structural Database Types

This section explains how structural database types are handled.

Object Types

An exception to this is where ORDS has adopted an accepted encoding for an Industry Standard type such as GeoJSON.

Following is a sample code snippet:

"address" : {

"number" : 42,

"street" : "Wallaby Way",

"city" : "Sydney"

}

Inheritance

Object type inheritance is not supported. For marshalling purposes, all object types are treated as if they are left concrete types.

PL/SQL Records

PL/SQL Records are not supported.

VARRAYS

VARRAYS are mapped directly to the JSON array type.

Following is a sample code snippet:

"addresses" : [

{

"__db_type" : "MY_SCHEMA.AUS_ADDRESS",

"number" : 42,

"street" : "Wallaby Way",

"city" : "Sydney"

},

{

"__db_type" : "MY_SCHEMA.UK_ADDRESS"

"number : 1,

"street" : "Oracle Parkway"

"city" : "Reading"

"postcode" : "RG6 1RA"

}

]

Element Inheritance

If the type of a VARRAY element instance is a sub-type of the defined type, then it becomes mandatory to add the __db_type named value, as explained in the object types section.

Associative Arrays

Associative arrays (formally known as PL/SQL table or index-by table) fall into following two categories:
  • Indexed by an integer value: A sparsely populated indexed array. This type of array may not yield a value for a given index. When this type of array is converted to and from JSON, the index is ignored, removing the indexable value gaps. This will have the side-effect that a sparsely populated indexed array that is passed as an IN/OUT parameter through a PL/SQL procedure without any changes, could still appear to have been changed, as the indexable value gaps would have been removed.

    Following is a sample code snippet:

    
    "avg_values" : [
    
    34,
    
    57,
    
    86,
    
    3235
    
    ]
    :
  • Not indexed by an integer value: For example, VARCHAR. This category is rarely used and not supported by the Oracle JDBC API.

B.3 Oracle Geospacial Encoding

Oracle Geospacial types comprises of more than the predefined Oracle Object types. However, recognized JSON encoding call, GeoJSON is used to encode the instance data.

B.4 Enabling Database Mapping Support

This section shows how to enable the extended database mapping support.

To enable the extended database mapping support, the following code snippet must be added to the Oracle REST Data Services defaults.xml file, which is located in the Oracle REST Data Services configuration ords directory:

<entry key="misc.datatypes.enable">true</entry>