Oracle APEX and OpenAI Walk Into a Bar… And Build an Assistant - Part 2 - Novoshore Europe - Norwegian IT Nearshoring company Malaga Spain

Building on our previous discussion about integrating OpenAI Assistants with Oracle APEX, this article focuses on structuring and displaying user interactions efficiently. Instead of frequent database queries, APEX collections provide session-based storage for optimized performance. The PR_LOAD_MESSAGES procedure initializes the conversation by retrieving system messages, structuring collections, and handling errors. Messages are displayed dynamically using an interactive report with AJAX updates for real-time responsiveness. User messages are processed through a RESTful API call to OpenAI, retrieving AI responses and dynamically updating the chat. This architecture ensures efficient message management, real-time interaction, and a scalable AI-powered chat system within Oracle APEX. Now that we’re using a Classic Report with the Cards Container template to display messages, the next step is to configure the actions for the “Send” button. These actions include:

SHOW SPINNER – Displays a loading spinner while the message is being processed.
PR_ADD_MESSAGE_TO_COLLECTION – Adds the message to the message collection for further processing.
PR_PERSONALITY_SELECTION – Selects the appropriate personality or context for the message.
PR_SEND_MESSAGE_TO_OPENAI – Sends the message to the OpenAI API for processing.
REFRESH CONVERSATION – Updates the conversation view with the new message.
HIDE SPINNER – Hides the loading spinner once the message has been processed.

Oracle APEX interface showing dynamic actions for message processing, including spinner display, personality selection, and OpenAI API integration

Configuration of dynamic actions in Oracle APEX for integrating OpenAI, showcasing steps like spinner display, message handling, personality selection, and API communication

Let’s look at each action in more detail:

SHOW SPINNER

As seen in the image, the first action will be SHOW SPINNER, an action that will execute JavaScript with the following content:

Copy to Clipboard

Is used to display a loading spinner on the screen. This spinner serves as a visual cue to inform the user that a process is underway, such as loading data or performing an operation.

PR_ADD_MESSAGE_TO_COLLECTION

The second action, PR_ADD_MESSAGE_TO_COLLECTION, is more complex:

PL/SQL code in Oracle APEX for adding user messages to collections during OpenAI integration workflow

Oracle APEX interface displaying server-side PL/SQL code used to validate and store user messages in session-based collections

This PL/SQL block is designed to handle user input in an Oracle APEX application. It ensures that a message entered by the user is added to a collection, performs some validation, and handles potential errors gracefully.

Copy to Clipboard

This PL/SQL block is used in Oracle APEX to process user input and store it in an APEX collection. It first checks that the input message (:P1_INPUT_MESSAGE) is not empty or null, ensuring valid data. If valid, it adds the message to a specified collection (:P1_COLLECTION_NAME) along with metadata such as the username (:APP_USER), a timestamp, and the session ID for tracking purposes. After successfully adding the message, the input field is cleared to prepare for new entries.

If any errors occur during execution, the block uses APEX’s error-handling framework to log the issue and display a user-friendly error message. This ensures smooth functionality while providing feedback in case of unexpected issues, enhancing both reliability and user experience.

PR_PERSONALITY_SELECTION

This section is the third part of the process, where we personalize the OpenAI assistant’s responses based on the Enneagram personality types. By selecting a specific personality (e.g., Reformer, Helper, Achiever), we tailor the assistant’s tone and style to align with that character’s traits, making interactions more relevant and engaging for the user.

PL/SQL block in Oracle APEX for personality-based response selection using Enneagram types during OpenAI integration.

Oracle APEX interface showing PL/SQL code to select personality traits based on Enneagram types for tailored AI responses

Below is the PL/SQL block that handles this logic:

Copy to Clipboard

DECLARE
    response_text VARCHAR2(200); -- Variable to hold the generated response text
BEGIN
    -- Determine the appropriate response based on the character type
    CASE :P1_ENNEAGRAM_CHARACTER  -- Bind variable that holds the selected character type
        WHEN 'REFORMER' THEN
            response_text := 'The response to the user should be crafted with the character: REFORMER (Type 1).';
        WHEN 'HELPER' THEN
            response_text := 'The response to the user should be crafted with the character: HELPER (Type 2).';
        WHEN 'ACHIEVER' THEN
            response_text := 'The response to the user should be crafted with the character: ACHIEVER (Type 3).';
        WHEN 'INDIVIDUALIST' THEN
            response_text := 'The response to the user should be crafted with the character: INDIVIDUALIST (Type 4).';
        WHEN 'INVESTIGATOR' THEN
            response_text := 'The response to the user should be crafted with the character: INVESTIGATOR (Type 5).';
        WHEN 'LOYALIST' THEN
            response_text := 'The response to the user should be crafted with the character: LOYALIST (Type 6).';
        WHEN 'ENTHUSIAST' THEN
            response_text := 'The response to the user should be crafted with the character: ENTHUSIAST (Type 7).';
        WHEN 'CHALLENGER' THEN
            response_text := 'The response to the user should be crafted with the character: CHALLENGER (Type 8).';
        WHEN 'PEACEMAKER' THEN
            response_text := 'The response to the user should be crafted with the character: PEACEMAKER (Type 9).';
        WHEN 'NEUTRAL' THEN
            response_text := 'The response to the user should be crafted with the character: NEUTRAL.';
        ELSE
            -- Default case for unrecognized character types
            response_text := 'The selected character type is not recognized.';
    END CASE;

