Building Semantic Search (Vector Search) in Oracle APEX using Oracle 26 AI & ONNX (MiniLM)

Introduction

Traditional keyword-based search has clear limitations. It relies on exact word matching and often fails to understand the actual intent behind a user's query. For example, searching for "large striped wild cat" wouldn't find results containing only the word "tiger" in a traditional search system.

With Oracle Database 26 AI and ONNX-based embedding models, we can now build true semantic search directly inside the database—no external vector databases or complex infrastructure required.

In this post, we'll build a complete semantic search application in Oracle APEX that understands meaning instead of keywords, featuring:

• Oracle APEX low-code development
• Oracle Database 26 AI Vector Search
• ONNX MiniLM embedding model
• Cards UI for rich visual results
• Real-time vectorization

Note: This post continues from "Preparing Oracle 26ai Database for ONNX-Based Vector Search: A Step-by-Step Walkthrough". Please complete that setup before proceeding.

Building Semantic Search (Vector Search) in Oracle APEX using Oracle 26 AI & ONNX (MiniLM)

---

Watch Complete setup Video tutorial here:

Understanding Semantic Search: Theory & Concepts

What is Semantic Search?

Semantic search focuses on meaning, not keywords. Instead of comparing text strings character by character, semantic search converts both the stored content and the user's search query into numerical vectors (embeddings). These vectors are then compared mathematically to measure how close their meanings are.

This approach allows searches like "striped carnivorous cat that swims" to correctly return Tiger, even though the word "tiger" never appears in the search text.

How Vector Embeddings Work

When text is converted into a vector embedding:

1. The text is processed by a machine learning model (in our case, MiniLM)
2. The model converts the text into a high-dimensional numerical vector (typically 384 or 768 dimensions)
3. This vector captures the semantic meaning, context, and relationships within the text
4. Similar concepts produce vectors that are close together in vector space

For example:

• "Dog" and "Puppy" would have vectors close together
• "Dog" and "Spaceship" would have vectors far apart

Understanding Cosine Distance & Similarity

When comparing two vectors, we use cosine similarity, which measures the angle between vectors rather than their absolute distance.

Conceptual view: Vectors with similar meanings point in similar directions, resulting in smaller angles and higher cosine similarity

Cosine Distance ranges from 0 to 2:

• 0 = Identical meaning (vectors point in same direction)
• 1 = Orthogonal (no similarity)
• 2 = Opposite meaning

Cosine Similarity is the inverse: 1 - Cosine Distance

• 1 = Perfect match
• 0 = No similarity

Converting to Similarity Percentage

To make results user-friendly, we convert cosine similarity into a percentage:

Similarity % = (1 - Cosine Distance) × 100

We can then categorize match quality:

• Excellent Match: ≥ 80%
• Good Match: 60–79%
• Fair Match: 40–59%
• Low Match: < 40%

This makes semantic relevance easy to understand for non-technical users.

Oracle's Vector Functions

Oracle Database 26 AI provides native vector search capabilities through two key functions:

1. VECTOR_EMBEDDING()
Converts text into a vector using a specified model:

VECTOR_EMBEDDING(
    MINILM_MODEL USING 'Your text here' AS DATA
)

2. VECTOR_DISTANCE()
Compares two vectors and returns their distance:

VECTOR_DISTANCE(
    vector1,
    vector2,
    COSINE  -- similarity metric
)

These functions enable semantic search entirely within SQL—no external APIs or services required.

---

Application Architecture

Application Overview

• Application Name: Semantic Search Contents
• Page 1: Semantic Search Interface (Cards View)
• Page 2: Add Animal Form (Modal Dialog)
• Database Table: ANIMAL_DETAILS
• Embedding Model: MINILM_MODEL (ONNX)
• Search Approach: Real-time vector similarity matching

Database Schema

CREATE TABLE ANIMAL_DETAILS (
    CARD_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    TITLE VARCHAR2(200),
    DESCRIPTION CLOB,
    IMAGE_URL VARCHAR2(500),
    EMBEDDING VECTOR,
    CREATED_ON DATE
);

