1 Overview of SODA

Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database.

There are separate SODA implementations for use with different languages and with the representational state transfer (REST) architectural style. SODA for REST can itself be accessed from almost any programming language. It maps SODA operations to Uniform Resource Locator (URL) patterns.


This book describes the features that are present in different SODA implementations. Some features described here may not be available for some implementations. In addition, different implementations can have different ways of providing some of the features. Please refer to the documentation for a particular implementation for detailed information about it.

SODA APIs are document-centric. You can use any SODA implementation to perform create, read, update, and delete (CRUD) operations on documents of nearly any kind (including video, image, sound, and other binary content). You can also use any SODA implementation to query the content of JavaScript Object Notation (JSON) documents using pattern-matching: query-by-example (QBE). CRUD operations can be driven by document keys or by QBEs.

Oracle Database supports storing and querying JSON data natively. SODA document collections are backed by ordinary database tables and views. Because of this, you can take advantage of database features for use with the content of SODA documents.

But you do not need knowledge of SQL, or database administrator (DBA) assistance, to develop or deploy a SODA application. SODA CRUD and query operations are automatically mapped to SQL operations on the underlying database tables or views, and these operations are optimized.

The SQL standard defines a set of SQL/JSON operators that allow direct querying of JSON data. Database views based on these operators provide schema-on-read behavior that is immune to changes in the structure of your documents. If needed, developers with SQL knowledge can use SQL/JSON to perform advanced operations on your SODA data that make full use of the database. For example, a SQL developer can apply database analytics and reporting to your JSON data, and can include it in aggregation and join operations that involve other data. In addition, your SODA applications can use database transactions.

These SODA abstractions hide the complexities of SQL and client programming:

  • Collection

  • Document

A document collection contains documents. Collections are persisted in an Oracle Database schema (also known as a database user). In some SODA implementations a database schema is referred to as a SODA database.

A SODA collection is analogous to an Oracle Database table or view.

SODA is designed primarily for working with JSON documents, but a document can be of any Multipurpose Internet Mail Extensions (MIME) type.

In addition to its content, a document has other document components, including a unique identifier, called its key, a version, a media type (type of content), and the date and time that it was created and last modified. The key is typically assigned by SODA when a document is created, but client-assigned keys can also be used. Besides the content and key (if client-assigned), you can set the media type of a document. The other components are generated and maintained by SODA. All components other than content and key are optional.

A SODA document is analogous to, and is in fact backed by, a row of a database table or view. The row has one column for each document component: key, content, version, and so on.

In addition to the documents it contains, a collection also has associated collection metadata. This specifies various details about the collection, such as its storage, whether it should track version and time-stamp document components, how such components are generated, and whether the collection can contain only JSON documents.

In some contexts collection metadata is represented as a JSON document. This metadata document is sometimes called a collection specification. You can supply a custom collection specification when you create a collection, to provide metadata that differs from that provided by default.

SODA provides CRUD operations on documents. JSON documents can additionally be queried, using query-by-example (QBE) patterns, also known as filter specifications. A filter specification is itself a JSON object.

SODA APIs provide operations for collection management (create, drop, list) and document management (CRUD).

These are some of the actions you can perform using SODA:

  • Create collections

  • Open existing collections

  • Drop collections

  • List all existing collections

  • Create documents

  • Insert documents into a collection

  • Find a document in a collection, by key or by key and version

  • Find all documents in a collection

  • Find documents in a collection, by keys or by QBE

  • Replace (update) a document in a collection, by key or by key and version (optimistic locking)

  • Remove a document from a collection, by key or by key and version (optimistic locking)

  • Remove documents from a collection, by keys or by QBE

  • Index the documents in a collection (to improve query performance)

  • Create a JSON data guide for a collection, which summarizes document structural and type information

Your applications use a database transaction when performing one or more such actions.Foot 1

See Also:

1.1 Overview of SODA Documents

SODA is designed primarily to manipulate JavaScript Object Notation (JSON) documents, that is, documents whose content is JSON data, but other kinds of documents can also be used. A document has other components, besides its content.

Here is a textual representation of the content of a simple JSON document:

{ "name"    : "Alexander",
  "address" : "1234 Main Street",
  "city"    : "Anytown",
  "state"   : "CA",
  "zip"     : "12345" }

You can set the following document components (with an application client, for example):

  • Key

  • Content

  • Media type

In a collection, each document must have a document key, which is unique for the collection. By default, collections are configured to automatically generate document keys for inserted documents. If you want to instead use your own, custom, keys for a collection then you must provide the key for a document when you create it.

