AI Chatbot
Using Oracle APEX
Introduction
Artificial
Intelligence is becoming an important part of modern business applications.
Organizations increasingly want applications that can answer questions,
summarize information, search internal data, and assist users with everyday
tasks.
Oracle APEX provides a low-code platform for building these applications quickly. When combined with Oracle Database and AI capabilities, APEX can be used to create an intelligent chatbot without requiring a completely separate frontend application.
1. What Is an AI Chatbot?
An AI chatbot is a software application that communicates with users using natural language, allowing them to interact with an application in a simple and conversational way. In traditional applications, users often need to navigate through multiple menus, reports, forms, and filters to find the required information. For example, a user may need to open Reports, select Employees, choose a Department, perform a Search, and then open Employee Details. With an AI chatbot, the user can simply ask a question such as, “Show me all employees in the IT department.” The chatbot can understand the user's request, process the required information, and provide a relevant response. In an Oracle APEX application, this conversational approach allows users to interact with application data more naturally without having to manually navigate through multiple pages and components.
2.
Why Use Oracle APEX for an AI Chatbot?
Oracle APEX is well suited for building AI chatbots because it already provides many of the features required for an enterprise application, including authentication, authorization, session management, database connectivity, forms, reports, Dynamic Actions, AJAX callbacks, PL/SQL, REST API integration, Interactive Reports, Interactive Grids, and built-in security mechanisms. Because these capabilities are already available within APEX, organizations do not necessarily need to create a separate frontend using technologies such as React or Angular. Instead, an AI assistant can be integrated directly into an existing Oracle APEX application and work alongside its existing modules. For example, an application may already contain modules such as Dashboard, Employees, Reports, Finance, and Settings, and an AI Assistant can be added as another module. This allows users to interact with the application's existing data and functionality through a conversational interface while continuing to use the same APEX authentication, authorization, database, and security architecture.
3.
High-Level Architecture
A practical AI chatbot architecture in Oracle APEX consists of several layers that work together to process user requests and generate intelligent responses. The user interacts with the Oracle APEX Chat UI, where components such as Dynamic Actions and AJAX Callbacks handle user interactions and send requests to the server. The request is then processed by the PL/SQL layer, which performs input validation, constructs the AI prompt, sends the API request, and processes the response returned by the AI service. The AI Service, which may use an LLM or other Generative AI model, processes the prompt and generates the appropriate response. The Oracle Database stores important information such as chat history, application data, and knowledge data that can be used to provide context to the AI. This architecture keeps the AI integration behind the application's server-side layer, which is important for security because API keys, authentication credentials, and other sensitive information should never be exposed directly in the browser. This separation also makes it easier to control access, validate requests, protect application data, and maintain the chatbot as part of the overall Oracle APEX application.
4. Create a Chat History Table
The first step in building an AI chatbot is to create a table for storing the conversation history. This table keeps track of messages exchanged between the user and the AI assistant, allowing the application to maintain context throughout a conversation. It can store information such as the user, session, message type, message content, and the time when the message was created. A MESSAGE_ROLE column can be used to identify whether a message was sent by the USER, generated by the ASSISTANT, or provided as a SYSTEM instruction.
Here, SESSION_ID identifies a particular conversation, while MESSAGE_ROLE identifies who generated each message. Storing these messages in the database allows the application to maintain conversation history, retrieve previous messages, provide context to the AI model, and display the complete conversation to the user.
5. Create the Chatbot Page in Oracle APEX
After creating the chat history table, the next step is to create the chatbot interface in Oracle APEX. Create a new page, for example, Page 50 – AI Assistant, which will provide the user with a simple interface for entering questions and viewing AI-generated responses. Create page items such as P50_MESSAGE to accept the user's question, P50_RESPONSE to display the AI response, and P50_SESSION_ID to identify the current conversation. A unique session identifier can be generated using Oracle's SYS_GUID() function. For example:
The generated value can be stored in P50_SESSION_ID and used to associate all messages belonging to the same conversation. The chatbot page can contain a chat area where previous user and assistant messages are displayed, along with a message input field and a Send button.
6. Build the Chat User Interface
After creating the chatbot page, the next step is to build the chat user interface that displays the conversation between the user and the AI assistant. In Oracle APEX, this can be implemented using a Classic Report, Cards, or a custom region. A Classic Report is a simple option because it can retrieve the conversation directly from the AI_CHAT_HISTORY table using the current session ID.
For example, the report query can be:
The P50_SESSION_ID item ensures that only messages belonging to the current conversation are displayed. The MESSAGE_ROLE column can then be used to distinguish between user and assistant messages. CSS can be applied to give each type of message a different appearance and create a more natural chat-style interface.
7. Create the User Message
When the user enters a question and clicks the Send button, the application should first save the user's message in the AI_CHAT_HISTORY table. This creates a record of the user's request and associates it with the current conversation using P50_SESSION_ID. The APP_USER value identifies the currently logged-in APEX user, while MESSAGE_ROLE is set to USER to indicate that the message was entered by the user.
The following PL/SQL can be used:
8. Prepare the AI Prompt
Before sending a user's question to an AI model, the application should prepare a clear and well-structured prompt. The prompt provides instructions that help the AI understand its role, the type of response expected, and how it should handle information that is not available. A well-designed prompt can help produce responses that are more consistent, relevant, and appropriate for the application.
For example, an Oracle APEX chatbot can use the following prompt:
You are an AI assistant inside an Oracle APEX application.
Answer the user's question clearly and professionally.
Do not invent database information.
If the required information is not available,
tell the user that the information is unavailable.
User Question:
What is Oracle APEX?The prompt can be constructed dynamically in PL/SQL by combining the chatbot instructions with the user's message:
Here, CHR(10) adds a new line to make the prompt easier to structure and read. The value of P50_MESSAGE contains the question entered by the user and is appended to the instructions before the prompt is sent to the AI service.
Prompt design is an important part of an AI chatbot because the instructions influence how the model interprets the request and generates its response. In an enterprise application, prompts can also include relevant application context, business rules, database information, and restrictions to help the AI provide responses that are appropriate for the application.
9. Call an AI Service
After preparing the AI prompt, the next step is to send the request to an AI service that provides access to a Generative AI or Large Language Model (LLM). Oracle APEX can communicate with an AI service through an HTTPS REST API using the APEX_WEB_SERVICE package. The basic flow is Oracle APEX → HTTPS REST API → AI Service → AI Model, where APEX sends the user's prompt to the service and receives the generated response. A simplified example using APEX_WEB_SERVICE.MAKE_REST_REQUEST is shown below:
In this example, p_url specifies the REST API endpoint, p_http_method specifies that a POST request is being used, and p_body contains the JSON request sent to the AI service. The actual endpoint, authentication method, request headers, JSON structure, and response format depend on the AI provider being used.
10. Process the AI Response
After the AI service receives the request, it normally returns the generated response in JSON format. The Oracle APEX application needs to process this JSON response and extract the actual text generated by the AI model. For example, a simplified API response might look like this:
{
"response": "Oracle APEX is a low-code development platform for building scalable web applications."
}Oracle Database provides JSON functions that can be used to extract specific values from the response. In this example, JSON_VALUE can be used to retrieve the value of the response property.
11. Store the AI Response
After receiving and processing the response from the AI service, the next step is to store the generated answer in the AI_CHAT_HISTORY table. The response is stored with MESSAGE_ROLE set to ASSISTANT, which allows the application to distinguish AI-generated messages from messages entered by the user. Storing both sides of the conversation makes it possible to display the complete chat history and provide previous conversation context when processing future questions.
The AI response can be stored using the following PL/SQL:
The complete simplified flow can be implemented as follows:
12. Use AJAX for a Better Chat Experience
Instead of submitting the entire Oracle APEX page every time the user sends a message, an AJAX Callback can be used to communicate with the server asynchronously. This provides a smoother chatbot experience because the page does not need to reload after every question. The browser sends the user's message to the APEX server, the server communicates with the AI service, and the generated response is returned to the page.
In Oracle APEX, create a PL/SQL AJAX Callback process with the following configuration:
Process Type: AJAX Callback
Process Name: CHAT_WITH_AIThe AJAX Callback can contain the following PL/SQL:
Here, APEX_APPLICATION.G_X01 receives the value sent from JavaScript. The process saves the user's message, calls the AI service, extracts the generated response, stores the response in the chat history table, and finally returns a JSON response to the browser.
The AJAX Callback can be called from JavaScript using apex.server.process:
13. Add Conversation Memory
A useful AI chatbot should be able to understand the context of previous messages instead of treating every question as a completely new conversation. Conversation memory allows the AI model to use earlier messages when generating a response. For example, a user may first ask, “What is Oracle APEX?”, and the AI may respond, “Oracle APEX is a low-code development platform.” If the user then asks, “What database does it use?”, the chatbot can understand that “it” refers to Oracle APEX because the previous message provides the necessary context.
The application can retrieve previous messages from the AI_CHAT_HISTORY table using the current session ID:
14. Connect the Chatbot to Oracle Database Data
This is where an Oracle APEX chatbot becomes much more useful. Instead of using the AI only as a general-purpose chatbot, it can be connected to application data so that users can ask questions about the current information stored in the Oracle Database.
For example, an Oracle APEX application may contain tables such as:
EMPLOYEE
CUSTOMER
HOTEL_PROPERTY
ROOM_TYPE_REF
ROOM_RATE_PLAN
STAFF_MEMBERA user could ask a question such as:
How many room types are available?Instead of asking the AI model to guess the answer, the application can first retrieve the required information directly from the Oracle Database.
For example:
15. Security and Production Best Practices
Security is one of the most important aspects of an AI chatbot, especially when the chatbot is integrated with enterprise application data and external AI services. A production-ready chatbot should protect API credentials, validate user input, enforce authorization, isolate tenant data, and handle external service failures safely.
Never Expose API Keys
API keys and other authentication credentials should never be exposed in client-side JavaScript.
For example, the following approach should be avoided:
const apiKey = "SECRET_KEY";Credentials included in JavaScript can potentially be viewed by users through browser developer tools. This can expose sensitive credentials and may allow unauthorized access to the AI service.
Instead, API credentials should be stored and accessed securely on the server side. The browser should communicate with Oracle APEX, and the APEX server should handle communication with the external AI service.
Validate User Input
User input should always be validated before it is processed or sent to an external AI service.
For example:
Additional validation can also be implemented based on application requirements, such as maximum message length, permitted input formats, authorization checks, and other application-specific rules.
Protect Tenant Data
For a multi-tenant Oracle APEX application, the chatbot must not allow one tenant to access another tenant's data.
For example, database queries should enforce the appropriate tenant context:
The tenant context should come from a trusted server-side mechanism rather than directly from a value supplied by the user.
This is particularly important when the chatbot is allowed to retrieve information from private application tables.
Use Authorization Schemes
Oracle APEX authorization schemes can be used to control access to the chatbot.
For example, an authorization scheme can be created with the name:
AI_CHAT_ACCESSOnly users who satisfy the authorization rules should be allowed to access the AI chatbot page or execute its server-side processes.
Authorization should be enforced on the server side rather than relying only on hiding the chatbot interface from unauthorized users.
Handle API Errors
External AI services can fail for several reasons, including:
- Network problems
- Invalid credentials
- Rate limits
- Service outages
- Invalid requests
- Request timeouts
- Response format changes
Therefore, appropriate exception handling should be implemented.
For example:
Detailed technical errors can be recorded in application logs for troubleshooting, while the end user should receive a safe and understandable message.
Internal database errors, SQL statements, API credentials, stack traces, and other sensitive implementation details should not be displayed directly to the user.
Conclusion
An AI chatbot can be integrated into Oracle APEX without replacing the existing APEX architecture. Instead, AI can be introduced as an additional intelligent component that works alongside the application's existing user interface, business logic, security mechanisms, and Oracle Database. A typical architecture consists of Oracle APEX → PL/SQL/AJAX → AI Service → AI Model → Oracle Database, where Oracle APEX provides the user interface and application logic, PL/SQL and AJAX handle server-side processing and communication, the AI service connects the application to the AI model, and Oracle Database provides structured application data and conversation history.
The most important design principle is that AI should be treated as an intelligent component of the application rather than as a replacement for the application's security, database, or business logic. The application should continue to control authentication, authorization, tenant isolation, database access, and business rules, while the AI model focuses on understanding natural-language requests and generating conversational responses.
Comments
Post a Comment