Oracle NoSQL Database Cloud Service Reference

Learn about supported data types, DDL statements, Oracle NoSQL Database Cloud Service Service parameters and metrics.

This article has the following topics:

Supported Data Types

Oracle NoSQL Database Cloud Service supports many common data types.

Data Type Description

BINARY

A sequence of zero or more bytes.

FIXED_BINARY A fixed-size byte array.

BOOLEAN

A data type with one of two possible values: TRUE or FALSE.

DOUBLE

A 64 bit (8 bytes) long floating-point number.

FLOAT

A 32 bit (4 bytes) long floating point number

LONG

A 64 bit ( 8 bytes) long integer number.

INTEGER

A 32 bit (4 bytes) long integer number.

STRING

A sequence of Unicode characters.

NUMBER

An arbitrary-precision signed decimal number.

TIMESTAMP

A point in time with a precision. The precision affects the storage size and usage. Timestamp is stored and managed in UTC (Coordinated Universal Time).

ENUM

An enumeration, represented as an array of strings. ENUM values are symbolic identifiers (tokens) and are stored as a small integer value representing an ordered position in the enumeration.

ARRAY

An ordered collection of zero of more typed items. Arrays that are not defined as JSON cannot contain NULL values.

Arrays declared as JSON can contain any valid JSON, including the special value, null, which is relevant to JSON.

MAP

An unordered collection of zero or more key-item pairs, where all keys are strings and all items are the same type. All keys must be unique. The key-item pairs are called fields, the keys are field names, and the associated items are field values. Field values can have different types, but maps cannot contain NULL field values.

RECORD

A fixed collection of one or more key-item pairs, where all keys are strings. All keys in a record must be unique.

JSON

Any valid JSON data.

Table States and Life Cycles

Learn about the different table states and their significance (table life cycle process).

Each table passes through a series of different states from table creation to deletion (drop). For example, a table in the DROPPING state cannot proceed to the ACTIVE state, while a table in the ACTIVE state can change to the UPDATING state. You can track the different table states by monitoring the table life cycle. This section describes the various table states.

Description of table-state.png follows
Description of the illustration table-state.png

Table State Description

CREATING

The table is in the process of being created. It is not ready to use.

UPDATING

Updating the table is in process. Further table modifications are not possible while the table is in this state.

A table is in the UPDATING state when:

  • The table limits are being changed
  • The table schema is evolving
  • Adding or dropping a table index

ACTIVE

The table can be used in the current state. The table may have been recently created, or modified, but the table state is now stable.

DROPPING

The table is being dropped and cannot be accessed for any purpose.

DROPPED

The table has been dropped and no longer exists for read, write, or query activities.

Note:

Once dropped, a table with the same name can be created again.

Data Definition Language Reference

Learn how to use DDL in Oracle NoSQL Database Cloud Service.

Use Oracle NoSQL Database Cloud Service DDL to create, alter, and drop tables and indexes.

For information on the syntax of the DDL language, see Table Data Definition Language Guide. This guide documents the DDL language as supported by the on-premises Oracle NoSQL Database product. The Oracle NoSQL Database Cloud Service supports a subset of this functionality and the differences are documented in the DDL Differences in the Cloud section.

Also, each NoSQL <language> driver provides an API to execute a DDL statement. To write your application, see Using APIs to Create Tables and Indexes in Oracle NoSQL Database Cloud Service.

Typical DDL Statements

Few samples of common DDL statements are as follows:

Create Table
CREATE TABLE [IF NOT EXISTS] (
    field-definition, field-definition-2 ...,
    PRIMARY KEY (field-name, field-name-2...),
) [USING TTL ttl]
For example:
CREATE TABLE IF NOT EXISTS audience_info (
    cookie_id LONG,
    ipaddr STRING,
    audience_segment JSON,
    PRIMARY KEY(cookie_id))
Alter Table
ALTER TABLE table-name (ADD field-definition)
ALTER TABLE table-name (DROP field-name)
ALTER TABLE table-name USING TTL ttl 
For example:
ALTER TABLE audience_info USING TTL 7 days
Create Index
CREATE INDEX [IF NOT EXISTS] index-name ON table-name (path_list)
For example:
CREATE INDEX segmentIdx ON audience_info
       (audience_segment.sports_lover AS STRING)
Drop Table
DROP TABLE [IF EXISTS] table-name
For example:
DROP TABLE audience_info

See the reference guides for a complete list:

DDL Differences in the Cloud

The cloud service DDL language differs from what is described in the reference guide in the following way:

Table Names

  • Limited to 256 characters, and are restricted to alphanumeric characters and underscore
  • Must start with a letter
  • Cannot include special characters
  • Child tables are not supported

Unsupported Concepts

  • DESCRIBE and SHOW TABLE statements.
  • Full text indexes
  • User and role management
  • On-premise regions