Using Oracle NoSQL Database From SQL Developer

 

Overview

Purpose

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

Time to Complete

Approximately 30 minutes.

Introduction

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

Hardware and Software Requirements

The following is a list of hardware and software requirements:

  • Linux Operating System
  • JDK 8 or above
  • Oracle NoSQL Database 3.2.5
  • Oracle SQL Developer 4.1

Prerequisites

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.
 

Creating a NoSQL Database Connection

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

  1. 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

    alt description here

  2. 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.

    alt description here

  3. 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

    alt description here
  4. The output shows that a kvstore is up and running successfully.
    alt description here

  5. Open SQL Developer.

    alt description here

  6. In the Connections navigator, right-click the Oracle NoSQL Connections node and select New Oracle NoSQL Connection... .

    alt description here

  7. 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.

    alt description here

  8. 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.

    alt description here

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

 

Viewing NoSQL Database Table Schema and Data

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

 

Running a script to create tables in the KVStore

  1. 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.
    alt description here

  2. 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.

    alt description here

  3. Start the data CLI for the kvstore using the following command:

    java -jar $KVHOME/lib/kvcli.jar -host localhost -port 5000 -store kvstore

    alt description here

  4. At the kv prompt, execute the script file using the following command:

    load -file createtables.kvs

    alt description here
  5. The script is executed successfully.
    alt description here

  6. To verify the created tables enter the following command:

    show tables

    alt description here
  7. The tables are listed in an hierarchical order.
    alt description here

  8. Enter exit at the kv prompt to close the data CLI.

    alt description here

You have successfully created some tables in a kvstore.

 

Running a script to load data into the KVStore tables

  1. 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.

    alt description here

  2. Run the load.sh script using the following command:

    sh load.sh

    alt description here

  3. The script should run without errors.

    alt description here

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

 

Viewing table schema and data

  1. Switch to SQL Developer and click the refresh icon in the Connections navigator.

    alt description here

  2. You should be able to see two tables listed here.

    alt description here

  3. Expand the products table node. You will see nothing listed under it as there are no child tables for the products table.

    alt description here

  4. Expand the user table. You should see some child tables listed for this table.

    alt description here

  5. Expand the accounts table. You should see a nested table listed for this table.

    alt description here

  6. To view the schema definition for a table, double-click the products table.

    alt description here
    A tab open at the right side that displays the columns for the selected table.

  7. To view the data present in the tables, click the Data tab. The data present in the products table is displayed.

    alt description here

  8. Double-click the user table.The data for the user table is displayed.

    alt description here

  9. Click the Child Tables tab to view the child tables for the user table.

    alt description here

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

 

Summary

In this tutorial, you learned to:

  • Start KVlite
  • Create an Oracle NoSQL Database connection in SQL Developer
  • View kvstore tables in SQL Developer
  • View kvstore table data in SQL Developer

Credits

  • Lead Curriculum Developer: Salome Clement
  • Other Contributors: Dave Segleau