Key Point: The EMBEDDING column stores the vector representation of each animal's semantic meaning, automatically generated from the title and description.

---

APEX Configuration & Setup

Page 1: Semantic Search Interface

Page Item Configuration

Search Input Field:

• Item Name: P1_SEMANTIC_SEARCH
• Type: Text Field
• Label: Search Animals
• Placeholder: Search by meaning, not keywords…

Cards Region: Search Results

Region Type: Cards

SQL Query:

SELECT 
    CARD_ID,
    TITLE,
    DESCRIPTION,
    IMAGE_URL,
    CREATED_ON,
    ROUND((1 - VECTOR_DISTANCE(
        EMBEDDING, 
        VECTOR_EMBEDDING(MINILM_MODEL USING :P1_SEMANTIC_SEARCH AS DATA),
        COSINE
    )) * 100, 2) AS SIMILARITY_PERCENTAGE,
    CASE 
        WHEN ROUND((1 - VECTOR_DISTANCE(EMBEDDING, 
            VECTOR_EMBEDDING(MINILM_MODEL USING :P1_SEMANTIC_SEARCH AS DATA),
            COSINE)) * 100, 2) >= 80 THEN 'Excellent Match'
        WHEN ROUND((1 - VECTOR_DISTANCE(EMBEDDING, 
            VECTOR_EMBEDDING(MINILM_MODEL USING :P1_SEMANTIC_SEARCH AS DATA),
            COSINE)) * 100, 2) >= 60 THEN 'Good Match'
        WHEN ROUND((1 - VECTOR_DISTANCE(EMBEDDING, 
            VECTOR_EMBEDDING(MINILM_MODEL USING :P1_SEMANTIC_SEARCH AS DATA),
            COSINE)) * 100, 2) >= 40 THEN 'Fair Match'
        ELSE 'Low Match'
    END AS MATCH_QUALITY,
    TO_CHAR(CREATED_ON, 'DD-MON-YYYY') AS FORMATTED_DATE
FROM 
    ANIMAL_DETAILS
WHERE 
    EMBEDDING IS NOT NULL
ORDER BY 
    VECTOR_DISTANCE(
        EMBEDDING, 
        VECTOR_EMBEDDING(MINILM_MODEL USING :P1_SEMANTIC_SEARCH AS DATA),
        COSINE
    ) ASC
FETCH FIRST 10 ROWS ONLY;

Query Breakdown:

1. VECTOR_EMBEDDING converts the user's search text into a vector
2. VECTOR_DISTANCE compares stored embeddings with the search vector
3. Results are ordered by distance (smallest = most similar)
4. FETCH FIRST 10 limits results to top matches
5. Similarity percentage provides user-friendly scoring

Cards Configuration:

• Primary Key Column 1: &CARD_ID.
• Title: &TITLE.
• Body: &DESCRIPTION.
• Secondary Body: 
• Advanced Formatting: Yes
• HTML Expression:

<span>Similarity Percentage: &SIMILARITY_PERCENTAGE. %</span>

• Icon & Badge:
Icon Source
No IconInitialsIcon ClassIcon Class ColumnImage URLImage BLOB Column
Image URL
Icon CSS Classes
Icon Position
TopStartEnd
Icon Description
Badge Column
- Select -CARD_IDTITLEDESCRIPTIONIMAGE_URLCREATED_ONSIMILARITY_PERCENTAGEMATCH_QUALITYFORMATTED_DATE
Badge Label

Dynamic Action: Auto-Refresh on Search

Event Configuration:

• Event: Change
• Selection Type: Item
• Item: P1_SEMANTIC_SEARCH

True Action:

• Action: Refresh
• Selection Type: Region
• Region: [Your Cards Region]

This ensures the search results update immediately as the user types.

---

Page 2: Add Animal (Modal Dialog)

Page Items

P2_TITLE

• Type: Text Field
• Label: Animal Name
• Required: Yes

P2_DESCRIPTION

