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

Previously, we explored the OpenAI API’s completion models, which enabled text generation and language-based automation. Now, we are shifting our focus to OpenAI’s new Assistant API, a more structured approach that enhances interactions by integrating memory, function calling, and multi-step reasoning. This new paradigm allows for more sophisticated and context-aware AI-driven assistants that can work seamlessly within Oracle APEX applications. Oracle APEX, a low-code development platform, benefits greatly from AI-powered assistants by providing users with automated responses, data analysis, and workflow optimizations. By leveraging OpenAI Assistants, developers can build intelligent chatbots, automate decision-making processes, and improve application functionality with minimal effort.

To achieve this seamless integration, we utilize Oracle APEX’s REST Data Source feature, which enables direct communication with external APIs. This ensures a smooth exchange of information between Oracle APEX applications and OpenAI’s Assistant API, allowing dynamic creation, retrieval, and management of assistant instances.

REST Data Source configuration interface showing settings for integrating with OpenAI's API, including details like the base URL, URL path prefix, and authentication credentials

REST Data Source configuration for connecting to OpenAI’s API, showcasing setup details for seamless integration and optimized performance.

The GIA_OPENAI_ASSISTANT REST Data Source can dynamically create and retrieve assistant instances, ensuring that AI-powered assistants are assigned and maintained efficiently. As shown in the image, this resource will be used to manage and create the AI assistant. The next step is to determine how to load this information into the system through the appropriate Oracle APEX page load procedures.

web application's page layout editor and process configuration, highlighting PL/SQL code execution for managing assistant interactions and loading messages.

Web application interface showcasing page layout design and process configuration for executing PL/SQL code to handle AI assistant interactions.

Retrieving and Storing OpenAI Assistant Identifiers in Oracle APEX To efficiently manage OpenAI Assistants within Oracle APEX, we use a PL/SQL procedure that retrieves an existing assistant identifier for a user and, if not found, generates a new instance using the OpenAI Assistant API. This process ensures that each user has an associated assistant ID stored in the database for future interactions.

Copy to Clipboard

DECLARE
    v_params_metadata apex_exec.t_parameters; -- Parameters for the REST API request
    l_assistant_id    VARCHAR2(200);          -- Local variable to store the assistant ID
    l_status_code     NUMBER;                 -- Variable to store the status code of the REST request
    v_response        CLOB;                   -- Variable to store the REST API response
    v_id              VARCHAR2(200);         -- Variable to store the extracted ID from the JSON
BEGIN
    -- Assign the USER_ID to a page variable
    :P1_USER_ID := :USER_ID;

BEGIN
        -- Try to fetch the ASSISTANT_ID for the given USER_ID
        SELECT assistant_id
        INTO l_assistant_id
        FROM GESTALTIA_USERS_ASSISTANTS
        WHERE USER_ID = :USER_ID;

-- If ASSISTANT_ID exists, assign it to the page variable and skip the REST call
        :P1_ASSISTANT_ID := l_assistant_id;
        RETURN;

EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- If no record is found, initialize l_assistant_id to NULL
            l_assistant_id := NULL;
    END;

-- Execute the REST source and get the response
    apex_exec.execute_rest_source(
        p_static_id  => 'GIA_OPENAI_ASSISTANT', -- Static ID of the REST source
        p_operation  => 'POST',                -- HTTP POST method
        p_parameters => v_params_metadata      -- Parameters for the request
    );

v_response := apex_exec.get_parameter_clob(v_params_metadata, 'RESPONSE');
    
    -- Retrieve the status code of the REST API request
    l_status_code := apex_web_service.g_status_code;

-- Check if the REST API call was successful
    IF l_status_code = 200 THEN
        -- Parse the JSON response to extract the "id"
        
        apex_json.parse(v_response);
        v_id := apex_json.get_varchar2('id'); -- Extract the "id" field from the JSON response

-- Assign the retrieved ASSISTANT_ID to the page variable
        :P1_ASSISTANT_ID := v_id;

-- Insert or update the record in the GESTALTIA_USERS_ASSISTANTS table
        BEGIN
            INSERT INTO GESTALTIA_USERS_ASSISTANTS (USER_ID, ASSISTANT_ID)
            VALUES (:USER_ID, v_id);

EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                -- If the record already exists, update it
                UPDATE GESTALTIA_USERS_ASSISTANTS
                SET assistant_id = v_id
                WHERE USER_ID = :USER_ID;
        END;

