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!

Update – Building & Debugging Custom Components for the Oracle Digital Assistant in Minutes!

Previously, i had summarized the steps to create and pack custom component to be deployed on the local container in ODA.

As for custom components built for ODA 19.4.1 and beyond, few changes were made. Here are the steps to create a CC.

  1. Globally install the Bots-node-sdk in your computer
npm install -g @oracle/bots-node-sdk

2. Create the custom component folder and cd into it, then;

bots-node-sdk init


3. Package the custom component.

bots-node-sdk pack


How do you debug CC code from your computer before deploying?

To run the code locally, you use;

 npm start

You have to create a tunnel to expose your localhost server to the internet. You can use ngrok or local tunnel to achieve this.

ie. If you use ngrok: >>> $ngrok http 3000

Now your url should be like below:

https://<your tunnel host>/components

Navigate to the components section in ODA select + Service button.

Select external as the deployment option as shown below: The username and password you write “none

This image has an empty alt attribute; its file name is Cc1-1024x604.png

Once connected you can now debug your code using your favorite code editor.

Happy Coding!

References:

Create an Events Booking Bot on Oracle Autonomous Database – use Alexa as the voice channel

The demo below shows an integration between Oracle Autonomous Database and the Digital assistant, using the Alexa Voice channel.

Follow the blog posts below to learn how this was created. I used instant client and other Node.js libraries to establish a connection to the Oracle Autonomous Database.

Happy Coding!

Connect Oracle Digital Assistant to Oracle ATP Database

From my previous post, i showed you how to connect to Oracle ATP database using Node.js.

We shall take advantage of this capability and build a custom  component to connect a digital assistant skill to the ATP database.

Prerequisites:

Use Case

I created a simple Event booking skill which run queries on ATP database.

If you managed to connect to ATP database using Node.js then you are up to this task also!

My pseudo persona, Jamie Foxx, will use the Oracle digital assistant to book seats on the events near him. This will update the Bookings column on the EVENTS table in ATP. It will also add a new record on the RESERVATIONS table.

On the bot:

oda4

On the ATP database an update is done on the Bookings column of EVENTS table (viewing using SQL Developer):

oda5

A new record is added on the RESERVATIONS table:

oda6

Validation:

You cannot book more than the seats available.

Ex. Jamie Foxx has booked two seats at Jazz Festival, so 198 seats are remaining now, lets try and book 201 seats for the same event  🙂

oda7

How was this done?

Interested? Let’s walk through the simple architecture: 🙂

IMG_4152

Create a Event Skill bot in ODA

My skill has 3 intents; Bookings, Greetings, SearchEvents.

I have implemented a resolveEntities system component to extract event name and number of seats entities from the user input in a composite bag.

metadata:
  platformVersion: "1.0"
main: true
name: LN_Events
#context: Define the variables which will used throughout the dialog flow here.
context:
  variables:
    iResult: "nlpresult"
    variableMakeReservations: "MakeReservations" #Composite bag
    name : "string"
    eventid : "string"
    seatstoReserve : "string"
    paid : "boolean"
    bookings : "string"
    eventname : "string"
    remainingSeats : "string"
    events: "Events"

states:

 ############## Intents #######################

  intent:
    component: "System.Intent"
    properties: 
      variable: "iResult"
    transitions:
      actions:
        Greetings: "startGreeting"
        Bookings: "startBookings"
        SearchEvents: "searchEvents"
        unresolvedIntent: "unresolvedState"
        

#########################################################
        
  startGreeting:
    component: "System.Output"
    properties:
      text: "Hi Jamie Foxx, I am your online Events Booking Bot, i can help you reserve seats on the available events, also trust me, I can facilitate your payments!"
    transitions:
      return: "startGreeting"

#########################################################

  searchEvents:
    component: "System.List"
    properties:
      prompt: "Hi Jamie Foxx, here are the events near your location. Which one would you wish to book?"
      options: "${events.type.enumValues}"
      variable: "events"        
    transitions: 
      next: "setEventName"
 
 ################################
  setEventName:
    component: "System.SetVariable"
    properties:
      variable: "variableMakeReservations.BagEvent"
      value: "${events.value}"

