Extending APEX application with Oracle Digital Assistant

We can extend APEX with a conversational UI which has Natural Language Processing (NLP) capabilities. To achieve this, i have used the Oracle Digital Assistant.

Prerequisites:

In one of my previous posts i showed you how to connect the Digital Assistant to Oracle ATP database using Node libraries and instant client.

In this post, we shall take advantage of Oracle REST Data services (ORDS), where we shall expose the data from the database using REST APIs.

We shall use APEX to build a low code application on Autonomous Oracle database.

We then create a Oracle Digital Assistant skill which shall pick the user intent and pass it as parameters to a Node.js function within a custom component that queries the Oracle database.

USECASE

This is a hypothetical use case. Just to unlock new possibilities with APEX and Oracle Digital Assistant at ACME Recruitment.

ACME Recruitment uses an APEX application to manage all open opportunities and applications in different regions within the different LOBs.

Below is a minimum viable product (MVP) of the APEX application. You can access the live demo here:

Live demo

Username: Recruiter

Password: Innovate@2019!

You can also import the APEX app on your environment.
Download the APEX import file here.

ACME Dashboard
ACME Job Listings

We have extended this application using Oracle Digital Assistant(ODA). This will enhance the user experience of the users searching for open opportunities. Users can search for roles using their normal natural language and ODA will handle the rest using Natural Language Processing (NLP).

ACME Recruitment can take advantage of ODA and extend their APEX application with any of the conversational channels of their choice ie. Web, Facebook, Google Voice, Alexa etc.

I have used a web channel in this demo.

Download the pre-built skill here, feel free to enhance it – it has the custom component embedded in it!

Searching for a job using Oracle Digital Assistant

The High level Architecture:

APEX+ODA Architecture

Oracle Autonomous Database REST API (ORDS)

You will have to expose the data in your APEX application using ORDS to be consumed by the Digital Assistant custom component via REST APIs.

I have done an article on how to create ATP RESTful services. Check it out!

Oracle Digital Assistant custom component

Here your knowledge of creating ODA Custom components will be highly useful!

On the skill, create an ODA “composite bag” entity which shall resolve the user intent of location and job type.

In the yaml flow create a System.ResolveEntities component to pick the user intention. You shall then pass the two variables; jobtype and location to a custom component.

jobsAvailable:     #Ideally should resolve the customer intent 
     component: "System.ResolveEntities"
     properties:
       nlpResultVariable: "iResult"   
       variable: "vacancy"
       maxPrompts: 2
       cancelPolicy: "immediate" 
     transitions:
       actions:
         cancel: "maxError"
         next: "listVacancies"
listVacancies:
     component: "vacancies"
     properties: 
       jobType: "${vacancy.value.BagItemjobType}"
       jobLoc: "${vacancy.value.BagItemlocation}" 
     transitions:
       actions:
        jobAvailablelist: "listVacancies2"
        NotAvailable: "resetVariables"
 

On the custom component, you pick the two variables jobtype and location and run a Node request to the ATP database REST API you had created.

There would be a better way to write the code below, this is just an example 🙂

'use strict';
var request = require('request');
//function to determine jobs in a given category and country
function query(jobt,jobl, callback)
{
  request({ "uri": "https://..../ords/labanish/recruitement/vacancies/"+jobt+"/"+jobl,
      "method": "GET"
    }, function (err, res1, body) {
        body = JSON.parse(body);
        callback(body, err);
  });
}
module.exports = {
  metadata: () => ({
    name: 'vacancies',
    properties: {
      jobType: { required: true, type: 'string' },
      jobLoc : { required: true, type: 'string' }
    },
    supportedActions: ['jobAvailablelist', 'NotAvailable']
  }),
  invoke: (conversation, done) => {
    
    const { jobType } = conversation.properties();
    const { jobLoc } = conversation.properties();
    // determine jobs
    var jobLocUpper = jobLoc.toUpperCase();
console.log(jobType+ " "+jobLocUpper);
 query(jobType,jobLocUpper, function (res1, err){
  var lst = "";
  if (res1 && res1.items)
  {
    if (res1.items.length) 
    {
      for (var i = 0; i < res1.items.length; i++) 
      {
       
        if (i > 0)
          lst += ", "
        lst += res1.items[i].vacancyid +": "+res1.items[i].title;
       
      }
 console.log(lst);
    conversation.variable("jobTypes", lst);
    conversation.transition("jobAvailablelist");
    conversation.keepTurn(true);
    done();
    }
    else
    {
      console.log("No items on the selected category");
      conversation.reply("Unfortunately we do not have any vacancies related to " +jobType.toLowerCase() + " in "+jobLoc);
      conversation.transition("NotAvailable");
      conversation.keepTurn(true);
      done();
    }
  }
  else
  {
    console.log("Encountered an error... please try again later or contact the demo administrator");
    conversation.reply("Encountered an error... please try again later or contact the demo administrator");
    conversation.transition();
    done();
  }
});
  }
};

