1. Developers Guide
  2. Create
  3. Create Database objects

Create Database objects

A database object is any defined object in a database that is used to store or reference data. You use a CREATE command to create a Database object. You can use a database object to hold and manipulate the data.

Creating a namespace

A namespace defines a group of tables, within which all of the table names must be uniquely identified. Namespaces permit you to do table privilege management as a group operation. You can grant authorization permissions to a namespace to determine who can access both the namespace and the tables within it. Namespaces permit tables with the same name to exist in your database store. To access such tables, you can use a fully qualified table name. A fully qualified table name is a table name preceded by its namespaces, followed with a colon (:), such as ns1:table1.

All tables are part of some namespace. There is a default Oracle NoSQL Database namespace, called sysdefault. All tables are assigned to the default sysdefault namespace, until or unless you create other namespaces, and create new tables within them. You can't change an existing table's namespace. Tables in sysdefault namespace do not require a fully qualified name and can work with just the table name.

You can add a new namespace by using the CREATE NAMESPACE statement.
CREATE NAMESPACE [IF NOT EXISTS] namespace_name

Note:

Namespace names starting with sys are reserved. You cannot use the prefix sys for any namespaces.
The following statement defines a namespace named ns1.
CREATE NAMESPACE IF NOT EXISTS ns1

Using APIs to create namespaces:

Use the Oracle NoSQL Database SDK for Java to create a namespace. Download the full code Namespaces.java from the examples here.
private static void createNS(NoSQLHandle handle) throws Exception {
   String createNSDDL = "CREATE NAMESPACE IF NOT EXISTS ns1";
   SystemRequest sysreq = new SystemRequest();
   sysreq.setStatement(createNSDDL.toCharArray());
   SystemResult sysres = handle.systemRequest​(sysreq);
   sysres.waitForCompletion​(handle, 60000,1000);
   System.out.println("Namespace " + nsName + " is created");
}
Use the Oracle NoSQL Database Python SDK to create a namespace. Download the full code Namespaces.py from the examples here.
def create_ns(handle):
   statement = '''CREATE NAMESPACE IF NOT EXISTS ns1'''
   sysreq = SystemRequest().set_statement(statement)
   sys_result = handle.system_request(sysreq)
   sys_result.wait_for_completion(handle, 40000, 3000)
   print('Created namespace: ns1')
Use the Oracle NoSQL Database Go SDK to create a namespace. Download the full code Namespaces.go from the examples here.
func createNS(client *nosqldb.Client, err error)(){
    stmt := fmt.Sprintf("CREATE NAMESPACE IF NOT EXISTS ns1")
    sysReq := &nosqldb.SystemRequest{
		Statement: stmt,
    }
    sysRes, err := client.DoSystemRequest(sysReq)
    _, err = sysRes.WaitForCompletion(client, 60*time.Second, time.Second)
    if err != nil {
        fmt.Printf("Error finishing CREATE NAMESPACE request: %v\n", err)
        return
    }
    fmt.Println("Created Namespace ns1 ")
    return
}
Use the Oracle NoSQL Database Node.js SDK to create a namespace. Download the full code Namespaces.js from the examples here.
async function createNS(handle) {
   const createNS = `CREATE NAMESPACE IF NOT EXISTS ns1`;
   let res = await handle.adminDDL(createNS);
   console.log('Namespace created: ns1' );
}
Use the Oracle NoSQL Database Dotnet SDK to create a namespace. Download the full code Namespaces.cs from the examples here.
private static async Task createNS(NoSQLClient client){
   var sql =
        $@"CREATE NAMESPACE IF NOT EXISTS ns1";
   var adminResult = await client.ExecuteAdminAsync(sql);
   // Wait for the operation completion
   await adminResult.WaitForCompletionAsync();
   Console.WriteLine("  Created namespace ns1");
}

Creating a table

The table is the basic structure to hold user data. You use the CREATE TABLE statement to create a new table in the Oracle NoSQL Database.

Guidelines for creating a table:
  • The table definition must include at least one field definition, and exactly one primary key definition.
  • The field definition specifies the name of the column, its data type, whether the column is nullable or not, an optional default value, whether or not the column is an IDENTITY column , and an optional comment. All fields ( other than the PRIMARY KEY) are nullable by default.
  • The syntax for the primary key specification (key_definition) specifies the primary key columns of the table as an ordered list of field names.
  • The Time-To-Live (TTL) value is used in computing the expiration time of a row. Expired rows are not included in query results and are eventually removed from the table automatically by Oracle NoSQL Database. If you specify a TTL value while creating the table, it applies as the default TTL for every row inserted into this table.
  • You specify the REGIONS clause if the table being created is a Multi-Region table. The REGIONS clause lists all the regions that the table should span.
