Add a Natural Language Interface to Oracle APEX Application

Introduction

We operate the Database Expert Resources (DBExpert Toolkit) site, which provides comparative information and interactive tools helping users identify the best Oracle Database Cloud Service for their use cases. We recently added a natural language interface to our website so users can also ask questions about the cloud services.

Our website is an Oracle Application Express (Oracle APEX) instance running on an Oracle Autonomous Transaction Processing Serverless database, and we saw the new Oracle Autonomous Database Select AI was available to enable natural language interactions with our data.

To gracefully handle not only content-related questions but also requests for help and to accept user feedback, we chose Oracle Digital Assistant for the frontend to the natural language queries.

This tutorial outlines the tasks we performed to add the natural language interface.

AskDBExpert on oracle.com/dbexpert

Objectives

Prerequisites

Task 1: Connect Oracle Autonomous Database Select AI to your LLM

In your APEX workspace, in SQL Workshop -> SQL Commands, run the code below to create an Artificial Intelligence (AI) profile to specify schemas and database objects that will be used with Select AI. The profile will be used in Task 2.

For this tutorial, we used OpenAI as the LLM.

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OPENAI_CRED',
username => 'OPENAI',
password => '...' );
END;

BEGIN
    DBMS_CLOUD_AI.CREATE_PROFILE(
        profile_name => 'OPENAI_PROFILE',
        attributes => '{ "provider": "openai",
                        "credential_name": "OPENAI_CRED",
                        "comments":"true", 
                        "object_list": [{"owner": "DB_SCHEMA", "name":"OBJECT_NAME"}]
                    }',
        description => 'AI profile to use OpenAI for SQL translation'
    );
END;

To verify Oracle Autonomous Database Select AI configuration, execute

select dbms_cloud_ai.generate(
    prompt => 'How many employees work at Oracle?',
    action => 'SHOWSQL',
    profile_name => 'OPENAI_PROFILE'
)
FROM DUAL;

From this, you should receive a SQL query as the output, verifying the configuration.

Task 2: Integrate the REST API

Once you have Oracle Autonomous Database Select AI working with your database objects, create a REST POST endpoint that will be integrated with the Oracle Digital Assistant. To create this in Oracle APEX, open SQL Workshop -> RESTful Data Services and create a Module with a POST handler. The module name and the POST handler name are used in Task 3 for configuring the Oracle Digital Assistant.

The POST handler receives the natural language questions from Oracle Digital Assistant and retrieves a SQL query. The resulting SQL query is executed, and the output is returned to the digital assistant for display. Paste the following code into the POST handler for the module you created:

Tip: See Enabling Data Exchange with RESTful Services for further details on RESTful Services with Oracle APEX.

DECLARE
    l_response       CLOB;
    l_sql            CLOB;
    l_prompt         VARCHAR2 (4000);
    l_cursor         NUMBER := DBMS_SQL.open_cursor;
    l_profile_name   user_cloud_ai_profiles.profile_name%TYPE :=  'YOUR_PROFILE'; -- the LLM profile you created per (link to Select AI setup)
    p_refcursor      SYS_REFCURSOR;
    l_result         CLOB;
BEGIN

/* 
    The variable name for the 'prompt' parameter below must match the variable used in the 
    Oracle Digital Assistant Flow, in the State that calls the REST endpoint
*/

    l_sql :=
        dbms_cloud_ai.generate (prompt         =>  :l_prompt, 
                                action         => 'showsql',
                                profile_name   => l_profile_name);

    -- check if SQL could be generated.
    BEGIN
        DBMS_SQL.PARSE (l_cursor, l_sql, DBMS_SQL.native);
        l_response := l_sql;
        parse_cursor (l_sql, p_refcursor, l_result);
        if l_result is not null then
            :status := 200;
        else
            raise_application_error(-20987,'No records found');
        end if;
    EXCEPTION
        WHEN OTHERS
        THEN
            :status := 400;
            l_result := 'Something went wrong while processing...'; 
    END;

    APEX_UTIL.PRN (l_result, FALSE);
END;

Execute the following in Oracle APEX SQL Workshop -> SQL Commands to create the procedure that runs the SQL query received from Oracle Autonomous Database Select AI:

CREATE OR REPLACE PROCEDURE parse_cursor (p_query    IN     VARCHAR2,
                                          cur        IN OUT SYS_REFCURSOR,
                                          p_result   OUT CLOB)
AS
    curs          INT;
    cols          INT;
    d             DBMS_SQL.desc_tab2;
    val           VARCHAR2 (32767);
    l_row_count   INTEGER := 0;