Once done you pack your custom component for local deployment.

Quick Steps:

The ODA skill I shared with you is linked to my APEX application. You can import the skill and retrain it to test it.

Also visit my APEX application to validate the results. (You can manipulate data in the APEX app, check the authentication credentials above)

In ODA run utterances such as;

Hi…

I am looking for a job in Marketing…

Any IT Jobs in Africa?

What roles do you have in Europe related to technology …

Any marketing roles in ASIA?

Watch the Demo Video:

CONCLUSION

APEX enables a business create low code applications within less time. These applications are secure and can be extended to fit the business requirements.

Oracle Digital Assistant offers Natural Language Processing capabilities which can allow the user to query using their normal natural language as they express their intention. This capability comes in handy while extending Enterprise applications using a conversational UI.

I advise you to get a Oracle Free tier cloud to get started with Oracle cloud.

I welcome feedback from the above post.

Happy coding!

Enable RESTful Services on your Oracle Autonomous Database

You can develop and deploy RESTful Services using the native Oracle REST Data Services (ORDS) support on an Autonomous Transaction Processing database.

This is a great use case in application development to access Oracle Database without using native libraries or instant client.

ORDS is a java mid tier application which maps the HTTP(s) verbs to Oracle database transactions using SQL commands as follows:

  • GET -> SELECT
  • PUT -> UPDATE
  • POST -> CREATE
  • DELETE -> DELETE

You can use SQL developer or APEX to enable REST on your ATP database. I will use Oracle Application Express (APEX) on this blog.

Prerequisites:

Navigate to your Oracle autonomous database on Oracle cloud. Select the service console.

On the dashboard, click on development then select Oracle APEX.

If it’s your first attempt to use APEX, you will have to create a new workspace and username. Then logout from your Oracle ADMIN profile and log in to the APEX using the new credentials created.

APEX workspace has an example RESTful Service Module, oracle.example.hr. You can install the sample service while registering the schema with ORDS to learn more about the functionality.

Once logged in click on SQL Workshop and select RESTful Service. Note that you will have to REST enable your schema. (You can also configure the schema attributes such as Authorization required for metadata access, for the purpose of this demo, I have selected NO)

In my ATP database i have a table called “vacancies” which I want to expose the data using a RESTful service. Find the table DDL script here.

SELECT VACANCYID, TITLE, LOCATION, JOBTYPE FROM VACANCIES

To expose the data using a RESTful service:

Create a new module.

Click on modules and select create module. My module name is called “tutorial

Now that we have the module created, we need to create a template on the under the module.

On the just created module page, locate the “create template” button and create a new template. Give it a name ie. “vacanciesInfo

Now we have a module and a template, now lets create a handler to define the REST methods, ie. GET, POST etc.

Locate the “create Handler” button on the template and create a new handler. Define the handler as shown below.

Restful Data services -> Module -> Template ->Handler

Save or apply changes.

Once done and applied the changes, copy the full url generated and test your RESTful service on a browser. You should get the JSON response of the data from the table in Autonomous database!

That completes the tutorial.

5th May 2020 Update:

What happens if you want to insert into a table in Oracle Database using REST. You use the POST method.

You will create a new handler for POST method, then use an insert PL/SQL code for your table. ie.

begin 

 insert into CASES (countyname, code, casesno, recoveryno, deathsno) values ( :countyname, :code, :casesno, :recoveryno, :deathsno);
 
 :status_code:=201; 
end;
The ORDs handler page on APEX

Test the request on postman;

The record is inserted, Status 201

To explore more on ORDS check out the documentation.

Also check:

Happy coding!