The media type specifies the type of content for a document. For JSON documents the media type is "application/json".

The following document components are set and maintained automatically by SODA itself:

  • Version

  • Creation time stamp

  • Last-modified time stamp

A SODA document is an abstract object that encapsulates its components, including its content — it is a carrier of content. A SODA create-document operation creates such a programmatic document object, and a document object is returned by some SODA operations, such as find operations.Foot 2

A document is stored in Oracle Database as a row in a table or view, with each component in its own column.

In a client application, a SODA document is represented in a way that is appropriate to the particular SODA implementation used. For example:

  • In SODA for Java, a document is represented as a Java interface.

  • In SODA for PL/SQL, a document is represented as a PL/SQL object type.

  • In SODA for C, a document is represented as an Oracle Call Interface (OCI) handle.

In all cases, there are methods or functions to create documents and access their components.

To write content to SODA collections and read content from them, you use create-document, write, and read operations:

  • You use a SODA create-document operation to create a document object with content that you provide. (The content can be JSON data or something else.)

  • You use a SODA write operation (such as insert), to store the document persistently in Oracle Database. (The document content is written to a database table.)

  • You use a SODA read operation (such as find), to fetch the document back from Oracle Database. You use specific getter operations to read specific document components (such as key and content).

See Also:

Introducing JSON for general information about JSON

1.2 Overview of SODA Document Collections

A SODA collection is a set of documents that is backed by an Oracle Database table or view.

By default, creating a SODA document collection creates the following in Oracle Database:

  • Persistent default collection metadata.

  • A table for storing the collection, in the database schema to which your SODA client is connected.

All SODA implementations provide a get-metadata operation, which returns the metadata for a collection, represented in JSON. The default collection metadata, which is returned for a default collection, is shown in Example 1-1.

The default metadata specifies a collection that tracks five components for each document: key, content, version, last-modified time stamp, and created-on time stamp. These are specified in JSON by fields keyColumn, contentcolumn, versionColumn, lastModifiedColumn, and creationTimeColumn, respectively. Each of these components is stored in a separate column in the table or view that backs the collection in Oracle Database. The metadata further specifies various details about these components and the database columns that back them.

In Example 1-1, for the key component: the column name is "ID", the column type is "VARCHAR2", the maximum key length is 255, and the key generation method used is "UUID".

In a client application, a document collection is represented in a way that is appropriate to the particular SODA implementation used. For example:

  • In SODA for Java, a collection is represented as a Java interface.

  • In SODA for PL/SQL, a collection is represented as a PL/SQL object type.

  • In SODA for C, a collection is represented as an Oracle Call Interface (OCI) handle.

When a collection is created, the create-collection operation returns a Java or PL/SQL object or an OCI handle, which you can use to perform various collection read and write operations.Foot 3


In the SODA for REST URI syntax, after the version component, you can use custom-actions, metadata-catalog, or a particular collection name. When you use custom-actions or metadata-catalog, the next segment in the URI, if there is one, is a collection name.

Because of this syntax flexibility, you cannot have a collection named either custom-actions or metadata-catalog. An error is raised if you try to create a collection with either of those names using SODA for REST.

In other SODA implementations, besides SODA for REST, nothing prevents you from creating and using a collection named custom-actions or metadata-catalog. But for possible interoperability, best practice calls for not using these names for collections.

When you create a collection you can specify things such as the following:

  • Storage details, such as the name of the table that stores the collection and the names and data types of its columns.

  • The presence or absence of columns for creation time stamp, last-modified time stamp, and version.

  • Whether the collection can store only JSON documents.

  • Methods of document key generation, and whether document keys are client-assigned or generated automatically.

  • Methods of version generation.

This configurability also lets you map a new collection to an existing database table or view.

To configure a collection in a nondefault way, you must define custom collection metadata and pass it to the create-collection operation. This metadata is represented as JSON data.


You can customize collection metadata to obtain different behavior from that provided by default. However, changing some components requires familiarity with Oracle Database concepts, such as SQL data types. Oracle recommends that you do not change such components unless you have a compelling reason. Because SODA collections are implemented on top of Oracle Database tables (or views), many collection configuration components are related to the underlying table configuration.

For example, if you change the content column type from BLOB (the default value) to VARCHAR2 then you must understand the implications (content size for VARCHAR2 is limited to 32K bytes, character-set conversion can take place, and so on).

