Oracle NoSQL Database Connector

Oracle NoSQL Database Connector enhances your experience of building an application with -

• Oracle NoSQL Database Cloud Service
• Oracle NoSQL Database on-premises
• Oracle NoSQL Database Cloud Simulator

You can use Oracle NoSQL Database Visual Studio (VS) Code extension to:

• View the tables in a well-defined tree structure with Table Explorer.
• View information on columns, indexes, primary key(s), and shard key(s) for a table.
• View column data in a well-formatted JSON Structure.
• Create tables using form-based schema entry or supply DDL statements.
• Drop tables.
• Add new columns using form-based entry or supply DDL statements.
• Drop Columns.
• Create Indexes.
• Drop Indexes.
• Execute SELECT SQL queries on a table and view query results in tabular format.
• Execute DML statements to update, insert, and delete data from a table.
• Download the Query Result after running the SELECT query into a JSON file.
• Download each row of the result obtained after running the SELECT query into a JSON file.
• View Index DDL.
• Add and store new multiple connections.
• Modify and Delete stored connections.
• Ability to toggle between various stored connections.
• Onprem MR -> Execute DDL statements.
• Cloud MR -> Freeze/Unfreeze schema, Edit reserved capacity, and View, Add and Drop Replicas.
• Auto-Completion on SQL Keywords and Column Names in the browse table query box.
• JSON tree exploration and search.
• SQL Pretty Formatting.
• SQL Syntax highlighting.
• JE Cache Sizing tool.

