Integrating LLMs with Oracle APEX: A Complete Practical Guide

Large Language Models (LLMs) are changing how enterprise applications interact with users. Oracle APEX provides a low-code development environment, while Oracle Database provides SQL, PL/SQL, security, and increasingly native AI capabilities. Together, they can be used to build applications such as AI chat assistants, natural-language search, document summarization, semantic search, RAG applications, and AI-powered dashboards.

Oracle APEX can connect to generative AI services including OCI Generative AI, OpenAI, Cohere, Google Gemini, Anthropic Claude, Mistral AI, Ollama, and OpenAI-compatible APIs.

1. What is an LLM?

A Large Language Model (LLM) is an artificial intelligence model designed to understand, process, and generate human language. LLMs are trained on large amounts of text data, which enables them to understand questions, summarize information, generate content, translate languages, write code, and interact with users using natural language. Popular examples of LLMs include OpenAI GPT models, Google Gemini, Anthropic Claude, OCI Generative AI models, Cohere, Mistral, and locally hosted models such as Ollama.

In a traditional Oracle APEX application, the interaction generally follows a straightforward architecture where the user interacts with an APEX page, the request is processed using PL/SQL, the application retrieves or modifies data in the Oracle Database, and the result is displayed back to the user. This architecture is highly effective for structured business applications such as employee management, asset management, hotel management, finance, and inventory systems.

With LLM integration, Oracle APEX applications can be extended with an intelligent natural-language layer. In this architecture, the user submits a question through an APEX page, the request is passed through APEX_AI or a REST API to the configured LLM service, and the generated response is returned to the APEX application and displayed to the user. This allows users to interact with enterprise applications using natural language instead of relying entirely on predefined forms, filters, reports, and menus.

2. Why Integrate LLMs with Oracle APEX?

Oracle APEX already provides a comprehensive platform for developing enterprise applications with features such as forms, interactive reports, interactive grids, charts, authentication, authorization, PL/SQL processing, REST API integration, and direct access to Oracle Database. These capabilities make APEX suitable for building applications across areas such as asset management, finance, human resources, hospitality, inventory, and customer management. However, traditional applications generally require users to navigate through screens, apply filters, select options, and understand predefined workflows to find the information they need.

Integrating a Large Language Model (LLM) adds a natural-language intelligence layer to these applications. Instead of depending entirely on traditional forms and reports, users can communicate with the application using normal language. The LLM can understand the user's request, work with relevant application data, and generate a response in a conversational format.

3. Oracle APEX Generative AI Architecture

A modern Oracle APEX Generative AI architecture connects the APEX user interface with an AI service and the Oracle Database. Users can interact with AI-powered features through chat interfaces, reports, forms, search pages, or dashboards. APEX can communicate with a configured Generative AI Service or an external web service, which sends the request to an LLM provider such as OpenAI, OCI Generative AI, or another supported provider. The LLM processes the request and generates a response, while Oracle Database remains responsible for storing and retrieving business data using SQL and PL/SQL. Recent APEX versions provide Generative AI Service configuration capabilities that allow developers to define the AI provider, endpoint, model, and authentication credentials, making it easier to integrate AI capabilities into existing APEX applications without building the entire integration layer from scratch.

4. Configure a Generative AI Service in APEX

Oracle APEX allows developers to configure a Generative AI Service that acts as a connection between the APEX application and an external AI provider. The general configuration can be accessed through App Builder → Workspace Utilities → All Workspace Utilities → Generative AI. When creating a service, developers typically define important properties such as the Provider, Endpoint, Model, Credentials, and Static ID. The provider determines which AI platform is used, while the endpoint and model specify where requests are sent and which AI model processes them. Credentials are used to securely authenticate requests, and the Static ID provides a stable identifier that can be referenced when accessing the configured service programmatically through APEX AI capabilities. For example, a service could be configured with the name My AI Service, Static ID MY_AI_SERVICE, an OpenAI-compatible provider, and the required model and endpoint. The exact endpoint, authentication method, and model depend on the AI provider being integrated.

5. Secure API Keys with APEX Web Credentials

When integrating external LLM providers with Oracle APEX, API keys should never be hard-coded directly into PL/SQL code, application processes, or page items. Hard-coding secrets such as l_api_key := 'sk-xxxxxxxxxxxxxxxx'; can expose sensitive credentials through source code, version control, logs, or application exports. Instead, Oracle APEX provides Web Credentials to securely manage authentication information used by REST APIs and external services. A typical configuration can be created through Workspace Utilities → Web Credentials → Create Credential, where developers can define a credential name, authentication method, header name, and secret value. For example, an HTTP Header authentication credential could use the name OPENAI_API, with Authorization as the header name and a value such as Bearer <API_KEY>. The application can then use the stored credential when making API requests without directly exposing the API key in PL/SQL. For production applications, restricting the credential to the intended API endpoint provides an additional security layer and helps prevent unintended use of the stored secret.

6. Calling an LLM REST API from PL/SQL

One of the most flexible ways to integrate a Large Language Model (LLM) with Oracle APEX is through the APEX_WEB_SERVICE package. Oracle APEX provides the APEX_WEB_SERVICE.MAKE_REST_REQUEST procedure for making HTTP requests to external REST APIs directly from PL/SQL. The application can construct a JSON request containing the selected model and user prompt, send it to the configured LLM endpoint, and store the returned response in a CLOB for further processing or display in the APEX interface. By using the p_credential_static_id parameter, the API authentication can be handled through an APEX Web Credential instead of exposing the API key in the source code. The exact request body, endpoint, authentication requirements, and response format depend on the LLM provider being used.