Reasons you might want to use custom metadata include:

  • To configure SecureFiles LOB storage.

  • To configure a collection to store documents other than JSON (a heterogeneous collection).

  • To map an existing Oracle Database table or view to a new collection.

  • To specify that a collection mapping to an existing table is read-only.

  • To use a VARCHAR2 column for JSON content, and to increase the default maximum length of data allowed in the column.

    You might want to increase the maximum allowed data length if your database is configured with extended data types, which extends the maximum length of these data types to 32767 bytes. For more information about extended data types, see Oracle Database SQL Language Reference.

See Also:

SODA Collection Metadata Components (Reference) for information about the SODA collection metadata components

You can perform read and write operations on a collection only if it is open. Opening a collection amounts to obtaining an object (in Java and PL/SQL) or a handle (in C) that represents the collection. Creating a collection opens it automatically: the create-collection operation returns a collection object or handle. There is also an open operation, to open an existing collection. It too returns a collection object or handle. If you try to create a collection, and a collection with the same name already exists, then that existing collection is simply opened.


By default, the table name for a collection is derived from the collection name, but it can also be explicitly provided in the custom collection metadata that you pass to the create-collection operation. If this table name (derived or explicitly provided) matches an existing table in the currently connected database schema (user), the create-collection operation tries to use that existing table to back the collection.Foot 4

You must therefore ensure that the existing table matches the collection metadata. For example, if the collection metadata specifies that the collection has three columns, for key, content, and version, then the underlying table must have these same columns, and the column types must match those specified in the collection metadata. The create-collection operation performs minimal validation of the existing table, to check that it matches collection metadata. If this check determines that the table and metadata do not match then the create-collection operation raises an error.


Do not use SQL to drop the database table that underlies a collection. Dropping a collection involves more than just dropping its database table. In addition to the documents that are stored in its table, a collection has metadata, which is also persisted in Oracle Database. Dropping the table underlying a collection does not also drop the collection metadata.

Example 1-1 Default Collection Metadata

   "schemaName" : "mySchemaName",
   "tableName" : "myTableName",
   "keyColumn" :
      "name" : "ID",
      "sqlType" : "VARCHAR2",
      "maxLength" : 255,
      "assignmentMethod" : "UUID"
   "contentColumn" :
      "name" : "JSON_DOCUMENT",
      "sqlType" : "BLOB",
      "compress" : "NONE",
      "cache" : true,
      "encrypt" : "NONE",
      "validation" : "STANDARD"
   "versionColumn" :
     "name" : "VERSION",
     "method" : "SHA256"
   "lastModifiedColumn" :
     "name" : "LAST_MODIFIED"
   "creationTimeColumn" :
      "name" : "CREATED_ON"
   "readOnly" : false

1.3 Default Naming of a Collection Table

By default, the name of the database table that underlies a document collection is derived from the collection name.

If you want a different table name from that provided by default then use custom collection metadata to explicitly provide the name.

The default table name is derived from the collection name you provide, as follows:

  1. Each ASCII control character and double quotation mark character (") in the collection name is replaced by an underscore character (_).

  2. If all of the following conditions apply, then all letters in the name are converted to uppercase, to provide the table name. In this case, you need not quote the table name in SQL code; otherwise, you must quote it.

    • The letters in the name are either all lowercase or all uppercase.

    • The name begins with an ASCII letter.

    • Each character in the name is alphanumeric ASCII, an underscore (_), a dollar sign ($), or a number sign (#).


      Oracle recommends that you do not use dollar-sign characters ($) or number-sign characters (#) in Oracle identifier names.

For example:

  • Collection names "col" and "COL" both result in a table named "COL". When used in SQL, the table name is interpreted case-insensitively, so it need not be enclosed in double quotation marks (").

  • Collection name "myCol" results in a table named "myCol". When used in SQL, the table name is interpreted case-sensitively, so it must be enclosed in double quotation marks (").

Related Topics

Footnote Legend

Footnote 1: SODA for REST is an exception in this regard; you cannot use database transactions for its actions.
Footnote 2: Because REST is not a programming language, SODA for REST has no programmatic “object” that represents a document. But SODA for REST operations involve the same concept of a document. For example, when you read a document you obtain a JSON representation of it, which includes all of the components (key, content, version, and so on).
Footnote 3: This is the case only for language-based SODA implementations. In SODA for REST a collection is essentially represented by just a URL.
Footnote 4: SODA for REST is an exception here. for security reasons, in this context an error is raised for SODA for REST, to disallow access to existing tables using REST operations.