#########################################################      
 
  startBookings:
    component: "System.SetVariable"
    properties:
      variable: "name"
      value: "Jamie Foxx"
    transitions:
      next: "resolveEntities"
        
############# Resolve entities ############################

  resolveEntities:
    component: "System.ResolveEntities"
    properties:
      #transitionAfterMatch: true
      variable: "variableMakeReservations"
      nlpResultVariable: "iResult"      
      maxPrompts: 2
      cancelPolicy: "immediate" 
    transitions:
      actions:
        #match: "" 
        cancel: "maxError"
        next: "submitBooking"      
      

 ######################################################  
 
  submitBooking: #This is the custom component
    component: "makeReservation" 
    properties:
     name : "${name}"
     eventname : "${variableMakeReservations.value.BagEvent}"
     seatstoReserve : "${variableMakeReservations.value.BagNumber.number}"
    transitions:
      next: "endsubmitBooking"      

##########################################################


  endsubmitBooking:
    component: "System.ResetVariables"
    properties:      
      variableList: "iResult,variableMakeReservations,name,eventid,seatstoReserve,eventname,remainingSeats,Events"
    transitions:
      return: "done"


##################################################

  output: #For debuging purposes
    component: "System.Output"
    properties:
      text: "You have reserved ${variableMakeReservations.value.BagNumber.number} seats at ${variableMakeReservations.value.BagEvent}"
    transitions:
      #next: "savetoATP"
      next: "endsubmitBooking"
      
##################################################

  maxError:
    component: "System.Output"
    properties:
      text: "Sorry the input is invalid"
    transitions:
      return: "maxError"

######################################################

  unresolvedState:
    component: "System.Output"
    properties:
      text: "Sorry,i didn't quite get that, could you try again?"
      keepTurn: false
    transitions:
      return: "unresolvedState"     

You can download the skill here.

Create a Custom Component

From the YAML file above, we can see that we have a custom component called “makeReservation

I scripted an ODA custom component  running on a Node.js server to get the inputs from the skill and perform CRUD operations on the ATP database.

Here is my custom component, named makeReservation.js I have tried to comment on my code to make it easier to understand 🙂

The inputs from the Event skill custom component includes; name, eventname, seatstoReserve.

After the logic the response is sent back to the bot using the “sdk.reply(message);”

require ('custom-env').env('stagging'); //find the .env.stagging file and place the right location of your wallet

var async = require('async');

var oracledb = require('oracledb');

var dbConfig = require('./../dbconfig.js');

//variables

let name;

let eventid;

let seatstoReserve;

let paid = 'No';

let bookings;

let eventname;

let remainingSeats;

let message;

