Inserting Data Into Tables

Learn how to insert data into Oracle NoSQL Database Cloud Service tables from the NoSQL console.

The NoSQL console lets you insert new rows into the Oracle NoSQL Database Cloud Service tables in two modes:
  1. Simple Input Mode: You can use this mode to provide the values for the new rows declaratively.
  2. Advanced JSON Input Mode: You can use this mode to provide the values for the new rows in JSON format.

You can also bulk upload the data from a local file into the table, via the browser.

Inserting Data Into Tables: Simple Input Mode

Learn how to insert data into Oracle NoSQL Database Cloud Service tables by using the Simple Input insertion mode.

To insert data into tables:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
  2. The NoSQL console lists all the tables in the tenancy. To view table details, do either of the following:
    • Click the table name, or
    • Click the action menu corresponding to the table name and select View Details.
    The Table Details page opens up.
  3. Click Insert Row.
  4. In the Insert Row window, select Simple Input for Entry Mode.
  5. All the columns in the table are listed. Input the data for the columns of the table. For some column types, such as Binary, you upload the data.

    Note:

    Entering a value is mandatory for all non-nullable columns of the table.
  6. Click Insert Row.
    The record is inserted into the table.
To view help for the current page, click the help link at the top of the page.

Inserting Data Into Tables: Advanced JSON Input Mode

Learn how to insert data into Oracle NoSQL Database Cloud Service tables by using the Advanced JSON input mode.

To insert data into tables:
  1. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
  2. The NoSQL console lists all the tables in the tenancy. To view table details, do either of the following:
    • Click the table name, or
    • Click the action menu corresponding to the table name and select View Details.
    The Table Details page opens up.
  3. Click Insert Row.
  4. In the Insert Record window, select Advanced JSON Input for Entry Mode.
  5. Paste or upload the Record Definition in JSON format.
  6. Click Insert Row.
    The record is inserted into the table.
To view help for the current page, click the help link at the top of the page.

Bulk Upload of table rows

The Upload Data button in the Table details page allows bulk uploading of data from a local file into the table, via the browser.

The Bulk upload feature is intended for loading less than a few thousand rows. This feature is great for performing a proof of concept ( POC) or doing basic testing of the service. It is a convenient way to populate a small table. If you want to write tens of thousands of rows, then for performance reasons you would be better off using the Oracle NoSQL Database Migrator or writing your own program using one of the NoSQL SDKs. If, however, you want to quickly insert a few hundred or a few thousand rows, this upload method is an expeditious approach.

The file to be uploaded must contain a series of JSON objects. The objects can be expressed as comma-separated items of a single array or as a sequence of simple objects bounded by curly braces, with no syntactic delimiters between them. The contents of each object must be correctly formatted JSON and must conform to the schema of the table to which they will be uploaded.

Example: A table is created using the following DDL statement
CREATE TABLE Simple ( id integer, val string, PRIMARY KEY ( id ) )
The following example illustrates using the array format for the file content.

[
  {
    "id": 0,
    "val": "0"
  },
  {
    "id": 1,
    "val": "2"
  }, ...
]
The following example illustrates using simple objects for the file content.

{
  "id": 0,
  "val": "0"
}
{
  "id": 1,
  "val": "2"
}, ...
  • If a column value is not required by the table's schema, then the corresponding JSON property may be left out.
  • If a column value is GENERATED ALWAYS, then the corresponding JSON property must be left out.
  • If a JSON object contains properties with names that do not match any column names, those properties are ignored.

To use the upload feature, click the Upload Data button and select the file to be uploaded. The upload begins immediately, and progress will be shown on the page. Upon successful completion, the total number of rows inserted will be shown. You can interrupt the upload by clicking the Stop Uploading button. The number of rows that were successfully committed to the database will be shown.

If an error in the input file is detected, then uploading will stop and an error message with an approximate line number will be shown. Input errors might be caused by incorrect JSON syntax or schema nonconformance. Errors can also occur during requests for the service. Such errors also stop the uploading and display a message.

If the upload is stopped in the middle for any reason, you can do one of the following:
  • If there are no columns with generated key values (that is, if the keys are entirely dictated by the JSON file), then you can simply start over with the same file. The already-written rows will be written again.
  • If there are generated key values, then starting over would write new records instead of overwriting existing records. The easiest path would be to drop the table and create it again.
  • Alternatively, you could remove all records from the table by executing the statement DELETE FROM tablename in the Explore data form.

If the provisioned write limit is exceeded during the upload process, a transient message indicating so will be displayed, and the uploading will be slowed down to avoid exceeding the limit again.