-- Assign the generated response to the page item for display
    :P1_PERSONALITY_SELECTION := response_text;

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

This PL/SQL block generates a personalized response based on the user’s selected Enneagram personality type (:P1_ENNEAGRAM_CHARACTER) in an Oracle APEX application. Using a CASE statement, it checks the selected type (e.g., REFORMER, HELPER, ACHIEVER) and assigns a corresponding message to the page item :P1_PERSONALITY_SELECTION for display. If the selection is unrecognized, a default message is provided.

The block also includes error handling to catch unexpected exceptions. If an error occurs, it logs the issue using APEX’s error framework and displays a user-friendly error message on the application’s error page. This ensures smooth functionality and enhances the user experience by providing tailored responses and robust error management.

PR_SEND_MESSAGE_TO_OPENAI

This is the fourth step, where we send the user’s input and personality selection to the OpenAI API. The PL/SQL block handles the API request, processes the response, and stores the generated message in the application’s collection. If the API call fails, an error message is displayed.

Oracle APEX interface showing PL/SQL configuration for sending user input and personality traits to OpenAI API

Detailed view of the PL/SQL block in Oracle APEX used to send user input and personality selection to OpenAI API for processing

Below is the PL/SQL block that implements this functionality.

Copy to Clipboard

DECLARE
    v_params_metadata apex_exec.t_parameters;
    l_first_name VARCHAR(100);
    l_status_code NUMBER;
    l_response CLOB;
    v_message_content CLOB;

BEGIN
    select FIRST_NAME into l_first_name from GESTALTIA_USERS where UPPER(USER_NAME) = UPPER(:APP_USER);

apex_exec.add_parameter(v_params_metadata, 'PROMPT', :P1_INPUT_MESSAGE);
    apex_exec.add_parameter(v_params_metadata, 'PERSONALITY', :P1_PERSONALITY_SELECTION);
    apex_exec.add_parameter(v_params_metadata, 'FIRST_NAME', l_first_name);

apex_exec.execute_rest_source(
        p_static_id  => 'GIA_OPENAI_COMPLETIONS',
        p_operation  => 'POST',
        p_parameters => v_params_metadata
    );

l_status_code := apex_web_service.g_status_code;

IF l_status_code = 200 THEN

l_response := apex_exec.get_parameter_clob(v_params_metadata, 'response');

IF l_response IS NOT NULL THEN
            BEGIN
                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;

:P1_RESPONSE := v_message_content;

apex_collection.add_member(
            p_collection_name => :P1_COLLECTION_NAME,
            P_C001            => 'GestaltIA',
            P_C002            => 'Yes',
            P_C003            => v_message_content,
            P_C004            => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
            P_C005            => :APP_SESSION,
            P_C006            => 'gestaltia-login.webp'
        );

ELSE
        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;

:P1_INPUT_MESSAGE := NULL;

IF l_response IS NOT NULL THEN
        DBMS_LOB.FREETEMPORARY(l_response);
    END IF;

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

This PL/SQL block begins by retrieving the user’s first name from the GESTALTIA_USERS table, based on the current session’s username. It then prepares the necessary parameters for the OpenAI API request, including the user’s input message (P1_INPUT_MESSAGE), the selected personality (P1_PERSONALITY_SELECTION), and the user’s first name. These parameters are passed to the API using the apex_exec.execute_rest_source method, which performs a POST request to the OpenAI API.

Once the API response is received, the block checks if the status code is 200 (successful). If successful, it extracts the generated message from the response using JSON parsing and stores it in the page item P1_RESPONSE, which will be displayed to the user. Additionally, the message is added to an APEX collection for future use, ensuring that the conversation history is preserved.

In case of an error—whether due to an invalid response or a failed API call—the block assigns an appropriate error message and displays it to the user, including the HTTP status code for transparency. This process ensures smooth interaction with the OpenAI API while providing error handling for unexpected issues.

REFRESH CONVERSATION

The “Refresh Conversation” action ensures that new messages appear instantly in the chat without requiring a manual page reload

The “Refresh Conversation” action updates the Classic Report displaying the conversation. It ensures that new messages, whether from the user or the OpenAI assistant, appear immediately without the need for manual page reloads. This action keeps the conversation up-to-date and ensures a smooth, real-time interaction for the user.

HIDE SPINNER

The “Hide Spinner” action ensures a smooth user experience by removing the loading indicator once the conversation is updated

Finally, the “Hide Spinner” action is used to hide the loading spinner once the conversation has been refreshed and the new message has been processed. This action ensures that the user can seamlessly continue the interaction without the distraction of the loading indicator, providing a smooth and responsive experience.

Copy to Clipboard

The code $("#apex_wait_overlay").remove(); and $(".u-Processing").remove(); are used to remove the loading spinner and overlay elements from the page once the process is complete. The first line removes the main wait overlay, and the second removes the processing indicator, making the UI clean and responsive after the action is finished.

For more information, please visit our company website. Stay updated and feel free to reach out for support or contributions!

Oracle APEX and OpenAI Walk Into a Bar… And Build an Assistant – Part 2