Making APEX Smarter One Chat at a Time

Artificial Intelligence (AI) is transforming how applications interact with users by providing dynamic and intelligent responses. Oracle APEX, with its low-code development environment, enables seamless integration of AI-powered services through REST Data Sources. By leveraging APEX_EXEC, we can call AI APIs, process responses, and display them in real-time within an APEX application.

Continuing with our series of articles on the intersection of psychology and artificial intelligence, we explore how Oracle APEX, combined with OpenAI, opens new doors for developing innovative, user-focused applications. This integration allows developers to easily incorporate personalized responses, intelligent analysis, and adaptive functionalities. In the context of psychology, it can enable real-time behavioral analysis, tailored mental health tools, or conversational AI that adapts to individual needs, revolutionizing how applications enhance user experiences with efficiency and intelligence.

In this article, we will explore how to integrate AI into Oracle APEX using a REST Data Source to send user prompts and retrieve AI-generated responses. The GIA_OPENAI_COMPLETIONS REST Data Source is configured to send POST requests to the OpenAI API, passing a structured JSON payload where the #PROMPT# placeholder is dynamically replaced with the user’s input.

1. Understanding the GIA_OPENAI_COMPLETIONS REST Data Source

The GIA_OPENAI_COMPLETIONS REST Data Source follows a standard structure for making requests to the OpenAI API. Below, you can see its main definition and how the POST operation is configured.

REST Data Source configuration interface in Oracle APEX for integration with OpenAI

REST Data Source configuration in Oracle APEX to connect with the OpenAI API and handle AI-generated responses

Here we can see the POST operation

Oracle APEX REST Data Source configuration with authentication details, JSON response format, and POST operation setup

By editing the POST operation, we can examine the request body template, which defines how data is sent to the API. The "model template" (Request Body Template) field specifies the AI model being used, while the "messages" array contains:

A system prompt, which sets the assistant’s behavior.
A user message, where #PROMPT# will be dynamically replaced with the actual input provided by the user.

REST Source Operation configuration in Oracle APEX for OpenAI API integration using a POST request

Oracle APEX REST Source Operation setup for POST requests to the OpenAI API, with a detailed request body template and operation parameters

This ensures that each request adapts to the context of the conversation, allowing AI to generate intelligent and relevant responses.

Copy to Clipboard

2. Managing collections in Oracle APEX

Once we have created the REST Data Source (GIA_OPENAI_COMPLETIONS) and configured it to send prompts to the OpenAI API, we move to the APEX page where user interactions with the AI will take place.

To manage these interactions dynamically, we use an APEX collection to store messages exchanged between the user and the AI. This allows us to maintain the conversation state during the session, ensuring a smooth and responsive chat experience.

Before the user starts interacting with the AI, we need to initialize the conversation by setting up a structured way to store messages. This is achieved using an APEX collection, which acts as a temporary storage table for the duration of the session. The collection allows us to maintain a record of all interactions, ensuring that both user inputs and AI responses are properly tracked and displayed in real-time.

Oracle APEX Page Designer interface showing a process setup for pre-loading messages in a chat application

Oracle APEX Page Designer configuration for the PR_LOAD_MESSAGES process, which initializes and pre-loads chat messages for an interactive AI interface

During the Pre-Render process of the APEX page, we execute a PL/SQL block that retrieves system settings, such as the welcome message and the input field label, from the GESTALTIA_OPENAI_SETTINGS table. The system then creates or resets the APEX collection associated with the session. Finally, the AI’s welcome message is inserted into the collection, setting the stage for a dynamic and engaging conversation. This ensures that each user begins with a fresh chat session, improving usability and interaction flow.

Copy to Clipboard

DECLARE
    l_collection_name   VARCHAR2(255);
    l_welcome_message   VARCHAR2(4000);
    l_prompt_message    VARCHAR2(4000);
BEGIN
    -- Fetch the required settings (WELCOME_MESSAGE and PROMPT) in a single query
    SELECT 
        MAX(CASE WHEN SETTING = 'WELCOME_MESSAGE' THEN VALUE END),
        MAX(CASE WHEN SETTING = 'PROMPT' THEN VALUE END)
    INTO 
        l_welcome_message, 
        l_prompt_message
    FROM GESTALTIA_OPENAI_SETTINGS
    WHERE SETTING IN ('WELCOME_MESSAGE', 'PROMPT');

-- Assign the retrieved prompt message to the page item
    :P1_PROMPT_MESSAGE := l_prompt_message;

-- Generate a new collection name if it is NULL, using the session ID
    :P1_COLLECTION_NAME := COALESCE(:P1_COLLECTION_NAME, 'GESTALTIA_MESSAGES_' || :APP_SESSION);
    l_collection_name := :P1_COLLECTION_NAME;

-- Ensure the collection exists; if it does, truncate it; if not, create it
    IF NOT apex_collection.collection_exists(l_collection_name) THEN
        apex_collection.create_collection(l_collection_name);
    ELSE
        apex_collection.truncate_collection(l_collection_name);
    END IF;