• Type: Textarea
• Label: Description
• Required: Yes
• Height: 5 rows

P2_IMAGE_URL

• Type: Text Field
• Label: Image URL
• Required: Yes
• Placeholder: https://example.com/image.jpg

Image Preview Region

Region Type: Static Content

HTML Source:

<img id="imgPreview" 
     src="" 
     style="max-width:100%; max-height:300px; display:none; border-radius:8px; margin-top:10px;" 
     alt="Image Preview"/>

Dynamic Action: Live Image Preview

Event Configuration:

• Event: Change
• Selection Type: Item
• Item: P2_IMAGE_URL

True Action:

• Action: Execute JavaScript Code

JavaScript Code:

var url = $v("P2_IMAGE_URL");
var img = document.getElementById("imgPreview");

if (url && url.startsWith("http")) {
    img.src = url;
    img.style.display = "block";
} else {
    img.style.display = "none";
}

This provides instant visual feedback without page submission.

Process 1: Insert & Vectorize Data

Process Type: Execute Code

PL/SQL Code:

INSERT INTO ANIMAL_DETAILS (
    TITLE, 
    DESCRIPTION, 
    IMAGE_URL, 
    EMBEDDING,
    CREATED_ON
)
VALUES (
    :P2_TITLE,
    :P2_DESCRIPTION,
    :P2_IMAGE_URL,
    VECTOR_EMBEDDING(
        MINILM_MODEL USING :P2_TITLE || ' : ' || :P2_DESCRIPTION AS DATA
    ),
    SYSDATE
);

Key Point: The embedding is generated automatically during insert by combining the title and description, then passing them to the ONNX model.

Success Message: Animal added successfully!

Process 2: Close Dialog

• Process Type: Close Dialog
• Execution Point: After Processing

---

Parent Page Dynamic Action: Refresh After Dialog Close

Configure on Page 1:

Event Configuration:

• Event: Dialog Closed
• Selection Type: JavaScript Expression
• JavaScript Expression: window

True Action:

• Action: Refresh
• Selection Type: Region
• Region: [Your Cards Region]

This ensures the main page shows the newly added animal immediately after the dialog closes.

---

How It All Works Together

Adding a New Animal

1. User clicks "Add Animal" button on Page 1
2. Modal dialog (Page 2) opens
3. User enters title, description, and image URL
4. Live preview shows the image as they type the URL
5. On submit:
   • Text is converted to a vector embedding
   • Record is inserted into the database
   • Dialog closes
   • Page 1 automatically refreshes
   • New animal is immediately searchable

Performing a Semantic Search

1. User types a natural language query (e.g., "fast African animal with spots")
2. Dynamic action triggers on keystroke
3. Query is converted to a vector embedding
4. Database compares this vector against all stored embeddings
5. Results are ranked by cosine similarity
6. Top 10 matches display as cards with similarity percentages
7. Results might include "Cheetah" even though those exact words weren't in the description

---

Why This Architecture Works So Well

✓ No External Dependencies
Everything runs inside Oracle Database—no external vector databases or API calls required.

✓ SQL-Based AI
Semantic search is just SQL, making it easy to understand, maintain, and integrate.

✓ Real-Time Vectorization
Embeddings are generated on insert, so new data is immediately searchable.

✓ Enterprise-Ready
Built on Oracle's proven security, scalability, and reliability.

✓ Low-Code Development
APEX makes it simple to build sophisticated AI applications without extensive coding.

---

Final Thoughts

Oracle Database 26 AI brings semantic search directly into the database, eliminating the complexity of external vector stores and API integrations. Combined with Oracle APEX's low-code capabilities, you can build intelligent, AI-driven search applications using familiar SQL and visual development tools.

This approach scales well, maintains enterprise security standards, and opens the door to advanced AI-driven applications without leaving the Oracle ecosystem.

What's Next?
In upcoming posts, we'll explore:

• Performance optimization with vector indexes
• Advanced embedding strategies for better accuracy
• Multi-modal search (combining text and images)
• Integration patterns for enterprise applications