This approach provides a clean integration pattern where APEX handles the application and user interface, PL/SQL manages the API request, Web Credentials protect authentication details, and the external LLM generates the AI response.

7. Create an AI Chat Page in Oracle APEX

Oracle APEX can be used to create a simple AI chat interface where users submit questions and receive responses generated by an LLM. A page can contain two main items: P10_QUESTION for entering the user's question and P10_RESPONSE for displaying the generated answer. An Ask AI button can submit the page and trigger a PL/SQL process. The process builds a JSON request using Oracle's JSON_OBJECT and JSON_ARRAY functions, sends the request to the configured LLM REST endpoint through APEX_WEB_SERVICE.MAKE_REST_REQUEST, and extracts the generated text from the JSON response using JSON_VALUE. The resulting answer is then assigned to P10_RESPONSE, creating a simple question → LLM → response workflow within the APEX application.


8. Use APEX_AI for Generative AI

When a Generative AI Service is configured in Oracle APEX, developers can use the APEX AI APIs to interact with an LLM without manually constructing every REST request and handling provider-specific HTTP details in application code. The architecture becomes simpler: the APEX page sends a prompt to APEX_AI, which communicates with the configured Generative AI Service, and the service forwards the request to the selected LLM. For example, an asset management application could provide a prompt such as, “Summarize the following asset maintenance record,” along with details about an asset, its reported issue, and the technician's recommendation. The generated summary can then be displayed in an APEX item, report, or region. This approach helps separate business and application logic from AI-provider-specific integration details, making the application easier to maintain and allowing the configured AI service to manage the underlying provider connection.

9. Build an AI-Powered Database Assistant

An AI-powered database assistant allows users to query database information using natural language instead of writing SQL. For example, a user can ask, “Show IT employees located in Pune,” and the LLM can generate the corresponding SQL query. However, generated SQL should always be validated before execution. A secure implementation should allow only read-only operations such as SELECT and reject commands such as DROP, DELETE, UPDATE, and INSERT.

Example table:

Uploading: 8913 of 8913 bytes uploaded.

User asks:

Show IT employees located in Pune.

AI-generated SQL:


10. Implement AI Summarization

AI summarization can make long enterprise records easier to understand. For example, in an asset maintenance system, users may have lengthy issue descriptions and technician notes. APEX can combine this information into a prompt and send it to a configured LLM to generate a short summary, such as three bullet points. The summary can then be displayed in an APEX item or region.

Example table:


PL/SQL example:


11. Add Semantic Search with Vector Embeddings

Traditional search usually looks for matching keywords, such as LIKE '%laptop%'. Semantic search goes further by searching based on the meaning of the text. Oracle AI Database 26ai provides a native VECTOR data type for storing embeddings and supports vector search. An embedding model converts text into numerical vectors, allowing sentences with similar meanings to be found even when they use different words.

Example:


12. Build RAG with Oracle Database

RAG (Retrieval-Augmented Generation) allows an LLM to answer questions using relevant information retrieved from your own database or documents. Instead of asking an LLM to know a company's private leave policy, the application first performs a vector search, retrieves the relevant document, and then sends that context along with the question to the LLM.

13. Example RAG Database Design

A simple table can store documents together with their vector embeddings:


Insert a document:


After generating embeddings, the vector can be stored with the original document. A semantic search can then find the most relevant documents:


14. Create an AI-Powered APEX Application

By combining Oracle APEX, APEX_AI, APEX_WEB_SERVICE, an LLM, and Oracle AI Database, developers can build intelligent enterprise applications. For example, an AI Asset Management Assistant can allow users to ask questions such as “Show laptops that require maintenance.” The application can retrieve relevant asset information, process the request through the configured AI service, and display the results directly in the APEX interface.

15. Security, Performance and Production Best Practices

LLM integration should be treated as an enterprise integration, not simply as adding a chatbot. Applications should protect API credentials, follow least-privilege access, avoid sending unnecessary sensitive data, and validate AI-generated SQL before execution. For enterprise knowledge, RAG and vector search can reduce the amount of data sent to the LLM and improve the relevance of responses. AI requests can also be logged for auditing, but sensitive information and secrets should not be stored unnecessarily.

Example AI request log:


For production systems, avoid logging API keys, passwords, unnecessary personal information, or other sensitive data.

Complete APEX AI Example

Create two page items:

P10_QUESTION
P10_RESPONSE

Add an ASK_AI button and create a PL/SQL process:






This provides a basic APEX → LLM → Response workflow while keeping authentication details outside the application code through APEX Web Credentials. For production implementations, additional controls such as input validation, authorization, error handling, rate limiting, audit logging, data filtering, and monitoring should be applied. APEX_WEB_SERVICE provides the mechanism for APEX/PLSQL applications to invoke external web services.

 

Final Architecture

Conclusion

Integrating LLMs with Oracle APEX enables traditional enterprise applications to support intelligent features such as natural-language interaction, summarization, semantic search, and conversational interfaces while retaining APEX for the user interface, authentication, workflows, and application logic. Oracle Database continues to manage enterprise data, while Oracle AI Database 26ai, Vector Search, and RAG can provide semantic retrieval and context for AI responses. A practical architecture is Oracle APEX → APEX_AI/REST → LLM → Vector Search → Relevant Enterprise Data → RAG → Grounded AI Response. This approach keeps the Oracle Database as the central source of enterprise data while using the LLM as an intelligent natural-language layer over the existing application.

Comments

Popular posts from this blog

Implementing Authentication and Authorization in Oracle APEX

Mastering Shared Components in Oracle APEX

Designing a Scalable Database Architecture for Oracle APEX Applications