-- Add the welcome message to the collection only if it's a new session
    apex_collection.add_member(
        p_collection_name => l_collection_name,
        P_C001 => 'GestaltIA',  -- Sender name
        P_C002 => 'Yes',  -- Indicates a system message
        P_C003 => l_welcome_message,  -- Welcome message content
        P_C004 => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),  -- Timestamp
        P_C005 => :APP_SESSION,  -- Session ID
        P_C006 => 'gestaltia-login.webp'  -- Associated image
    );

-- Clear the input message field
    :P1_INPUT_MESSAGE := NULL;

EXCEPTION
    WHEN OTHERS THEN
        -- Log the error and show a user-friendly message in APEX
        apex_error.add_error(
            p_message          => apex_lang.message('GIA_UNEXPECTED_ERROR'),
            p_display_location => apex_error.c_on_error_page
        );
END;

With the initial message loading process and chat page structure in place, the next steps focus on enhancing user interaction. We will implement dynamic handling of user input, sending messages to the OpenAI API through the configured REST Data Source. Once the AI processes the request, we will retrieve the response, store it in the APEX collection, and display it in real-time within the chat interface. This approach ensures a smooth, efficient, and interactive experience, allowing users to engage seamlessly with the AI-powered assistant.

3. Displaying Conversations Dynamically

Now that we have successfully configured the APEX collection to store user messages and AI responses, we need to display the conversation in a visually structured way. To achieve this, we will use a Classic Report with a “Comments” template.

Oracle APEX Page Designer showing configuration of a Classic Report for displaying conversation messages

Oracle APEX configuration of a Classic Report to dynamically display chat conversations, including user and system messages

To ensure the Classic Report aligns with the “Comments” template, we need to structure the SQL query so that its column names correspond to the expected fields in the template. The query retrieves messages from the APEX collection, formatting them appropriately by displaying the user’s name (USER_NAME), distinguishing between user and system messages (USER_TYPE), showing the chat message (COMMENT_TEXT), capturing the timestamp (COMMENT_DATE), linking it to the session (SESSION_ID), and generating a simple avatar (USER_ICON). Ordering the results by COMMENT_DATE ensures the conversation appears in chronological order, mimicking a real-time chat flow.

Copy to Clipboard

With this configuration and SQL setup, the conversation will be displayed in a structured format similar to the image below.

Chat interface with GestaltIA, a virtual therapist providing responses based on Gestalt therapy and Enneagram insights

Interactive chat interface with GestaltIA, offering personalized guidance using Gestalt therapy and Enneagram principles

4. Sending messages to AI

Now that we have the chat interface set up, the next step is handling user input and sending messages to the OpenAI API. In our Oracle APEX page, we have a text input field (P1_INPUT_MESSAGE) where users can type their messages and a button (B_SEND) to send them. This triggers a sequence of Dynamic Actions that handle the message processing.

When the user clicks the B_SEND button, a set of actions are executed in the following order:

SHOW SPINNER → Displays a loading indicator to inform the user that the system is processing their request.
PR_ADD_MESSAGE_TO_COLLECTION → Saves the user’s message in an APEX collection to maintain conversation history.
PR_SEND_MESSAGE_TO_OPENAI → Sends the message to the OpenAI API and retrieves the response.
REFRESH CONVERSATION → Updates the Classic Report to display the latest messages.
HIDE SPINNER → Removes the loading indicator once the response is received.

Let’s break down each step in detail:

Oracle APEX Page Designer displaying dynamic actions for sending a message and updating the chat interface

Dynamic actions configuration in Oracle APEX for handling message sending, API integration, and UI updates in a chat application

SHOW SPINNER

This action executes JavaScript code to display a loading spinner while waiting for the API response:

Copy to Clipboard

This ensures users receive immediate feedback that their message is being processed.

PR_ADD_MESSAGE_TO_COLLECTION

Before sending the message to the API, we need to store it in an APEX collection to maintain the conversation history. This ensures that the chat interface updates immediately with the user’s input, even before receiving the AI’s response.

To achieve this, we create a PL/SQL process (PR_ADD_MESSAGE_TO_COLLECTION) that adds the user’s message to the collection when the B_SEND button is clicked. The process checks whether the input field is not empty, then inserts the message along with relevant metadata, such as the username, timestamp, and session ID.

Copy to Clipboard

PR_SEND_MESSAGE_TO_OPENAI

Once the user’s message has been stored in the APEX collection, the next step is to send it to the AI-powered OpenAI API and retrieve the assistant’s response. This is achieved using a PL/SQL process (PR_SEND_MESSAGE_TO_OPENAI), which executes a REST API call through the configured GIA_OPENAI_COMPLETIONS REST Data Source.

The process sends the user’s input as a dynamic parameter, calls the AI model, and extracts the generated response from the API’s JSON output.

