Python Developer Track for Oracle JSON and Duality Views - Part 6: Database Actions, GUI for JSON

In this part we are going to install Oracle Rest Data Services (ORDS). ORDS is the HTTPS Web Gateway for your Oracle Database, which includes features such as Oracle Database Actions, Oracle APEX access, REST APIs for your data and databases, Oracle Database API for MongoDB, and much more.

After the installation, we are going to use Database Actions, the GUI that ORDS offers to create and work with JSON documents. Let's install it! If you want to know more about ORDS, you can find it here: https://www.oracle.com/database/technologies/appdev/rest.html

Installing ORDS

As I'm using an Oracle Cloud VM with Oracle Linux, this is very stright forward. If you are not using Oracle Linux, you can go to the ORDS webpage I pointed before and download it. It is very simple to install.

ORDS is a Java application, so we are going to install the GraalVM jdk. If you want to know more about GraalVM, you can find the info here: https://www.graalvm.org/

yum install graalvm-17-jdk.x86_64
yum install ords

In the installation, ORDS is going to create an oracle user. We need to configure, start and stop ORDS via this user. Let's change user to start the configuration:

su - oracle

We need to run the following command to configure it. During the configuration it will ask for some configurations. I'm going to put my output here so it can help you as a reference. We are going to run the installation as SYS user which has the following password: PassworD123##

[oracle@dualityviews opc]$ ords --config /etc/ords/config install

ORDS: Release 23.3 Production on Tue Nov 28 11:15:38 2023

Copyright (c) 2010, 2023, Oracle.

Configuration:
  /etc/ords/config/

The configuration folder /etc/ords/config does not contain any configuration files.

Oracle REST Data Services - Interactive Install

  Enter a number to select the type of installation
    [1] Install or upgrade ORDS in the database only
    [2] Create or update a database pool and install/upgrade ORDS in the database
    [3] Create or update a database pool only
  Choose [2]:
  Enter a number to select the database connection type to use
    [1] Basic (host name, port, service name)
    [2] TNS (TNS alias, TNS directory)
    [3] Custom database URL
  Choose [1]:
  Enter the database host name [localhost]:
  Enter the database listen port [1521]:
  Enter the database service name [orcl]: FREEPDB1
  Provide database user name with administrator privileges.
    Enter the administrator username: SYS
  Enter the database password for SYS AS SYSDBA:
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1521/FREEPDB1

Retrieving information.
  Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]:
  Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]:
  Enter a number to select additional feature(s) to enable:
    [1] Database Actions  (Enables all features)
    [2] REST Enabled SQL and Database API
    [3] REST Enabled SQL
    [4] Database API
    [5] None
  Choose [1]:
  Enter a number to configure and start ORDS in standalone mode
    [1] Configure and start ORDS in standalone mode
    [2] Skip
  Choose [1]:
  Enter a number to select the protocol
    [1] HTTP
    [2] HTTPS
  Choose [1]:
  Enter the HTTP port [8080]:

After the configuration is done, it will automatically start ORDS. You should see something like this at the screen:

Mapped local pools from /etc/ords/config/databases:
  /ords/                              => default                        => VALID


2023-11-28T11:17:10.737Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 23.3.0.r2891830
Oracle REST Data Services server info: jetty/10.0.17
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 17.0.9+11-LTS-jvmci-23.0-b21

Right now we are serving ORDS via web, but the port is blocked. Let's do some extra configuration before we start it again. For that, run ctrl + c to stop serving the ORDS.

ctrl + c

First, let's open the port 8080 where ORDS is listening. Let's do this operation as root user, so we need to exit from oracle user first:

exit
firewall-cmd --permanent --zone=public --add-port=8080/tcp
firewall-cmd --reload

Now we need to enable our "myapp" user to access via web. All users are blocked by default for security reasons. We need to enable the schema running the following command as SYS user:

sqlplus sys/PassworD123##@localhost:1521/FREEPDB1 as sysdba

grant inherit privileges on user SYS to ORDS_METADATA;
BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'MYAPP',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'myapp',
    p_auto_rest_auth      => FALSE
  );
  COMMIT;
END;
/

exit;

We will take advantage and we are going to configure ORDS for a later exercise. We are going to enable the MongoDB API so we can use MongoDB code against our JSON data. We connect as oracle user again and we enable this feature:

su - oracle
ords config set mongo.enabled true

We are done! Now let's tart ORDS. We are going to start it as a background process and we are going to save the output in a custom log so we can have a look if something goes wrong:

cd /tmp
nohup ords serve >> ords_log.log 2>&1 &

Using Database Actions

To connect to ORDS, this is the url you have to use: http://YOUR_PUBLIC_IP:8080/ords/sql-developer You will see the following:

Now you can login with the user:myapp and password: PassworD123##

Let's use Database Actions to create collections and insert JSON data. Click on the JSON menu:

Click on the button to create a new collection:

Let's call it restaurant_bookings and click create:

Now let's insert some documents. Click on the icon to insert documents:

Let's insert our first JSON document. Copy and paste this document and click create:

{
    "guest_email": "olivia.johnson@example.com",
    "board":"full",
    "included_in_reservation": true ,
    "allergens": ["gluten", "nuts"]
}

Follow the same process for a second JSON document:

{
    "guest_email": "liam.patel@example.com",
    "board":"half",
    "included_in_reservation": true ,
    "allergens": ["milk", "eggs"]
}

Now let's change the half board to full board. Click on the edit button to our document with half board:

Once it is changed to full, click save:

Now, select hotel_reservation collection. Then click on the diagram button:

We can see the JSON structure for our hotel_reservation collection.

Also you can run queries following the Query By Example (QBE):

In the next chapter we are going to talk about Data Modeling and Duality Views: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-7-data-modeling-including-duality-views-10l3