Using Oracle NoSQL Database From SQL Developer

Overview

This tutorial covers the tasks that you can perform on an Oracle NoSQL Database using Oracle SQL Developer.

Approximately 30 minutes.

In this tutorial, you perform the steps required to configure and use Oracle NoSQL Database with Oracle SQL Developer.

The following is a list of hardware and software requirements:

Before starting this tutorial, you should:

Have access to an instance with Linux OS installed.
Install JDK, Oracle SQL Developer, and Oracle NoSQL Database.
Downloaded the files required for this tutorial to a working directory in the Linux instance.

To create a connection to the Oracle NoSQL Database, perform the following steps:

Confirm that a kvstore is up and running. In this tutorial, kvlite is used.
To start kvlite, enter the following command in a terminal. KVHOME refers to the directory where you have installed Oracle NoSQL Database. This command assumes that Oracle NoSQL Database is installed in the local machine.

java -jar $KVHOME/lib/kvstore.jar kvlite
A single node kvstore instance is created and opened using the default parameter values. Do not close this terminal as it will shut down kvlite.
To verify that kvlite is running, enter the following command in a new terminal.

java -jar $KVHOME/lib/kvstore.jar ping -host localhost -port 5000
The output shows that a kvstore is up and running successfully.
Open SQL Developer.
In the Connections navigator, right-click the Oracle NoSQL Connections node and select New Oracle NoSQL Connection... .
Review the fields. Since, you started kvlite using default values and this store is not secured, you need not change or enter any new values. Accept all the default values and click OK.
View the connection you created by double-clicking the Oracle NoSQL Database Connections node. If you do not see it in the list, click the refresh icon.

You have successfully created a connection to the Oracle NoSQL Database using SQL Developer.

To view the table schema and data existing in the kvstore, perform the following steps:

Expand NoSQL_Connection. You will see a node called Tables. Note that there are no entries under the Tables node. This is because there are no tables in the kvstore kvlite instance you created in the previous topic.
You need to create some tables in the kvstore. You can do this by running the createtables.kvs file from the files folder for this tutorial. Open a terminal and navigate to the directory where the files for this tutorial are located.
Start the data CLI for the kvstore using the following command:

java -jar $KVHOME/lib/kvcli.jar -host localhost -port 5000 -store kvstore
At the kv prompt, execute the script file using the following command:

load -file createtables.kvs
The script is executed successfully.
To verify the created tables enter the following command:

show tables
The tables are listed in an hierarchical order.
Enter exit at the kv prompt to close the data CLI.

You have successfully created some tables in a kvstore.

You will now load some data into the kvstore tables. In a terminal, confirm that you are at the directory containing the files for this tutorial.
Run the load.sh script using the following command:

sh load.sh
The script should run without errors.

You have successfully inserted some data into the tables in a kvstore.

Switch to SQL Developer and click the refresh icon in the Connections navigator.
You should be able to see two tables listed here.
Expand the products table node. You will see nothing listed under it as there are no child tables for the products table.
Expand the user table. You should see some child tables listed for this table.
Expand the accounts table. You should see a nested table listed for this table.
To view the schema definition for a table, double-click the products table.

A tab open at the right side that displays the columns for the selected table.
To view the data present in the tables, click the Data tab. The data present in the products table is displayed.
Double-click the user table.The data for the user table is displayed.
Click the Child Tables tab to view the child tables for the user table.

You have successfully viewed the Oracle NoSQL Database table schema and table data using Oracle SQL Developer.

In this tutorial, you learned to:

To navigate this Oracle by Example tutorial, note the following:

Topic List:: Click a topic to navigate to that section.
Expand All Topics:: Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:: Click the button to show or hide the screenshots. By default, all images are displayed.
Print:: Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.