Example 1: The following CREATE TABLE statement defines a BaggageInfo table that holds baggage information of passengers in an airline system.
CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)
Example 2: The following CREATE TABLE statement defines a stream_acct table that holds data from a TV streaming application.
CREATE TABLE stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON, 
PRIMARY KEY(acct_id)
)
Example 3: The following CREATE TABLE statement defines a stream_acct_new table that holds data from a TV streaming application. The rows of the table expire in 2 days.
CREATE TABLE stream_acct_new(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON, 
PRIMARY KEY(acct_id)) USING TTL 2 days

Using APIs to create tables:

Use the Oracle NoSQL Database SDK for Java to create a table. Download the full code CreateTable.java from the examples here.
private static void createTab(NoSQLHandle handle) throws Exception {
   String createTableDDL = 
   "CREATE TABLE IF NOT EXISTS " + tableName +
                               "(acct_Id INTEGER," +
                               "profile_name STRING," +
                               "account_expiry TIMESTAMP(1) ," +
                               "acct_data JSON, " +
                               "PRIMARY KEY(acct_Id))";

   TableLimits limits = new TableLimits(20, 20, 1);
   TableRequest treq = new TableRequest()
          .setStatement(createTableDDL)
          .setTableLimits(limits);
   TableResult tres = handle.tableRequest(treq);
   /* The request is async,
    * so wait for the table to become active.
    */
    tres.waitForCompletion(handle, 60000,1000); 
    System.out.println("Created Table: " + tableName);
}
Use the Oracle NoSQL Database Python SDK to create a table. Download the full code CreateTable.py from the examples here.
def create_table(handle):
  statement = '''create table if not exists 
              stream_acct (acct_Id INTEGER,
                           profile_name STRING,
                           account_expiry TIMESTAMP(1),
                           acct_data JSON,
                           primary key(acct_Id))'''

    request = TableRequest().set_statement(statement)
                            .set_table_limits(TableLimits(20, 10, 1))

    table_result = handle.do_table_request(request, 40000, 3000)
    table_result.wait_for_completion(handle, 40000, 3000)

    if (table_result.get_state() == State.ACTIVE):
        print('Created table: stream_acct')
    else:
        raise NameError('Table stream_acct is in an unexpected state ' + 
                             str(table_result.get_state()))
Use the Oracle NoSQL Database Go SDK to create a table. Download the full code CreateTable.go from the examples here.
func createTable(client *nosqldb.Client, err error, tableName string)(){
// Creates a table
 stmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s ("+
     	"acct_Id INTEGER," +
     	"profile_name STRING," +
     	"account_expiry TIMESTAMP(1) ," +
     	"acct_data JSON, " +
     	"PRIMARY KEY(acct_Id))",tableName)

 tableReq := &nosqldb.TableRequest{
		Statement: stmt,
		TableLimits: &nosqldb.TableLimits{
			ReadUnits:  20,
			WriteUnits: 20,
			StorageGB:  1,
              },
 }

 tableRes, err := client.DoTableRequest(tableReq)
 if err != nil {
    fmt.Printf("cannot initiate CREATE TABLE request: %v\n", err)
    return
 }
 // The create table request is asynchronous, 
 // wait for table creation to complete.
 _, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)

 if err != nil {
    fmt.Printf("Error finishing CREATE TABLE request: %v\n", err)
    return
 }
 fmt.Println("Created table: ", tableName)
 return
}
Use the Oracle NoSQL Database Node.js SDK to create a table. Download the full code CreateTable.js from the examples here.
async function createTable(handle) {
  const createDDL = `CREATE TABLE IF NOT EXISTS 
                  ${TABLE_NAME} (acct_Id INTEGER,
                                 profile_name STRING,
                                 account_expiry TIMESTAMP(1),
                                 acct_data JSON,
                                 primary key(acct_Id))`;
  let res =  await handle.tableDDL(createDDL, {
             complete: true,
             tableLimits: {
              readUnits: 20,
              writeUnits: 20,
              storageGB: 1
             }
  });
  console.log('Created table: ' + TABLE_NAME);
}
Use the Oracle NoSQL Database Dotnet SDK to create a table. Download the full code CreateTable.cs from the examples here.
private static async Task createTable(NoSQLClient client){
// Create a table
  var sql =
    $@"CREATE TABLE IF NOT EXISTS 
         {TableName}(acct_Id INTEGER,
                     profile_name STRING,
                     account_expiry TIMESTAMP(1),
                     acct_data JSON,
                     primary key(acct_Id))";
  var tableResult = await client.ExecuteTableDDLAsync(sql,
    new TableDDLOptions{TableLimits = new TableLimits(20, 20, 1)});

  // Wait for the operation completion
  await tableResult.WaitForCompletionAsync();
  Console.WriteLine("  Created table: ",tableResult.TableName);  
}