See [Extension Documentation] (For On-premise: https://docs.oracle.com/en/database/other-databases/nosql-database/24.3/plugins/oracle-nosql-database-visual-studio-code-extension.html For Cloud-service: https://docs.oracle.com/en/cloud/paas/nosql-cloud/aznjq/index.html) for more details.

Getting Started

Connect to your database

1. Click the Oracle NoSQL DB view in the Activity Bar.
2. Click Add Connection to open the Oracle NoSQL DB Show Connection Settings page.
3. 3. In the Connection Settings page, click on Add New Connection to add a connection to either a new Cloud region, Onprem database or CloudSim connection .
4. Enter the connection information and click on Add. This will connect and save the connection details.

Modify the connection

1. In the Oracle NoSQL DB Show Connection Settings page, navigate to a Cloud, Onprem or Cloudsim tab and select the connection to be modified from the dropdown list.
2. Click on Modify Connection.
3. The connection settings page opens below.
4. Make the required changes to the details and click on Save.

Delete the connection

1. In the Oracle NoSQL DB Show Connection Settings page, navigate to a Cloud, Onprem or Cloudsim tab and select the connection to be deleted from the dropdown list.
2. Click on Delete Connection.
3. A confirmation tab opens.
4. Click on Yes to delete the connection.

All Connections

1. Click the Oracle NoSQL DB view in the Activity Bar.
2. Click All Connections to open the Oracle NoSQL DB All Connections page.
3. In the All Connections page, select the connection from the dropdown to connect.
4. Click on Connect.

Manage Tables

Once you connect to your deployment -

• Use the Table Explorer left navigation to explore your tables.

• Click the table name to view its columns, indexes, primary key(s), and shard key(s). The column name displays along with its data type.

You can refresh the schema or table at any time to re-query your deployment and populate Oracle NoSQL Database with the most up-to-date data.

• In the Table Explorer, locate the connection and click the Refresh icon to reload the schema. Alternatively, you can right-click the connection and select Refresh Schema.

• In the Table Explorer, locate the table name and click the Refresh icon to reload the table. Alternatively, you can right-click the table name and select Refresh Table.

Create New Tables

To create a new table:

1. Hover over the database where you want to add the new table.

2. Click the Plus icon that appears.

3. In the prompt, enter the details for your new table.

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

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

4. (Optional) 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 a Table

1. Right-click the target table and select Drop table.
2. A confirmation window appears, click "yes" to drop the table.

Add new Columns to the table

1. Right-click the target table and select Add Columns.

2. In the Add Columns panel, enter the details for adding new columns.

   You can Add new COLUMNs in two modes like CREATE TABLE:

   • Simple DDL Input : You can use this mode to add new columns declaratively, that is, without writing a DDL statement.
   • Advanced DDL Input : You can use this mode to add new columns into the table by supplying a valid DDL statement.

3. Click Add Column(s).

Drop a column

1. Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.
2. Locate the target-column which has to be dropped and right-click on it.
3. Click on Drop Column.
4. A confirmation window appears, click "yes" to drop the column.

Create an Index

• Via Simple Input

1. Right-click the target table and select Create Index.
2. In the Create Index panel, navigate to the "Simple Input" section and enter the details for creating an index declaratively, that is, without writing any DDL statement.
3. Click Create Index.

• Using DDL statement

1. Right-click the target table and select Create Index.
2. In the Create Index panel, navigate to the "Using Advanced DDL" section and enter the DDL statement.
3. Click Create Index.

• Creating index on JSON columns

1. Right-click the target table and select Create Index.
2. In the Create Index panel, navigate to the "Simple Input" section and select the JSON columnName from the dropdown, enter the JSON path to the index field and select the type of the JSON index field or pass the DDL statement via "using Advanced DDL" section.
3. Click Create Index.

Drop an Index

1. Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.
2. Locate the target-index which has to be dropped and right-click on it.
3. Click on Drop Index.
4. A confirmation window appears, click "yes" to drop the index.

Insert a new Row into the table

1. Right-click the target table and select Insert Row.

2. In the Insert Row panel, enter the details for inserting a new row.

   You can INSERT a new ROW in two modes:

   • Simple Input : You can use this mode to insert the new row declaratively, that is, without writing a DML statement.
   • Advanced JSON Input : You can use this mode to insert a new row into the table by supplying a json Object containing the columnName and its corresoponding value as key-value pairs.

3. Click Insert Row.

Execute SQL Queries on a Table

• Right-click the target table and select Browse Table.

• In the SQL window, specify the SELECT query in the text box and click execute button. The query result is displayed in tabular format. You can adjust the number of rows per page.

• To view individual cell data separately, click the table cell.

• To perform DML operations like Update and Delete Row, right-click on the particular row. A context-menu will appear. If you click on:

  • Delete Row : A confirmation window appears, click "yes" to delete the row.
  • Update Row : A separate HTML panel opens below the listed rows, containing the columnNames and its corresponding value in a form-based entry and as a JSON key-pair object. You can choose either of the two methods and supply new values. NOTE: PRIMARY KEY and GENERATED ALWAYS AS IDENTITY columns cannot be update in any rows.
  • Download JSON: Row Object gets downloaded into a JSON file.

Show Query Plan

• Right-click the target table and select Browse Table.
• In the SQL window, specify any query in the text box and click "Show Query Plan" button. The query execution plan will be displayed in a window.

View Table DDL

• Right-click the target table and select View Table DDL.
• A window having the DDL statement of the present status of the table will be displayed.

View Index DDL

• Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.
• Right-click the target Index and select View Index DDL.
• A window having the DDL statement of the Index will be displayed.

Execute DDL - Only for Onprem Connections

1. Click the Oracle NoSQL DB view in the Activity Bar.
2. Execute DDL feature will be only available on the Onprem connections.
3. Click Execute DDL to open the Execute DDL page.
4. Enter the DDL to be executed in the text box and click on Execute.

Edit Reserved Capacity - Only for Cloud Connections

1. Right-click the target table and select Edit Reserved Capacity.

2. This can be done in two modes.

   • Provisioned Capacity mode: Enter the Read, Write Capacity and Disk Storage values to be changed.
   • On Demand Capacity mode: Enter the Disk Storage value to be changed.

Freeze/Unfreeze Schema - Only for Cloud Connections

1. Right-click the target table and select Freeze/Unfreeze schema.
2. If the schema is currently unfrozen, a window will appear to ask the confirmation from user if they need to freeze the schema.
3. If the schema is currently frozen, a window will appear to ask the confirmation from user if they need to unfreeze the schema.
4. User needs to click on "Yes" to proceed.

Replicas - Only for Cloud Connections

Add Replicas - Only for Cloud Connections

1. Right-click the target table and select Add Replicas.
2. In the Add Replica page, select the Replication region from the dropdown.
3. Enter the Read and Write Capacity.
4. Click on Add Replica.

Drop Replicas - Only for Cloud Connections

1. Right-click the target table and select Drop Replicas.
2. In the Drop Replica page, select the Replica name from the dropdown.
3. Multiple Replica(s) (if any) can be selected to drop.
4. Click on Drop Replica.

View Replicas - Only for Cloud Connections

1. Right-click the target table and select View Replicas.
2. List of the Replicas will be displayed.

JE Cache Sizing tool

1. Click the Oracle NoSQL DB view in the Activity Bar.
2. Click JECacheSizing Tool to respective page.
3. Enter Primary Key Size, Row Size, Number of Rows, Index details.
4. Click on Add Table to add the details of a new table.
5. Click on Calculate to calculate and display JE Cache Size, Number of Shards and Storage Size.

SDK

This extension uses Oracle NoSQL Node.js SDK which is included in VSIX package under '/oracle-nosqldb' directory. This directory includes a separate README and CHANGELOG file specific to the SDK.