ELSE
        -- Handle a failed REST API request
        apex_error.add_error(
            p_message          => 'REST API call failed with status code: ' || l_status_code,
            p_display_location => apex_error.c_on_error_page
        );
    END IF;

EXCEPTION
    -- Handle general exceptions
    WHEN OTHERS THEN
        -- Display an error message on the APEX page
        apex_error.add_error(
            p_message          => apex_lang.message('GIA_UNEXPECTED_ERROR') || ': ' || SQLERRM,
            p_display_location => apex_error.c_on_error_page
        );
END;

The procedure follows these steps:

Check for an Existing Assistant ID: The system first queries the GESTALTIA_USERS_ASSISTANTS table to check if an assistant ID already exists for the given user.
Create a New Assistant if Necessary: If no record is found, a REST API call is made to GIA_OPENAI_ASSISTANT, which generates a new assistant instance using the OpenAI Assistant API.
Store the Assistant ID: The newly created assistant ID is extracted from the API response and stored in the GESTALTIA_USERS_ASSISTANTS table.
Handle Updates: If an assistant ID already exists, it is updated in the database to maintain consistency.
Error Handling: The procedure includes robust error handling to manage failed API calls and unexpected issues.

Each assistant has a set of threads associated with it, which naturally correspond to different interactions with the AI over time. These threads represent distinct conversational contexts, allowing the assistant to maintain continuity and relevance in its responses. To ensure a coherent user experience, it is essential to manage the loading process in a way that enables each thread to be correctly linked to its respective interaction. This requires careful coordination between the data retrieved from the REST Data Source and the logic implemented in Oracle APEX. In the following section, we will explore how to structure this process efficiently, leveraging APEX page load procedures and dynamic actions to seamlessly integrate thread management within the broader framework of AI assistant deployment.

Oracle APEX interface showing PL/SQL process configuration for retrieving conversation threads and managing dynamic interactions with an AI assistant.

PL/SQL process setup in Oracle APEX for retrieving conversation threads and ensuring seamless AI assistant communication.

To manage the association between users and their AI interaction threads, we have created a PL/SQL procedure named PR_GET_THREAD. This procedure is responsible for retrieving the most recent thread associated with a given user and assistant. If no thread exists, it dynamically creates one via a REST API call to the GIA_OPENAI_THREAD REST Data Source. This ensures that each interaction with the AI assistant maintains contextual continuity, a key element in delivering a coherent and personalized user experience.

Copy to Clipboard

DECLARE
    v_params_metadata apex_exec.t_parameters; -- REST API parameters
    l_thread_id       VARCHAR2(200);          -- Thread ID
    l_status_code     NUMBER;                 -- HTTP status code from API response
    v_response        CLOB;                   -- JSON response from API
BEGIN
    -- Assign USER_ID to the APEX page variable
    :P1_USER_ID := :USER_ID;

-- Attempt to fetch the latest THREAD_ID associated with the USER_ID and ASSISTANT_ID
    BEGIN
        SELECT thread_id
        INTO l_thread_id
        FROM GESTALTIA_USERS_THREADS
        WHERE USER_ID = :USER_ID
          AND ASSISTANT_ID = :ASSISTANT_ID
        ORDER BY CREATED_AT DESC
        FETCH FIRST 1 ROWS ONLY; -- Retrieves the most recent record

EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- No THREAD_ID found in the database, triggering a REST API request
            l_thread_id := NULL;
    END;

-- If no THREAD_ID was found in the database, call the REST API
    IF l_thread_id IS NULL THEN
        BEGIN
            -- Execute REST API request
            apex_exec.execute_rest_source(
                p_static_id  => 'GIA_OPENAI_THREAD', -- Static ID of the REST source
                p_operation  => 'POST',             -- HTTP method
                p_parameters => v_params_metadata   -- API request parameters
            );

-- Get the HTTP status code of the response
            l_status_code := apex_web_service.g_status_code;

-- If the API response is successful (HTTP 200 OK)
            IF l_status_code = 200 THEN
                -- Extract the response JSON
                v_response := apex_exec.get_parameter_clob(v_params_metadata, 'RESPONSE');
                apex_json.parse(v_response);
                l_thread_id := apex_json.get_varchar2('id'); -- Extract "id" field from JSON

-- Assign the retrieved THREAD_ID to APEX page variables
                :P1_THREAD_ID := l_thread_id;
                :THREAD_ID := l_thread_id;