Copy to Clipboard

DECLARE
    -- Parameters for the REST API request
    v_params_metadata apex_exec.t_parameters;
    l_first_name VARCHAR(100);
    l_status_code NUMBER;
    l_response CLOB;
    v_message_content CLOB;
BEGIN
    -- Add the user’s message as a parameter
    apex_exec.add_parameter(v_params_metadata, 'PROMPT', :P1_INPUT_MESSAGE);

-- Retrieve the first name of the user from the database
    SELECT FIRST_NAME INTO l_first_name FROM GESTALTIA_USERS WHERE UPPER(USERNAME) = UPPER(:APP_USER);
    apex_exec.add_parameter(v_params_metadata, 'FIRST_NAME', l_first_name);

-- Execute the REST API call
    apex_exec.execute_rest_source(
        p_static_id  => 'GIA_OPENAI_COMPLETIONS',  -- REST Data Source
        p_operation  => 'POST',  -- HTTP method
        p_parameters => v_params_metadata  -- Request parameters
    );

-- Retrieve HTTP status code
    l_status_code := apex_web_service.g_status_code;

-- Check if the API request was successful
    IF l_status_code = 200 THEN
        -- Extract JSON response
        l_response := apex_exec.get_parameter_clob(v_params_metadata, 'response');

-- Parse AI response from JSON
        IF l_response IS NOT NULL THEN
            BEGIN
                -- Extract AI message content
                SELECT JSON_VALUE(l_response, '$.choices[0].message.content' RETURNING CLOB)
                INTO v_message_content
                FROM dual;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    v_message_content := apex_lang.message('GIA_NO_VALID_RESPONSE');
            END;
        ELSE
            v_message_content := apex_lang.message('GIA_API_ERROR');
        END IF;

-- Store AI response in a page item
        :P1_RESPONSE := v_message_content;

-- Add AI response to the APEX collection
        apex_collection.add_member(
            p_collection_name => :P1_COLLECTION_NAME,
            P_C001            => 'GestaltIA',  -- AI identifier
            P_C002            => 'Yes',  -- AI-generated message
            P_C003            => v_message_content,  -- Message content
            P_C004            => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),  -- Timestamp
            P_C005            => :APP_SESSION,  -- Session ID
            P_C006            => 'gestaltia-login.webp'  -- Associated image
        );

ELSE
        -- Handle API errors
        apex_error.add_error(
            p_message          => apex_lang.message('GIA_API_ERROR') || ' HTTP Status: ' || l_status_code,
            p_display_location => apex_error.c_on_error_page
        );
    END IF;

-- Clear input field
    :P1_INPUT_MESSAGE := NULL;

-- Free up CLOB resources
    IF l_response IS NOT NULL THEN
        DBMS_LOB.FREETEMPORARY(l_response);
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        -- Handle unexpected errors
        apex_error.add_error(
            p_message          => apex_lang.message('GIA_UNEXPECTED_ERROR') || ': ' || SQLERRM,
            p_display_location => apex_error.c_on_error_page
        );
END;

REFRESH CONVERSATION

Once the AI’s response is stored in the APEX collection, we need to update the chat interface to display the latest messages. This is done using a Refresh Action that automatically reloads the Classic Report containing the conversation. By selecting the chat region as the affected element, the conversation updates in real time without requiring a full page reload, ensuring a seamless and dynamic user experience.

HIDE SPINNER

After the AI response is received and the conversation is refreshed, we need to remove the loading indicator to signal that processing is complete. This is achieved with a JavaScript Action that executes the following code:

Copy to Clipboard

This ensures a smooth user experience by restoring the normal interface state after each interaction.

5. Ensuring a Smooth Chat Experience

As the conversation progresses, new messages appear at the bottom of the chat. However, when the Classic Report refreshes, the user is not automatically scrolled to the latest message, making it necessary to manually scroll down. To improve the user experience, we implement auto-scroll after each refresh, ensuring that the most recent messages remain in view.

To achieve this, we add a Dynamic Action triggered when the conversation region refreshes. This action executes JavaScript code to smoothly scroll the chat to the latest message.

Copy to Clipboard

Each time the Classic Report refreshes, the JavaScript code selects the conversation container using its ID (conversationReport) and checks if it exists on the page. If found, it triggers a smooth scrolling animation that moves the chat view to the bottom, ensuring the latest messages remain visible. This prevents users from manually scrolling down after each update, providing a seamless and natural chat experience.

With this article, we conclude our introductory series on Artificial Intelligence in Oracle APEX. However, this is just the beginning—there are many more exciting AI capabilities to explore!

Our next focus will be on OpenAI’s Assistants API, a powerful tool currently in Beta, designed to build AI-driven assistants within applications. This API allows developers to create custom AI assistants with specific instructions, leveraging models, tools, and files to respond intelligently to user queries.

For more information, please visit our company website or find us on APEX World for additional resources and updates!

Making APEX Smarter One Chat at a Time