Integration of Google Assistant with Oracle APEX and Oracle DB. Part 1

9 January 2020, Dominik Grabiński

Nowadays, chatbots are becoming more and more popular, and allow for more significant interaction with users. But is it possible to combine Google Assistant with an Oracle database, and integrate it with APEX? 

Imagine that you are a sales representative, just say to your phone: “OK Google, talk to hi APEX” and you can ask the chatbot about the next place on the list to visit today. Are you running out of reagents in a laboratory? The contract with an important customer is about to expire? Nothing easier! Just ask your Google Assistant for any information downloaded directly from the database, without distracting yourself from your activities.

This article provides general information on how to implement the Google Assistant chatbot in an APEX application. In this article, we will outline the assumptions made, the general architecture of the chatbot implementation, and the general workflow. In part 2, we will include more technical information and samples of the final implementation.

If you’re interested in chatbots and assistants, check out our other articles:

Assumptions made regarding database and APEX

To start working with Google Assistant, you have to create an application that is open to the internet. The fastest way to achieve this is to create an APEX workspace at https://apex.oracle.com (go to ‘Sign In’) and create a new, blank application. This method has one flaw: you won’t have access to any of your databases through SQL Developer. If this is a must, we suggest that you create the same schema/workspace/application in the localhost, and after preparing your database, migrate it (via export/import) to your workspace at apex.oracle.com. Remember, that you must install the Oracle Rest Data Service (ORDS) on your (local) database in order for it to be accessible by Google Assistant. It is also possible to add Google Assistant to your existing application if you want to access your existing database.

A few words about Dialogflow and Actions on Google

Dialogflow is a tool provided by Google that allows you to architect conversations between end-users and Google Assistant. This tool offers many built-in conversation templates and also allows you to create special, unique questions that directly query the database. Imagine, for example, that you wanted to add your own welcome response to a user saying hi or good morning, say having Google Assistant respond with  “good morning sunshine !” or “hi, I’ve been waiting for you forever :)” – in Dialogflow it’s simple! 

Maybe you wanted specific information from a table in the database (e.g., EMPLOYEES), for example asking: “Could you tell me which department Steven King works in?” – you can also do this in Dialogflow! (check out the exact implementation in part 2).

Actions on Google is another tool created for developers to extend Google Assistant and release their own version. To use it, you must first fulfill a few mandatory conditions like entering the name of your bot and specifying a Privacy Policy. Instructions are given at every step and are clear and user-friendly. When you fulfill all the necessary conditions, your bot will be ready for deployment but must first be verified. You will have to send your prepared version for verification to Google, and after they verify it, they will contact you and give you information about whether your bot was approved or denied, and about any changes you need to make. This process can take anywhere from a few hours to a day. Should your bot be rejected, don’t worry! You will be told why it was rejected and what changes you should make before reapplying. Finally, once your Google Assistant is verified, you can deploy it to many devices (e.g., Google Home, Android, iOS)!

What do I have to do in APEX?

The APEX side of things is straightforward. Since you are provided some HTML code by Dialogflow to setup an iframe for the assistant, you can simply paste the given code into your APEX web page and enjoy a friendly chat with your new bot.

An example of implementation:

Summary

Technical information about how to allow the bot to access a database is given in part 2 of this article.

Tagged with: , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!