Perform DDL operations using Visual Studio Code

You can use Visual Studio Code to perform DDL operations.

Some of the DDL operations that can be performed from inside the Visual Studio Code plugin are:

• CREATE TABLE
• DROP TABLE
• CREATE INDEX
• DROP INDEX
• ADD COLUMN
• DROP COLUMN

CREATE TABLE

You can create the Oracle NoSQL Database table in two modes:

• Simple DDL Input: You can use this mode to create the Oracle NoSQL Database table declaratively, that is, without writing a DDL statement.
• Advanced DDL Input: You can use this mode to create the Oracle NoSQL Database table using a DDL statement.

1. Hover over the Oracle NoSQL Database connection to add the new table.
2. Click the Plus icon that appears.
3. In the Create Table page, select Simple DDL Input.

   
   
   

   Table 5-9 Create an Oracle NoSQL Database Table

   Field	Description
   Table Name:	Specify a unique table name.
   Column Name	Specify a column name for the primary key in your table.
   Column Type	Select the data type for your primary key column.
   Set as Shard Key	Select this option to set this primary key column as shard key. Shard key is to distribute data across the Oracle NoSQL Database cluster for increased efficiency, and to position records that share the shard key locally for easy reference and access. Records that share the shard key are stored in the same physical location and can be accessed atomically and efficiently.
   Remove	Click this button to delete an existing column.
   + Add Primary Key Column	Click this button to add more columns while creating a composite (multi-column) primary key.
   Column Name	Specify the column name.
   Column Type	Select the data type for your column.
   Default Value	(optional) Specify a default value for the column. Note: Default values can not be specified for binary and JSON data type columns.
   Not Null	Select this option to specify that a column must always have a value.
   Remove	Click this button to delete an existing column.
   + Add Column	Click this button to add more columns.
   Unit	Select the unit (Days or Hours) to use for TTL value for the rows in the table.
   Value	Specify expiration duration for the rows in the table. After the number of days or hours, the rows expire automatically, and are no longer available. The default value is zero, indicating no expiration time. Note: Updating Table Time to Live (TTL) does not change the TTL value of any existing data in the table. The new TTL value applies only to those rows that are added to the table after this value is modified and to the rows for which no overriding row-specific value has been supplied.

4. Click Show DDL to view the DDL statement formed based on the values entered in the fields in the Simple DDL input mode. This DDL statement gets executed when you click Create.
5. Click Create.

DROP TABLE

1. Right-click the target table.
2. Click Drop Table.
3. Click Yes to drop the table.

CREATE INDEX

• Locate the Table Explorer, and click the Refresh Schema to reload the schema.
• Right click on the table where index need to be created. Choose Create Index.
• Specify the name of the index and the columns to be part of the index.
• Click Add Index.

DROP INDEX

• Locate the Table Explorer, and click the Refresh Schema to reload the schema.
• Click on the table where the index needs to be removed. The list of indexes are displayed below the column names.
• Right click on the index to be dropped. Click Drop Index.
• A confirmation window appears, click Ok to confirm the drop action.

ADD COLUMN

• Locate the Table Explorer, and click the Refresh Schema to reload the schema.
• Right click on the table where column needs to be added. Click Add columns.
• Specify the name of the column and define the column with its properties - datatype, default value and whether it is nullable.
• Click Add New Columns.

DROP COLUMN

• Locate the Table Explorer, and click the Refresh Schema to reload the schema.
• Expand the table where column needs to be removed.
• Right click the column to be removed and choose Drop Column.
• A confirmation window appears, click Ok to confirm the drop action.