Creating a region

Oracle NoSQL Database supports Multi-Region Architecture in which you can create tables in multiple KVStores and Oracle NoSQL Database will automatically replicate inserts, updates, and deletes in a multi-directional fashion across all regions for which the table spans. Each KVStore cluster in a Multi-Region NoSQL Database setup is called a Region.

Example 1: The following CREATE REGION statement creates a remote region named my_region1. 
CREATE REGION my_region1

In a Multi-Region Oracle NoSQL Database setup, you must define all the remote regions for each local region. For example, if there are three regions in a Multi-Region setup, you must define the other two regions from each participating region. You use the CREATE REGION statement to define remote regions in the Multi-Region Oracle NoSQL Database.

Example 2: Create a table in a region. 

CREATE TABLE stream_acct_region(acct_id INTEGER,
acct_data JSON,
PRIMARY KEY(acct_id)) IN REGIONS my_region1

Note:

The region my_region1 should be set as the local region before creating the table.

Using APIs to create regions:

Use the Oracle NoSQL Database SDK for Java to create a region, and add table to a region. Download the full code Regions.java from the examples here.
/* Create a remote region and a local region*/
private static void crtRegion(NoSQLHandle handle) throws Exception {
    // Create a remote region
    String createRemRegDDL = "CREATE REGION "+ remRegName;
    SystemRequest sysreq1 = new SystemRequest();
    sysreq1.setStatement(createRemRegDDL.toCharArray());
    SystemResult sysres1 = handle.systemRequest​(sysreq1);
    sysres1.waitForCompletion​(handle, 60000,1000);
    System.out.println(" Remote Region " + remRegName + " is created");
    // Create a local region
    String createLocRegDDL = "SET LOCAL REGION "+ localRegName;
    SystemRequest sysreq2 = new SystemRequest();
    sysreq2.setStatement(createLocRegDDL.toCharArray());
    SystemResult sysres2 = handle.systemRequest​(sysreq2);
    sysres2.waitForCompletion​(handle, 60000,1000);
    System.out.println(" Local Region " + localRegName + " is created");
}

/**
 * Create a table and add the table in a region
 */
private static void crtTabInRegion(NoSQLHandle handle) throws Exception {
    String createTableDDL = "CREATE TABLE IF NOT EXISTS " + tableName +
                                                              "(acct_Id INTEGER," +
                                                              "profile_name STRING," +
                                                              "account_expiry TIMESTAMP(1) ," +
                                                              "acct_data JSON, " +
                                                              "PRIMARY KEY(acct_Id)) IN REGIONS FRA";

    TableLimits limits = new TableLimits(20, 20, 1);
    TableRequest treq = new TableRequest()
            .setStatement(createTableDDL).setTableLimits(limits);
    TableResult tres = handle.tableRequest(treq);
    /* The request is async,
     * so wait for the table to become active.
     */
    System.out.println("Table " + tableName + " is active");
}
Use the Oracle NoSQL Database Python SDK to create a region, and add table to a region. Download the full code Regions.py from the examples here.
# create a remote and local region
def create_region(handle):
   #Create a remote region
   statement = '''CREATE REGION LON'''
   sysreq = SystemRequest().set_statement(statement)
   sys_result = handle.system_request(sysreq)
   sys_result.wait_for_completion(handle, 40000, 3000)
   print('Remote region LON is created')
   #Create a local region
   statement1 = '''SET LOCAL REGION FRA'''
   sysreq1 = SystemRequest().set_statement(statement1)
   sys_result1 = handle.system_request(sysreq1)
   sys_result1.wait_for_completion(handle, 40000, 3000)
   print('Local region FRA is created')

#Create a table in the local region
def create_tab_region(handle):
   statement = '''create table if not exists stream_acct (acct_Id INTEGER,
                                                           profile_name STRING,
                                                           account_expiry TIMESTAMP(1),
                                                           acct_data JSON,
                                                           primary key(acct_Id)) IN REGIONS FRA'''
   request = TableRequest().set_statement(statement).set_table_limits(TableLimits(20, 10, 1))
   # Ask the cloud service to create the table, waiting for a total of 40000 milliseconds
   # and polling the service every 3000 milliseconds to see if the table is active
   table_result = handle.do_table_request(request, 40000, 3000)
   table_result.wait_for_completion(handle, 40000, 3000)
   if (table_result.get_state() == State.ACTIVE):
      print('Created table: stream_acct')
   else:
      raise NameError('Table stream_acct is in an unexpected state ' + str(table_result.get_state()))
