Oracle AI Vector Search: Adding Semantic Search to Your APEX Application

Beyond Keyword Search

Traditional database search matches exact keywords. When a user searches for “laptop” they will not find products described as “portable computer” or “notebook.” Oracle Database 23ai introduced AI Vector Search, which stores mathematical representations (embeddings) of text meaning and finds results based on semantic similarity rather than keyword matching. This brings the intelligence of modern AI search into your SQL queries.

How Vector Search Works

Each piece of text is converted into a high-dimensional vector (array of numbers) by an embedding model. Texts with similar meanings produce vectors that are close together in vector space. Oracle stores these vectors in a VECTOR column type and provides operators to find the nearest vectors to a query vector.

Setting Up Vector Search

-- Add a vector column to your products table
ALTER TABLE products ADD (
    description_vector VECTOR(1536, FLOAT32)
);

-- Create a vector index for fast similarity search
CREATE VECTOR INDEX idx_product_vectors ON products(description_vector)
    ORGANIZATION NEIGHBOR PARTITIONS
    DISTANCE COSINE
    WITH TARGET ACCURACY 95;

Generating Embeddings

Use Oracle’s built-in DBMS_VECTOR package or call an external embedding API:

-- Using Oracle's built-in embedding model (23ai)
UPDATE products
   SET description_vector = DBMS_VECTOR.UTL_TO_EMBEDDING(
       description,
       JSON('{"provider":"database","model":"doc_model_v1"}')
   )
 WHERE description_vector IS NULL;

-- Or call OpenAI/Cohere from PL/SQL
DECLARE
    l_embedding VECTOR(1536, FLOAT32);
BEGIN
    l_embedding := get_embedding_from_api(p_text => :P10_SEARCH_TERM);
END;

Querying With Vector Search in APEX

-- APEX report source: find products similar to search query
SELECT product_id, product_name, description,
       VECTOR_DISTANCE(description_vector, :search_vector, COSINE) AS similarity
  FROM products
 WHERE status = 'ACTIVE'
 ORDER BY VECTOR_DISTANCE(description_vector, :search_vector, COSINE)
 FETCH APPROXIMATE FIRST 20 ROWS ONLY;

Combining Vector and Traditional Search

The real power comes from combining vector similarity with traditional filters. A user searching for “lightweight laptop under $1000” can have the “lightweight laptop” part handled by vector search for semantic relevance while “under $1000” is a standard WHERE clause filter. This hybrid approach delivers both relevance and precision.

Use Cases for APEX Applications

Product catalog search that understands natural language queries. Knowledge base and FAQ search that finds relevant articles even when the user’s phrasing does not match the document text. Customer support ticket routing that matches new tickets to similar resolved tickets. Document similarity detection for compliance and deduplication workflows.