module.exports = {

metadata: function metadata() {

return {

"name": "makeReservation",

"channels": {

"facebook": "1.0",

"webhook": "1.0"

},

"properties": {

"name": {"type":"string", "required": true},

"eventname": {"type":"string", "required": true},

"seatstoReserve": {"type":"string", "required": true}

},

"supportedActions": []

};

},

invoke: function invoke(sdk, done) {

    

//console.log('Check User Payload: ' + JSON.stringify(sdk.payload()));

name = sdk.properties().name;

eventname = sdk.properties().eventname;

seatstoReserve = parseInt(sdk.properties().seatstoReserve);

console.log("DATA: " + eventname +": "+ seatstoReserve);




////Start Functions

var doconnect = function(cb) {

oracledb.getConnection({

user: dbConfig.dbuser,

password: dbConfig.dbpassword,

connectString: dbConfig.connectString

},

cb);

};

var dorelease = function(conn) {

conn.close(function (err) {

if (err)

console.error(err.message);

});

};

var doCheckAvailability = function(conn, cb){

console.log(`check availability at ${eventname}`);

conn.execute(

`SELECT EVENTID, BOOKINGS, (SEATS - BOOKINGS) AS REMAININGSEATS FROM EVENTS WHERE eventname = :v`,

[eventname],

function(err, result)

{

if (err) { console.error(err); return cb(err, conn); }

console.log(JSON.stringify(result.rows));




eventid = JSON.stringify(result.rows[0][0]);

remainingSeats = JSON.stringify(result.rows[0][2]);




bookings= JSON.stringify(result.rows[0][1]);

console.log('Event ID: '+ eventid);

console.log('Event Name: '+ eventname);

console.log('Bookings: '+ bookings);

console.log('Remaining Seats: '+ remainingSeats);

return cb(null, conn);




});

}

var doinsert = function (conn, cb) {




//before you add a reservation, check if there are seats available

if (parseInt(seatstoReserve) > parseInt(remainingSeats)){

message = `Sorry, you can't book ${seatstoReserve} seats at ${eventname}. Unfortunately we do not have enough seats available... the available seats remaining are ${remainingSeats}`;

console.log(`From console: log Sorry, you can't book ${seatstoReserve} seats at ${eventname}. No enough seats available... the available seats are ${remainingSeats}`);




dorelease(conn);

//return false;

}else{

var data = [name,eventid,paid,seatstoReserve]

conn.executeMany(

"INSERT INTO RESERVATIONS VALUES (:1, :2, :3, :4)",

[data], // bind the JSON string for inserting into the JSON column.

{ autoCommit: true },

function(err) {

if (err) {

return cb(err, conn);

} else {

//console.log("Data Inserted");

message ="Reservation done succesifully for: "+ eventname +": "+ seatstoReserve+" seat(s)";

//return cb(null, conn);

}

});




}

return cb(null, conn);

};

var doUpdateBooking = function(conn, cb){

bookings = parseInt(bookings) + parseInt(seatstoReserve);

var Updatedata = [bookings, eventid]

console.log(Updatedata);

conn.executeMany(

`UPDATE EVENTS SET BOOKINGS = :1 WHERE EVENTID = :2`, // WHERE eventid =21

[Updatedata],

{ autoCommit: true },

function(err) {

if (err) {

return cb(err, conn);

} else {

console.log("Data Updated!");

return cb(null, conn);

}

} );

};

////Run the logic here using waterfall

async.waterfall(

[

doconnect,// Does the connection -Very key!

doCheckAvailability, //Check if seats are available

doinsert, //Add reservation

doUpdateBooking //Update the bookings in Events table

],

function (err, conn) {

if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }

if (conn)

dorelease(conn);




//reply to the bot!

sdk.reply(message);

sdk.transition("endsubmitBooking");

sdk.keepTurn(true);

done();

});




    }

};

From the code above, you need to define dbCofig.js -where you store you connection details (Just like from my previous post) and then define environment variables on a .env file.

The dbconfig.js file:
module.exports= {

dbuser: 'admin',

dbpassword: 'Your Password',

connectString: 'YourDatabaseName_TP'

}

On my .env.stagging file indicates where you have stored your ATP connection wallet.

TNS_ADMIN='c:\wallets'

On my main file which is event_api.js you need to spin up the Node server using express() Node library:

require ('custom-env').env('stagging') //either stagging or production

const apiURL = '/mobile/atp/components';

// Reference component shell

var shell = require('./shell')();

const express = require('express')

const bodyParser = require('body-parser')

const service = express()

const request = require('request')

service.set('port', (process.env.PORT || 5002))

// Process application/x-www-form-urlencoded

service.use(bodyParser.urlencoded({extended: false}))

// Process application/json

service.use(bodyParser.json())

/**

* Mobile Cloud custom code service entry point.

* @param {external:ExpressApplicationObject}

* service

*/

//module.exports = function(service) {

/**

* Retrieves metadata for components implemented by this service.

*/

service.get(apiURL, function(req,res) {

res.set('Content-Type', 'application/json')

.status(200)

.json(shell.getAllComponentMetadata());

});