Use the Oracle NoSQL Database Go SDK to create a region, and add table to a region. Download the full code Regions.go from the examples here.
//Creates a remote and a local region
func crtRegion(client *nosqldb.Client, err error)(){
   // Create a remote region
   stmt := fmt.Sprintf("CREATE REGION LON")
   sysReq := &nosqldb.SystemRequest{
	Statement: stmt,
   }
   sysRes, err := client.DoSystemRequest(sysReq)
   _, err = sysRes.WaitForCompletion(client, 60*time.Second, time.Second)
   if err != nil {
      fmt.Printf("Error finishing CREATE REGION request: %v\n", err)
      return
   }
   fmt.Println("Created REGION LON ")
   // Create a local region
   stmt1 := fmt.Sprintf("SET LOCAL REGION FRA")
   sysReq1 := &nosqldb.SystemRequest{
		Statement: stmt1,
   }
   sysRes1, err1 := client.DoSystemRequest(sysReq1)
   _, err1 = sysRes1.WaitForCompletion(client, 60*time.Second, time.Second)
   if err1 != nil {
      fmt.Printf("Error finishing CREATE REGION request: %v\n", err)
      return
   }
   fmt.Println("Created REGION FRA ")
   return
}

//creates a table in a specific region
func crtTabInRegion(client *nosqldb.Client, err error, tableName string)(){
   stmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s ("+
		"acct_Id INTEGER," +
		"profile_name STRING," +
		"account_expiry TIMESTAMP(1) ," +
		"acct_data JSON, " +
		"PRIMARY KEY(acct_Id)) IN REGIONS FRA",tableName)
   tableReq := &nosqldb.TableRequest{
		Statement: stmt,
		TableLimits: &nosqldb.TableLimits{
			ReadUnits:  20,
			WriteUnits: 20,
			StorageGB:  1,
             },
   }
   tableRes, err := client.DoTableRequest(tableReq)
   if err != nil {
      fmt.Printf("cannot initiate CREATE TABLE request: %v\n", err)
      return
   }
   // The create table request is asynchronous, wait for table creation to complete.
   _, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
   if err != nil {
      fmt.Printf("Error finishing CREATE TABLE request: %v\n", err)
      return
   }
   fmt.Println("Created table ", tableName)
   return
}
Use the Oracle NoSQL Database Node.js SDK to create a region, and add table to a region. Download the full code Regions.js from the examples here.
//creates a remote and a local region
async function createRegion(handle) {
   // Create a remote region
   const crtRemReg = `CREATE REGION LON`;
   let res = await handle.adminDDL(crtRemReg);
   console.log('Remote region created: LON' );
   // Create a local region
   const crtLocalReg = `SET LOCAL REGION FRA`;
   let res1 = await handle.adminDDL(crtLocalReg);
   console.log('Local region created: FRA' );
}

//creates a table in a given region
async function crtTabInRegion(handle) {
   const createDDL = `CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (acct_Id INTEGER,
                                                                 profile_name STRING,
                                                                 account_expiry TIMESTAMP(1),
                                                                 acct_data JSON,
                                                                 primary key(acct_Id)) IN REGIONS FRA`;
   let res =  await handle.tableDDL(createDDL, {
            complete: true,
            tableLimits: {
                readUnits: 20,
                writeUnits: 20,
                storageGB: 1
            }
   });
   console.log('Table created: ' + TABLE_NAME);
}
Use the Oracle NoSQL Database Dotnet SDK to create a region, and add table to a region. Download the full code Regions.cs from the examples here.
private static async Task createRegion(NoSQLClient client){
   // Create a remote region
   var sql = $@"CREATE REGION LON";
   var adminResult = await client.ExecuteAdminAsync(sql);
   // Wait for the operation completion
   await adminResult.WaitForCompletionAsync();
   Console.WriteLine("  Created remote REGION LON");
   // Create a local region
   var sql1 = $@"SET LOCAL REGION FRA";
   var adminResult1 = await client.ExecuteAdminAsync(sql1);
   // Wait for the operation completion
   await adminResult1.WaitForCompletionAsync();
   Console.WriteLine("  Created local REGION FRA");
}

private static async Task createTabInRegion(NoSQLClient client){
   // Create a table
   var sql =
      $@"CREATE TABLE IF NOT EXISTS {TableName}(acct_Id INTEGER,
                                                profile_name STRING,
                                                account_expiry TIMESTAMP(1),
                                                acct_data JSON,
                                                primary key(acct_Id)) IN REGIONS FRA";
   Console.WriteLine("\nCreate table {0}", TableName);
   var tableResult = await client.ExecuteTableDDLAsync(sql,
          new TableDDLOptions{
             TableLimits = new TableLimits(20, 20, 1)
          });
   // Wait for the operation completion
   await tableResult.WaitForCompletionAsync();
   Console.WriteLine("  Table {0} is created",tableResult.TableName);
   Console.WriteLine("  Table state: {0}", tableResult.TableState);
}