Building
AI-Powered Search in Oracle APEX
Introduction
Modern
business applications increasingly need more than traditional keyword-based
search. Users expect to search for information using natural language, even
when they do not know the exact words stored in the database.
Traditional
SQL search generally depends on exact words, LIKE conditions, or predefined
filters. AI-powered semantic search allows applications to understand the
meaning of the query and retrieve conceptually related information.
Oracle
APEX combined with Oracle AI Database provides a practical platform for
building this type of application. APEX handles the user interface,
authentication, reports, forms, and application logic, while Oracle Database
can store text, generate vector embeddings, and perform similarity searches.
This
blog explains how to build an AI-powered search feature in Oracle APEX using Oracle
AI Vector Search.
1. What Is AI-Powered Search?
AI-powered search is a search mechanism that understands the meaning and context of text rather than relying only on exact keyword matches. For example, consider records such as Laptop Computer, Notebook PC, Employee Computing Device, Desktop Computer, and Office Printer. A traditional SQL query such as WHERE LOWER(asset_name) LIKE '%laptop%' may return only records containing the word laptop. In contrast, semantic search can understand that a query such as “employee notebook computer” is semantically related to “Laptop Computer”, even though the exact words are different. AI-powered search typically works by converting the user's query into a vector embedding, performing a vector similarity search against stored embeddings in the Oracle Database, and returning the most relevant records. In an Oracle APEX application, the overall flow is User Query → Oracle APEX → Generate Query Embedding → Vector Similarity Search → Oracle Database → Relevant Records → APEX Report or Cards, providing users with more meaningful and intelligent search results.
2. Why Use Vector Embeddings?
Vector embeddings are used to convert text into a numerical representation that captures its meaning and context. For example, the text “Laptop computer” can be represented conceptually as a vector such as [0.021, -0.182, 0.451, 0.093, ...]. The actual vector contains many dimensions depending on the embedding model being used. When two pieces of text have similar meanings, their generated vectors tend to be mathematically close to each other in the vector space. This allows an AI-powered search system to find semantically related records even when they do not contain the same keywords. For example, “Laptop computer” and “Employee notebook PC” can produce similar embeddings, allowing the search system to identify them as related results.
3. Technology Stack
A typical AI-powered search implementation can use Oracle APEX as the user interface, PL/SQL for application logic, and Oracle Database or Oracle AI Database for storing and processing application data. Text can be converted into embeddings using an Oracle-supported embedding model and stored using the Oracle VECTOR data type. When a user performs a search, vector similarity techniques can compare the query embedding with stored vectors and identify semantically relevant records. The results can then be displayed through APEX Interactive Reports, Cards, or other APEX components. The key advantage of this architecture is that the Oracle Database remains the central location for both business data and its vector representations, allowing traditional relational queries and AI-powered semantic search to work together within the same application.
4. Create a Sample Business Table
Suppose we are building an Asset Management System. We can create a business table to store important asset information such as the asset name, description, category, status, purchase date, and warranty details. The following table provides a simple structure that can later be extended with vector embeddings for AI-powered search.
We can then insert sample asset records such as laptops, desktop computers, and printers:
This table provides the business data layer for our AI-powered search example. In the next step, we can generate vector embeddings from the asset name and description and store those embeddings alongside the existing asset information.
5. Create a Vector Column
Oracle AI Database provides the VECTOR data type for storing numerical representations of text generated by embedding models. We can add a vector column to the AI_ASSETS table to store the embedding of each asset description.
Here, 1536 represents the number of dimensions in the embedding, while FLOAT32 specifies the vector element format. The exact dimension must match the output of the embedding model being used. For example, if the selected embedding model generates 1,536-dimensional vectors, then VECTOR(1536, FLOAT32) is appropriate. The key rule is that the vector dimension and vector format must be compatible with the embedding model, otherwise the generated embeddings cannot be stored or compared correctly.
6. Generate Embeddings
The next step is to convert the business data into vector embeddings that represent its semantic meaning. Instead of generating an embedding only from the asset name, it is often useful to combine important fields such as the asset name, category, description, and status. This provides the embedding model with more contextual information and can improve semantic search results. For example, the searchable text for an asset could be created as follows:
For a Dell Latitude Laptop, the combined text might be: “Dell Latitude Laptop Computer Business laptop used by employees for daily office work. Allocated.” This complete text can then be passed to an embedding model to generate its vector representation.
Conceptually, the embedding operation can follow a pattern such as:
The generated vector is stored in the description_vector column and can later be compared with the vector generated from a user's search query. The exact embedding function, model name, and syntax depend on the Oracle Database version and the AI/embedding model configured in the environment.
7. Store the Embedding in the Database
Once an embedding has been generated, it is stored in the database alongside the corresponding business record. Each asset therefore has both its original business information and a vector representation of its meaning. For example, the AI_ASSETS table can conceptually contain an ASSET_ID, ASSET_NAME, and DESCRIPTION_VECTOR, where each vector represents the semantic meaning of that particular asset. This allows the database to perform similarity searches against the stored vectors and identify records that are semantically related to a user's query. Importantly, the original business data remains unchanged; the vector column is simply an additional representation that enables AI-powered semantic search.
8. Create a Vector Index
For a small dataset, a direct vector similarity query may be sufficient because the database has only a limited number of vectors to compare. However, as the number of records increases, a vector index can improve the performance of semantic searches by efficiently locating vectors that are close to the user's query vector. Oracle Database supports different vector indexing approaches depending on the database version and configuration. For example, an HNSW-style index can conceptually be created as follows:
The exact vector-index syntax, parameters, and supported indexing methods can vary between Oracle Database versions and configurations, so the syntax should always be matched to the environment being used. The main purpose of the vector index is to efficiently identify semantically similar vectors without requiring an exhaustive comparison with every stored record.
9. Generate an Embedding for the User's Query
When a user enters a search term such as “employee laptop computer”, Oracle APEX can store the value in a page item such as P10_SEARCH. The application then converts this search text into a vector embedding using the configured embedding model. Conceptually, this can be represented as:
The resulting vector is called the query vector. It can then be compared with the vector embeddings stored for the assets in the database. The overall search flow is User Query → Query Embedding → Vector Similarity Search → Similar Asset Vectors, allowing the application to find assets based on semantic meaning rather than requiring an exact keyword match. The exact embedding function and syntax should match the Oracle Database version and embedding model configured in the environment.
10. Perform Vector Similarity Search
Vector similarity search compares the query vector generated from the user's search text with the stored vectors associated with business records. Oracle provides vector distance functions that can be used for this comparison. A representative query is:
The VECTOR_DISTANCE function calculates the distance between the stored asset vector and the query vector. Depending on the selected distance metric, a smaller distance generally indicates greater semantic similarity. The query can therefore return the most relevant assets by sorting the results by distance and limiting the output to the top records. This vector comparison is the core of AI-powered semantic search, allowing users to discover relevant assets even when their search terms do not exactly match the stored asset names or descriptions.
11. Connect the Search to Oracle APEX
The next step is to connect the vector search functionality to an Oracle APEX page. For example, create Page 10 – AI Asset Search with a text field named P10_SEARCH and set its placeholder to “Search assets using natural language...”. You can provide an example such as “Find laptops used by employees” to guide users. Add a SEARCH button and create a report region that uses the vector similarity query to retrieve the most relevant assets.
Configure the report region to refresh when the SEARCH button is clicked. This creates a natural-language search experience where users can enter meaningful descriptions instead of relying only on exact asset names or keywords.
12. Improve the Search with Hybrid Search
Pure semantic search is powerful, but business applications can often benefit from combining keyword search with semantic search. This approach is known as hybrid search. Semantic search can understand relationships between terms such as laptop, notebook, and portable computer, while keyword search can provide precise matching for terms such as a specific brand or asset name, such as Dell.
A basic example can combine both approaches:
However, this is only a simple demonstration of the concept. In a production application, the search should use a more sophisticated ranking strategy that combines keyword relevance and vector similarity scores. This allows the application to take advantage of both exact matching and semantic understanding, providing more useful search results for business users.
13. Add Business Filters to AI Search
AI-powered search should complement, not replace, traditional business rules. For example, if a user searches for “Find available employee laptops”, semantic search can identify records related to laptops and employee computing devices, while SQL conditions can ensure that only assets with an AVAILABLE status are returned.
Business rules can also include conditions based on dates. For example, to find assets whose warranties are still valid:
This demonstrates an important enterprise architecture: AI understands the meaning of the user's request, while SQL enforces business rules and data conditions. Combining these capabilities allows an application to provide intelligent search while still respecting the organization's existing business logic and data requirements.
14. Build an APEX AI Search Interface
The final Oracle APEX interface can be designed as a simple and user-friendly AI Asset Search dashboard. The page can contain a search field where users enter natural-language queries such as “Find employee laptops”, along with a SEARCH button. The search results can then be displayed using an APEX Cards region, showing important information such as the asset name, category, status, description, and similarity distance. A basic Cards SQL query can be structured as follows:
For example, a result card could display Dell Latitude Laptop, its Computer category, ALLOCATED status, and a similarity distance such as 0.12, followed by its description. The Cards region can be refreshed using an APEX Dynamic Action when the user clicks the SEARCH button. This creates a clean AI-powered search experience where users can enter natural-language queries and receive semantically relevant business records directly within the APEX application.
15. Complete End-to-End Architecture and Conclusion
The complete AI-powered search architecture brings together Oracle APEX, an embedding model, PL/SQL/SQL, and Oracle Database. The user enters a natural-language query through an APEX search interface. The query is converted into an embedding vector, which is then compared with the vector embeddings stored alongside the business data. Oracle Database performs the vector similarity search and returns the most relevant records, which can be displayed through an APEX Report, Cards region, or dashboard.
Overall flow:
User → Oracle APEX → Natural-Language Query → Embedding Model → Query Vector → Oracle Database → Vector Similarity Search → Relevant Records → APEX Report/Cards
Complete SQL Flow
Step 1 — Create the table
Step 2 — Insert business data
Step 3 — Generate embeddings
The embedding function and model should be configured according to the Oracle environment:
Step 4 — Perform the semantic search
Step 5 — Display results in APEX
Create a page item such as:
P10_SEARCH
Then create an APEX Cards or Interactive Report region using the vector search query. The user can enter a query such as:
Conclusion
AI-powered search does not require replacing an existing Oracle APEX application with a completely new AI platform. Oracle APEX can provide the user-facing experience, while Oracle Database handles business data, vector storage, and similarity search. By combining traditional SQL capabilities with AI-powered semantic search, applications can provide users with more natural and meaningful ways to find information.
This architecture provides a strong foundation for building advanced solutions such as semantic document search, AI assistants, Retrieval-Augmented Generation (RAG) applications, recommendation engines, document intelligence, and AI-powered enterprise dashboards.
Available next action: Create a downloadable PDF file here in this chat containing the finalized decisions and immediate actions above
Comments
Post a Comment