Oracle APEX
with Oracle AI Database
Oracle
APEX and Oracle AI Database provide a powerful platform for building modern,
data-driven applications with AI capabilities directly alongside enterprise
database functionality. Oracle AI Database combines traditional relational
database features with capabilities for working with AI workloads, including
vector data and similarity search. Oracle APEX can provide the application
layer for these capabilities, allowing developers to build forms, dashboards,
reports, search interfaces, and AI-powered features using familiar SQL and
PL/SQL.
1.
What is Oracle AI Database?
Oracle
AI Database is designed to support traditional enterprise workloads together
with modern AI and machine-learning use cases. Instead of moving application
data to a separate database or AI-specific system, developers can work with
relational data, JSON, vector data, SQL, PL/SQL, and AI-related workloads
within the Oracle Database environment.
Oracle
APEX can then be used as the low-code application development layer.
This
architecture is useful for applications such as document search, recommendation
systems, knowledge portals, customer-support applications, and intelligent
dashboards.
2. Why Use APEX with Oracle AI Database?
Oracle APEX provides the user interface, application workflows, and business logic, while Oracle AI Database provides the underlying data management and AI capabilities. Combining these technologies allows developers to build modern applications that can work with both traditional relational data and AI-powered features such as semantic search, vector similarity, recommendations, intelligent dashboards, document management, knowledge bases, customer-support systems, and natural-language interfaces. For example, an APEX application could allow a user to enter a query such as "Find documents related to hotel room booking." Instead of searching only for the exact words entered by the user, the application can convert the query into a vector representation and perform a similarity search against stored document embeddings. This allows the application to find documents with similar meaning or context, even when they do not contain the exact search terms, making the overall search experience more intelligent and useful.
3.
Create a Table for Application Data
Start
with a normal relational table.
For
example, suppose we are building a document-management application.
Insert
sample data:
The
relational database remains the foundation of the application.
4. Store Vector Embeddings
One important capability of Oracle AI Database is the ability to store and work with vector embeddings. A vector embedding represents information as a numerical vector, allowing similar pieces of content to be compared mathematically. For example, the text “Hotel booking” can be converted into a vector such as [0.12, -0.45, 0.78, 0.31, ...]. Content with similar meanings will generally have vectors that are mathematically close to each other. Oracle AI Database provides a VECTOR data type that can be used to store these embeddings directly in a table. The vector dimension depends on the embedding model being used. For example, the following table stores a 1536-dimensional FLOAT32 embedding for each document:
Here, document_id identifies the original document, while the embedding column stores its numerical vector representation. This stored vector can later be used for semantic search, similarity matching, recommendation systems, and AI-powered retrieval.
5. Generate Embeddings
The next step is to convert text into vector embeddings. An embedding model transforms text into a numerical vector that captures the semantic meaning of the text. Oracle AI Database can work with embedding-generation capabilities depending on the configured AI services, embedding model, and database environment. A conceptual example of generating an embedding is shown below:
In this example, 'Hotel booking process' is the input text, while my_embedding_model represents the configured embedding model. The actual model name, syntax, and configuration may vary depending on the Oracle environment and embedding model being used. The generated result is a numerical vector that can then be stored in an Oracle VECTOR column.
6. Perform Vector Similarity Search
After storing vector embeddings, an application can perform a vector similarity search to find documents that are semantically similar to a given query. Instead of searching only for exact words, vector similarity compares the mathematical distance between the query vector and the vectors stored in the database. For example, the following query uses cosine distance to find the 10 most similar documents:
Here, :P10_QUERY_VECTOR represents the vector embedding generated from the user's search query. VECTOR_DISTANCE() calculates the distance between the query vector and each stored document vector using the COSINE distance metric. The results are sorted in ascending order, so documents with a smaller distance are more semantically similar to the query.
This approach is different from traditional keyword-based searching. For example:
Traditional search looks for an exact or partial word match, whereas vector search can identify documents with similar meaning, even when they use different words. For example, a search for “hotel reservation” could potentially find content discussing “booking a room” because the meanings are semantically related.
7. Build an AI-Powered Search Page in APEX
Oracle APEX can provide the user interface for semantic search by combining page items, buttons, PL/SQL processes, and SQL queries with Oracle AI Database's vector capabilities. First, create a page item such as P10_SEARCH where the user can enter a natural-language query, for example, “Find information about cancelling a hotel reservation”. A Search button can then trigger a process that converts the search text into a vector embedding and stores or passes the resulting vector as P10_QUERY_VECTOR. The report can then compare this query vector with the document embeddings and return only documents that are sufficiently similar.
For example, the report query can be written as:
In this query, VECTOR_DISTANCE() calculates the cosine distance between the user's query vector and each document's stored embedding. The 0.40 value acts as a similarity threshold, so documents whose distance is below that value are returned. The results are ordered by distance, allowing the most semantically similar documents to appear first.
9. Build a RAG-Style Application
Retrieval-Augmented Generation (RAG) combines document retrieval with generative AI to provide answers based on relevant information stored in the database. In an Oracle APEX application, a user can enter a natural-language question such as “What is the cancellation policy for Hotel A?”. APEX can convert the question into a vector embedding and use vector similarity search to retrieve the most relevant documents from Oracle AI Database. The retrieved document content is then provided as context to an AI model, which generates an answer based on that information. Finally, the generated response can be displayed to the user in an APEX region.
10. Call AI Services from Oracle APEX
Oracle APEX can communicate with external AI services through REST APIs using the APEX_WEB_SERVICE package. This allows an APEX application to send user input or retrieved document context to an AI service and receive a generated response. For example, a PL/SQL process can make a POST request to an AI endpoint and store the returned response in an APEX page item:
In this example, :P10_AI_ENDPOINT contains the AI service endpoint, while :P10_REQUEST_BODY contains the request payload, such as the user's question and relevant document context. The response returned by the AI service is stored in :P10_AI_RESPONSE and can then be displayed in an APEX region.
For production applications, sensitive authentication information such as API keys and credentials should not be hard-coded in PL/SQL. Instead, APEX Web Credentials or the appropriate secure credential mechanism should be used.
11. Store AI-Generated Results
AI-generated information can also be stored in the Oracle Database so that the application can maintain a history of previous interactions. This is useful for auditing, tracking user questions and AI responses, analyzing application usage, and allowing users to retrieve previous conversations or results. For example, an ai_responses table can be created as follows:
After an AI response is generated, it can be stored in the table using an INSERT statement:
Here, :P10_QUESTION contains the question submitted by the user, :P10_AI_RESPONSE contains the response generated by the AI service, and :APP_USER identifies the current APEX user. The created_on column automatically records the date and time when the interaction is stored.
12. Build an AI Chat Interface in APEX
Oracle APEX can also be used to build a simple AI chat assistant interface. The APEX page can contain two page items: P20_MESSAGE for the user's question and P20_RESPONSE for displaying the AI-generated response. For example, the user could enter “How can I modify a room reservation?” and click a Submit button. The button can execute a PL/SQL process that calls a dedicated AI assistant package:
Instead of placing all AI integration logic directly inside the APEX page process, it is better to encapsulate that logic in a reusable PL/SQL package:
The package body can then contain the implementation for generating embeddings, retrieving relevant documents, calling an AI REST service, processing the response, and optionally storing the interaction in the database.
14. Security and Multi-Tenant AI Applications
Security is especially important when AI features are connected to enterprise and multi-tenant data. In a multi-tenant application, every database query and AI-powered search operation should ensure that users can access only the data belonging to their authorized tenant. For example, a normal document query can apply a tenant filter as follows:
The same tenant restriction must also be applied when performing vector similarity searches:
Here, the tenant_id condition ensures that the vector search is performed only against documents belonging to the current tenant. The AI search layer should never be allowed to bypass the application's existing tenant-level security controls.
15. Complete Oracle APEX + Oracle AI Database Architecture
A complete AI-powered Oracle APEX application can combine the APEX user interface, SQL and PL/SQL business logic, Oracle AI Database capabilities, vector search, and external or configured AI services into a unified architecture. Oracle APEX provides the application interface through pages, forms, reports, and dashboards, while SQL and PL/SQL handle data access and business logic. Oracle AI Database can manage relational, JSON, and vector data and perform similarity searches. AI services can then use the retrieved information to support features such as semantic search, RAG, recommendations, and intelligent reporting.
Conclusion
Oracle APEX and Oracle AI Database provide a strong foundation for developing modern AI-enabled business applications. Oracle APEX provides the user interface, application workflows, authentication, authorization, SQL reports, dashboards, and PL/SQL processing, while Oracle AI Database extends the database layer with capabilities such as vector storage, embedding management, and similarity search.
By combining these capabilities, developers can build a wide range of AI-powered applications, including semantic search, recommendation systems, document intelligence, Retrieval-Augmented Generation (RAG), AI assistants, and intelligent dashboards. These capabilities can be integrated with familiar Oracle SQL and PL/SQL, allowing developers to incorporate AI into existing application architectures.
The combination is particularly useful for enterprise applications, because AI capabilities can work alongside existing relational data, business rules, authentication and authorization mechanisms, security controls, and multi-tenant architectures. This allows organizations to introduce AI features while continuing to use their existing Oracle-based application and data infrastructure.
Comments
Post a Comment