Data Type Definitions

The Oracle NoSQL Database data model types inside SQL statements are referred to using type_definition syntax. This syntax is used both in data definition language (DDL) statements and data manipulation language (DML) statements.

Syntax

type_definition ::= 
   INTEGER | 
   LONG | 
   FLOAT | 
   DOUBLE | 
   NUMBER | 
   STRING | 
   BOOLEAN | 
   ANY | 
   JSON | 
   ANYRECORD | 
   ANYATOMIC | 
   ANYJSONATOMIC |
   array_definition | 
   map_definition | 
   binary_definition | 
   timestamp_definition | 
   enum_definition | 
   record_definition 

array_definition ::= ARRAY "(" type_definition ")"
map_definition ::= MAP "(" type_definition ")"
binary_definition ::= BINARY ["(" INT_CONSTANT ")"]
timestamp_definition ::= TIMESTAMP ["(" INT_CONSTANT ")"]

enum_definition ::= ENUM "(" id_list ")"
id_list ::= id ["," id]

record_definition ::= RECORD "(" field_definition ("," field_definition)* ")"
field_definition ::= id type_definition [default_definition] [comment]
default_definition ::= 
   (default_value [NOT NULL]) | (NOT NULL default_value)
default_value ::= DEFAULT (number | string | TRUE | FALSE | id)

Semantics

type_definition

When the type_def grammar rule is used in any DDL statement, the only wildcard type that is allowed is the JSON type. So, for example, it is possible to create a table with a column whose type is JSON, but not a column whose type is ANY.

timestamp_definition

The precision is optional while specifying a TIMESTAMP type. If omitted, the default precision is 9 (nanoseconds). This implies that the type TIMESTAMP (with no precision specified) is a supertype of all other TIMESTAMP types (with a specified precision). However, in the context of a CREATE TABLE statement, a precision must be explicitly specified. This restriction is to prevent users from inadvertently creating TIMESTAMP values with precision 9 (which takes more space) when in reality they don't need that high precision.

record_definition

Field default values and descriptions do not affect the value of a RECORD type, i.e., two RECORD types created according to the above syntax and differing only in their default values and/or field descriptions have the same value (they are essentially the same type).

field_definition

The field_definition rule defines a field of a RECORD type. It specifies the field name, its type, and optionally, a default value and a comment. The comment, if present, is stored persistently as the field's description.

default_definition

By default, all RECORD fields are nullable. The default_definition rule can be used to declare a field not-nullable or to specify a default value for the field. When a record is created, if no value is assigned to a field, the default value is assigned by Oracle NoSQL Database, if a default value has been declared for that field. If not, the field must be nullable, in which case the null value is assigned. Currently, default values are supported only for numeric types, STRING, BOOLEAN, and ENUM.