-- Insert or update the record in the GESTALTIA_USERS_THREADS table using MERGE
                MERGE INTO GESTALTIA_USERS_THREADS t
                USING (SELECT :USER_ID AS USER_ID, :ASSISTANT_ID AS ASSISTANT_ID, l_thread_id AS THREAD_ID, SYSTIMESTAMP AS CREATED_AT FROM DUAL) src
                ON (t.USER_ID = src.USER_ID AND t.ASSISTANT_ID = src.ASSISTANT_ID)
                WHEN MATCHED THEN
                    -- Only update THREAD_ID if it is different
                    UPDATE SET t.THREAD_ID = src.THREAD_ID
                    WHERE t.THREAD_ID <> src.THREAD_ID
                WHEN NOT MATCHED THEN
                    -- Insert a new record
                    INSERT (USER_ID, THREAD_ID, ASSISTANT_ID, CREATED_AT) 
                    VALUES (src.USER_ID, src.THREAD_ID, src.ASSISTANT_ID, src.CREATED_AT);

ELSE
                -- Handle failed REST API request
                apex_error.add_error(
                    p_message          => 'REST API call failed with status code: ' || l_status_code,
                    p_display_location => apex_error.c_on_error_page
                );
            END IF;

EXCEPTION
            WHEN OTHERS THEN
                -- Capture and log unexpected errors from the REST API execution
                apex_error.add_error(
                    p_message          => 'REST API Error: ' || SQLERRM,
                    p_display_location => apex_error.c_on_error_page
                );
        END;
    END IF;
END;

Key Steps in `PR_GET_THREAD`:

Initialization and Variable Declaration
The procedure starts by declaring several local variables, including a parameter set for the REST call, the thread ID, the HTTP status code, and a CLOB to hold the JSON response.
User ID Assignment
The USER_ID value is assigned to the APEX page item :P1_USER_ID, ensuring that the session context remains consistent throughout the procedure.
Retrieving the Latest Thread
A query attempts to fetch the most recent thread ID for the given user and assistant from the GESTALTIA_USERS_THREADS table. If no thread is found, the control gracefully falls into the NO_DATA_FOUND exception handler, setting l_thread_id to NULL.
REST API Call for Thread Creation
If no existing thread is retrieved, the procedure triggers a POST request to the GIA_OPENAI_THREAD REST Data Source. Upon a successful API response (HTTP 200 OK), the JSON response is parsed, and the new thread ID is extracted.
Thread Assignment and Persistence
The retrieved thread ID is assigned to APEX page variables :P1_THREAD_ID and :THREAD_ID. Then, a MERGE operation ensures that the thread ID is either inserted or updated in the GESTALTIA_USERS_THREADS table, preserving data consistency and avoiding duplicates.
Error Handling
The procedure includes robust error handling mechanisms, both for unsuccessful API responses and unexpected exceptions. Errors are logged and surfaced through apex_error.add_error, ensuring that issues are traceable during execution.

Now that we have defined our AI assistant and established a conversation thread to manage interactions, the next step is to efficiently structure the storage and display of messages. To achieve this, we will use an Oracle APEX collection, which allows for fast message retrieval, maintains a well-organized conversation structure, and optimizes resource usage. Rather than relying on constant database queries, this approach provides a dynamic, temporary storage mechanism that enables real-time message access with minimal performance overhead. To implement this strategy, we have developed the PR_LOAD_MESSAGES procedure, responsible for initializing the conversation environment, retrieving predefined system messages, and managing the message collection in a structured and efficient manner.

Copy to Clipboard

DECLARE
    l_collection_name   VARCHAR2(255);
    l_welcome_message   VARCHAR2(4000);
    l_prompt_message    VARCHAR2(4000);
BEGIN
    --
    :P1_ENNEAGRAM_CHARACTER := 'NEUTRAL';
    
    -- 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);

-- 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
        );
        
    END IF;
    -- 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 this, we now have a solid foundation in place: the assistant is set up, conversation threads are properly managed, and messages are efficiently stored and retrieved. This structure not only ensures smooth interaction but also optimizes performance by reducing unnecessary database queries.

Now, we move on to the next critical step: sending messages to the assigned thread and facilitating a seamless interaction with the AI. This involves designing an efficient mechanism to transmit user inputs, process responses, and maintain the conversation context across interactions. By carefully managing how messages flow between the user and the AI, we can enhance responsiveness, improve scalability, and create a more intuitive experience. Our next task will be to refine this communication process, ensuring that each message is properly delivered, processed, and stored, paving the way for a robust and dynamic exchange with the assistant.

In the next post (Part 2) of this series, we will cover all these aspects in detail, exploring how to send messages to the AI, handle responses efficiently, and maintain a structured, high-performance interaction model.

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 1