BEGIN
    OPEN cur FOR p_query;
    curs := DBMS_SQL.to_cursor_number (cur);
    DBMS_SQL.describe_columns2 (curs, cols, d);
    p_result := p_result || '<table> <tr>';
    FOR i IN 1 .. cols
    LOOP
        DBMS_SQL.define_column (curs,
                                i,
                                val,
                                32767);
        p_result := p_result || '<th>' || d (i).col_name || '</th>';
    END LOOP;
    p_result := p_result ||'</tr>';
    WHILE DBMS_SQL.fetch_rows (curs) > 0
    LOOP
        p_result := p_result ||'<tr>';

        FOR i IN 1 .. cols
        LOOP
            DBMS_SQL.COLUMN_VALUE (curs, i, val);
            p_result := p_result ||'<td>' || val || '</td>';
        END LOOP;

        p_result := p_result ||'</tr>';
        l_row_count := l_row_count + 1;
    END LOOP;
    p_result := p_result ||'</table>';
    IF l_row_count = 0
    THEN
        p_result := NULL;
    END IF;

    DBMS_SQL.close_cursor (curs);
END;
/

The output is formatted into a table for use with Oracle Digital Assistant; JSON output would be another option for different applications.

Note: This endpoint can be used from multiple Oracle Digital Assistants (or similar applications) to leverage the data and processing performed by Oracle Autonomous Database Select AI.

Task 3: Configure an Oracle Digital Assistant

In our Oracle Digital Assistant instance, we created a flow to handle questions about our data. Additional flows handle requests for help, small talk, and feedback. The screenshots below show the places in the Oracle Digital Assistant where we used the information about the REST endpoint we created in Task 2 in order to connect the chatbot to the REST service.

Tip: This tutorial does not cover creating and configuring an Oracle Digital Assistant. For more information , see Oracle Digital Assistant.

This flow passes the user input to the REST endpoint for processing.

Configuring Oracle Digital Assistant API Services

Oracle Digital Assistant - Setting Variable for Calling REST Service

Oracle Digital Assistant - Setting Variable Detail

Oracle Digital Assistant - Call REST Service

Oracle Digital Assistant - Capture REST Service Response

Task 4: Prepare Data for Oracle Autonomous Database Select AI

Our schema includes multiple tables that are joined with mapping tables. Rather than pass all of these to the LLM, we created a view that joins the relevant tables, using our domain knowledge to send a table with all (and only) necessary data. This view is periodically refreshed from the source tables to stay up to date.

Polish the experience with Retrieval Augmented Generation (RAG)

We quickly discovered that although we were sending our complete and trusted data to the LLM, more information was needed for our application to produce good results. (We learned that prompt engineering does not mean code it quickly!) By using retrieval augmented generation (RAG), we were able to improve our results. For example, the Oracle packaged applications represented in our data are known by various abbreviations and acronyms, which we now provide in comments on the relevant columns. You may consider this type of action to improve your results.

Task 5: Integrate an Oracle Digital Assistant into the Oracle APEX Application

The application is an Oracle APEX instance hosted on an Oracle Autonomous Database instance. Integrate Oracle Digital Assistant into the application.

  1. In the Oracle APEX application, go to Shared Components and Static Application Files.

    These static application files are used for configuring and invoking the chatbot.

    • settings.js
    • web-sdk.js
    • chatbot_style.css

    Note: Download these Oracle Digital Assistant templates from here: Oracle Digital Assistant (ODA) and Oracle Mobile Cloud (OMC) Downloads.

    • settings.js: This file is for the Oracle Digital Assistant configuration. Update the following values.

      • URL: Enter the URL of your Oracle Digital Assistant instance.
      • Name: Enter the name of the bot referenced from the Oracle APEX Application On Page Load event in page 0.
      • Channel ID: Enter channel ID copied from the Oracle Digital Assistant application.
      • To tie Oracle APEX application user sessions to a bot instance that is maintained across different application pages as the user navigates.
        • userId: document.getElementById("pInstance").value.

    • web-idk.js: No changes are needed to this file, but it must be updated for each new release of the Oracle Digital Assistant platform, to match the version of the Oracle Digital Assistant.

    • chatbot_style.css: Create CSS file from scratch to customize the chatbot icon.

  2. To configure the application to use, upload the file into Static Application Files. Go to Shared Components, User Interface Attributes, JavaScript and enter the URLs for settings.js and web-sdk.js.

    Update the application's User Intertface Attributes - JavaScript

  3. Go to CSS tab and enter the URL for CSS file.

    Update the application's User Intertface Attributes - CSS

  4. In the application’s Global Page 0, add an event that fires on page load to invoke the initSdk function in the settings.js file.

    APEX application event for chatbot initialization

    APEX application event code for chatbot initialization

Run your Oracle APEX application, and it should display the chatbot icon at the bottom right of the screen. It is ready for users to ask natural language questions!

Next Steps

Enable Natural Language Queries to add Value to Your Data

By combining three strategic Oracle technologies, you rapidly introduced useful and extensible AI capabilities into your application with a new chatbot that can be used in other applications and websites. You can expand the scope of the data handled using Oracle Autonomous Database Select AI, and integrate additional sources of information for new Oracle Digital Assistant flows to process.

Go to the Database Expert Resources and try natural language interface and give us feedback.

AskDBExpert on oracle.com/dbexpert

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.