/**

* Invoke the named component

*/

service.post(apiURL+'/:componentName', function(req,res) {

console.log("HELLO");

const sdkMixin = { oracleMobile: req.oracleMobile };

console.log(req.params.componentName);

shell.invokeComponentByName(req.params.componentName, req.body, sdkMixin, function(err, data) {

if (!err) {

res.status(200).json(data);

}

else {

switch (err.name) {

case'unknownComponent':

res.status(404).send(err.message);

break;

case'badRequest':

res.status(400).json(err.message);

break;

default:

res.status(500).json(err.message);

break;

}

}

});

});

//};

//

// Spin up the server

service.listen(service.get('port'), function() {

console.log('running on port', service.get('port'))

})

The package.json:

{

"name" : "event_api",

"version" : "1.0.0",

"description" : "API for Event bot connecting to ATP",

"main" : "event_api.js",

"oracleMobile" : {

"dependencies" : {

"apis" : { },

"connectors" : { }

},

"configuration": {

"node": "6.10"

}

}

}

If you have packaged the custom component with all the other javascript files required ie. Shell.js, sdk.js, registry.js, MessageModel.js , you can spin up the server with:

>node event_api.js

Clone the Component on GitHub

However if you want to keep you server live you can run event_api.js using the opensource tools like pm2 running on Oracle Compute cloud.

You need to connect your skill to your custom component service running on the Node server on the components tab.

oda1

All done now! Run the code!

On my ATP database tables RESERVATIONS, i have 0 entries. Let’s reserve two tickets at Jazz festival.

oda2

oda3

On the events skill bot:

oda4

The data is updated on table EVENTS – for jazz festival : 2 seats on Bookings column.oda5

Also added a record on the RESERVATIONS table.

oda6

I played around with validations too- you cannot over book 🙂

oda7

Final Thoughts

Node-orcledb can be used to create a middleware layer which will present REST APIs and websockets interfaces for Oracle ATP database. The middleware logic can be hosted on any Node.js server or even run in a container such as OKE.

References:

 

Thank you and Happy coding! If you liked it, share it!

This blog reflects  my own thoughts and doesn’t reflect the thoughts of my employer.

 

Build Custom Service Components for the Oracle Digital Assistant in Minutes!

Hello there,

I will not keep you with a long post. You need to be familiar with the Oracle Digital Assistant to try out this.

To implement custom components, you use the Oracle Digital Assistant Node.js SDK to interface with Digital Assistant’s custom component service. You can then deploy to the Digital Assistant embedded container, a Mobile Hub backend, or a Node.js server.

Here i will explain deploying on Digital Assistant Embedded container.

Prerequisites:

  • Node Package Manager, Node version 8.x or lower
  • Access to Oracle Digital Assistant 18.4.3+

1. Install node globally on your windows machine;

npm install -g @oracle/bots-node-sdk

2. Create a directory on windows ie. helloworld

3. Enter in the directory using cmd (terminal)

3. Initialize new node project inside the folder ie. helloworld

npm init -y

4. Install Node SDK locally

npm install --save-dev @oracle/bots-node-sdk

5. Create a custom component with a custom name. ie LaHelloWorld

bots-node-sdk init -c LaHelloWorld

6. Navigate out of the folder.

cd ..

7. Start node server and test the custom component just created.

bots-node-sdk service helloworld

8. Open the browser and test the URL given on cmd

Capture

9. Crt + C to cancel the node server process

10. Navigate back to the folder

cd helloworld

11. Create a package to deploy.

npm pack

12. Go to the skills on Oracle Digital Assistant ie. LN_customc101

(Download and import one I  created on here)

13. Open it and go to custom component service menu

14. Click add a service

15. Give it a name ie. Helloworld and select Embedded container.

16. Upload the packaged helloworld1.x.0.0.tgz packaged earlier.

17. Click create and wait for it to be deployed.

Capture1

18. Test your work! It works! 🙂

Capture2

 

Check out this code on Github

Learn more | Useful resources, check out;