Category: Database

Introduction

In modern AI workflows, one common requirement is: given some piece of content (a document, image caption, query text), find “similar” items in your data store — not by exact keyword match, but by meaning. This is where vector embeddings + vector search come in. In this post we build a real API that:

• Takes input text,
• Generates an embedding,
• Stores embeddings in Oracle’s vector-enabled database,
• Builds a vector index,
• Exposes an API endpoint that returns the top K similar items.

2. Setup & Embedding Generation

2.1 Provisioning

Ensure you have an Oracle Database that supports:

• VECTOR data type. Oracle+2Medium+2
• Vector index capabilities. Medium+1

2.2 Embedding generation code (Python)

from sentence_transformers import SentenceTransformer
import oracledb

# Load embedding model
model = SentenceTransformer('all-MiniLM-L12-v2')

# Sample dataset
docs = [
    {"id":1, "title":"Cloud cost management", "category":"Finance", "text":"How to optimize cloud costs …"},
    {"id":2, "title":"Vendor contract termination", "category":"Legal", "text":"Steps and risks around vendor termination …"},
    # more documents...
]

# Connect to Oracle
conn = oracledb.connect(user="vec_user", password="pwd", dsn="your_dsn")
cursor = conn.cursor()

# Create table
cursor.execute("""
  CREATE TABLE doc_store (
    doc_id     NUMBER PRIMARY KEY,
    title      VARCHAR2(500),
    category   VARCHAR2(100),
    doc_text   CLOB,
    embed_vec  VECTOR
  )
""")
conn.commit()

# Insert embeddings
for d in docs:
    vec = model.encode(d["text"]).tolist()
    cursor.execute("""
      INSERT INTO doc_store(doc_id, title, category, doc_text, embed_vec)
      VALUES(:1, :2, :3, :4, :5)
    """, (d["id"], d["title"], d["category"], d["text"], vec))
conn.commit()

At this point you have your texts stored with their embedding vectors.

3. Vector Indexing & Querying

3.1 Create index

CREATE INDEX idx_doc_embed 
  ON doc_store(embed_vec)
  INDEXTYPE IS vector_ann 
  PARAMETERS('distance_metric=cosine, dimension=384');

(Modify dimension per your embedding size.)

3.2 API Query: embedding + vector similarity

from flask import Flask, request, jsonify
import oracledb

app = Flask(__name__)
model = SentenceTransformer('all-MiniLM-L12-v2')
conn = oracledb.connect(user="vec_user", password="pwd", dsn="your_dsn")
cursor = conn.cursor()

@app.route('/similar', methods=['POST'])
def similar():
    query = request.json["text"]
    q_vec = model.encode([query]).tolist()[0]
    cursor.execute("""
      SELECT doc_id, title, category, vector_distance(embed_vec, :qv) AS dist
      FROM doc_store
      ORDER BY vector_distance(embed_vec, :qv)
      FETCH FIRST 5 ROWS ONLY
    """, {"qv": q_vec})
    results = [{"doc_id": r[0], "title": r[1], "category": r[2], "distance": r[3]} for r in cursor]
    return jsonify(results)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8080)

When you call this API with input text, it returns the top 5 similar documents by semantic meaning.

3.3 Hybrid filtering example

Suppose you want only results in category = “Legal”. Modify the SQL:

SELECT doc_id, title, vector_distance(embed_vec, :qv) AS dist
FROM doc_store
WHERE category = 'Legal'
ORDER BY vector_distance(embed_vec, :qv)
FETCH FIRST 5 ROWS ONLY;

This combines business metadata and semantic similarity.

Conclusion

This tutorial walked you through building a vector-based similarity API: embedding generation, vector storage, indexing, query API, hybrid filtering and production readiness. While the example uses text and embeddings, the same pattern works for images, audio, logs — any data converted into vectors. For your next step, you might add: embedding refresh jobs, user feedback logging, multi-modal embeddings (text+image), or integrate into a larger Microservices architecture.

Regards

Osama

In the age of generative AI and LLM-driven applications, one of the biggest challenges enterprises face is how to connect their business-critical data (structured and unstructured) to AI models in a performant, scalable and governed way. Enter vector databases and vector search: these allow you to represent unstructured data (documents, images, embeddings) as high-dimensional “vectors”, index them for speedy similarity or semantic search, and combine them with relational business data.

With the Oracle stack — particularly the release of Oracle Database 23 ai / AI Database 26 ai — this capability is built into the database, giving you a unified platform for relational, JSON, spatial, graph and vector data.

In this article you’ll learn:

• What vector databases and vector search are, and why they matter for AI use-cases.
• How Oracle’s AI Vector Search works: data types, indexes, distance functions.
• A step-by-step example: ingest text embeddings into Oracle, query them via SQL using the VECTOR data type, combine with business metadata.
• Architectural and operational considerations: when to use, how to scale, best practices.
• Real-world use cases and governance implications.
  
  
  

Vector Databases & Why They Matter

What is a vector?

A vector is simply a list of numbers that represent features of an object: could be a sentence, document, image or audio snippet. By converting raw content into vectors (embeddings) via a model, you can perform similarity or semantic search in a high-dimensional space. Oracle+1

What is a vector database / vector search?

A vector database supports the storage, indexing and efficient querying of vectors — typically enabling nearest-neighbour or similarity search. According to Oracle:

“A vector database is any database that can natively store and manage vector embeddings and handle the unstructured data they describe.”

Importantly, in Oracle’s case, they’ve integrated vector search into their flagship database platform so you don’t need a separate vector store — you can keep relational data + vector embeddings in one system.

Why does this matter for AI and enterprise apps?

• Search not just by keywords, but by meaning. For example: “find all documents about contracts with high risk” might match content without the word “risk” explicitly.
• Enables Retrieval-Augmented Generation (RAG): your LLM can query your private business data (via vector search) and feed it into the prompt to generate more accurate responses.
• Combines unstructured data (embeddings) with structured business data (metadata, JSON, graph) in one platform — leading to simpler architecture, fewer data silos

How Oracle’s AI Vector Search Works

New data type: VECTOR

With Oracle Database 23 ai / AI Database 26 ai, the VECTOR data type is introduced: you can define table columns as VECTOR, store high-dimensional embeddings, and perform vector-specific operations.

Example:

CREATE TABLE docs (
  doc_id   INT,
  doc_text CLOB,
  doc_vector VECTOR  -- storing embedding
);

Vector Indexes & Distance Metrics

To deliver performant searches, Oracle supports vector indexes and distance functions (cosine, Euclidean, etc.). You can build indexes on the VECTOR column. oracle-base.com+1

SQL Example – similarity query:

SELECT doc_id, doc_text
FROM docs
WHERE vector_distance(doc_vector, :query_vector) < 0.3
ORDER BY vector_distance(doc_vector, :query_vector)
FETCH FIRST 10 ROWS ONLY;

Embedding generation & model support

You have two broad options:

• Generate embeddings externally (for example using an open-source transformer model) and load them into the VECTOR column.
• Use built-in or integrated embedding models (Oracle offers embedding generation or ONNX support) so that vector creation and storage is closer to the database.

Hybrid queries: relational + vector

Because everything is in the same database, you can combine structured filters (e.g., WHERE region = 'EMEA') with vector similarity queries. This enables richer semantics. Example: “Find contract documents similar to this one and related to Europe market” in one query.

Retrieval-Augmented Generation (RAG) support

By using vector search to fetch relevant documents and feeding them into your LLM prompt, you create a pipeline where your AI model is grounded in your private enterprise data. Oracle emphasises this with the AI Vector Search feature.

3. Example Walk-through: Text Embeddings + Similarity Search on OCI

Let’s walk through a practical example of how you might use Oracle AI Vector Search on OCI.

Step 1: Set up the environment

• Provision the Oracle AI Database 26 ai service in your OCI tenancy (or use Exadata/Autonomous with vector support).
• Ensure compatible version (VECTOR data type support requires version 23.7+ or similar). Oracle Documentation
• Create a user/table space for embeddings.

Step 2: Create tables for content and embeddings

CREATE TABLE knowledge_base (
  kb_id       NUMBER GENERATED BY DEFAULT AS IDENTITY,
  title       VARCHAR2(500),
  content     CLOB,
  embed_vector VECTOR
);

Step 3: Generate embeddings and load them

Example with Python using sentence-transformers to generate embeddings, and oracledb python driver to insert:

import oracledb
from sentence_transformers import SentenceTransformer

model = SentenceTransformer('all-MiniLM-L12-v2')
texts = ["Contract for vendor A", "Service Level Agreement for cloud services", ...]
embeds = model.encode(texts).tolist()

conn = oracledb.connect(user="vector_usr", password="pwd", dsn="your_dsn")
cursor = conn.cursor()

for text, embed in zip(texts, embeds):
    cursor.execute("""
        INSERT INTO knowledge_base(title, content, embed_vector)
        VALUES(:1, :2, :3)
    """, (text, text, embed))
conn.commit()

Step 4: Build a vector index (optional but recommended)

CREATE INDEX idx_kb_embed ON knowledge_base(embed_vector)
INDEXTYPE IS vector_ann INDEX_PARAMETERS('distance_metric=cosine, dimension=384');

Step 5: Run a similarity search query

Suppose you want documents similar to a query “cloud SLA compliance vendor”:

query_text = "cloud SLA compliance vendor"
query_embed = model.encode([query_text]).tolist()[0]

cursor.execute("""
  SELECT kb_id, title, vector_distance(embed_vector, :qb) AS dist
  FROM knowledge_base
  ORDER BY vector_distance(embed_vector, :qb)
  FETCH FIRST 5 ROWS ONLY
""", {"qb": query_embed})
for row in cursor:
    print(row)

Step 6: Combine with relational filters

For example: only search documents where region = 'EMEA' and then do vector search on their embeddings.

SELECT kb_id, title
FROM knowledge_base
WHERE region = 'EMEA'
ORDER BY vector_distance(embed_vector, :qb)
FETCH FIRST 5 ROWS ONLY;

Step 7: Build RAG pipeline

• Use vector search to fetch top K relevant documents for a given input.
• Pass those documents plus user input to an LLM in your application layer (OCI Functions, Data Science notebook, etc).
• Return generated answer citing which documents were used.
• Store feedback/metrics to refine embeddings over time.

4. Architecture & Operational Considerations

When to use vector databases

Use cases:

• Semantic document search across large unstructured corpora
• Recommendation engines (product similarity, content suggestions)
• Anomaly/outlier detection (embeddings of transactions or sessions)
• RAG workflows, chatbots backed by enterprise data

Architecture variations

• Fully integrated: Use Oracle AI Database / Exadata with vector support. One system for relational + vector.
• Hybrid: Vector store + separate LLM + service layer (if you already have a vector DB elsewhere). But the integrated approach simplifies data movement and governance.
  Oracle emphasises eliminating data silos by embedding vector search within the database.

Performance & scaling

• Choose appropriate vector index type (ANN, HNSW, IVF) according to scale.
• Ensure correct dimension of embeddings (e.g., 384, 768) and index parameters (e.g., nlist,nprobe).
• Use horizontal scalability: Oracle supports sharding, parallel SQL, and Exadata acceleration for vector workloads.
• Keep control of memory and storage: high-dimensional embeddings and large volumes need planning (embedding store size, index maintenance).

Data governance, security & maintainability

• Embeddings often represent sensitive data: apply encryption / access controls as you would relational data.
• Versioning of embeddings: if you regenerate embeddings (new model version), you need to update vectors & indexes.
• Monitoring & freshness: track metrics like query latency, drift in embeddings, relevance degradation.
• Explainability: embeddings are opaque. When building enterprise apps, you may need audit trails showing “why” a result was returned.

Best practices

• Define embedding generation strategy: consistent model, dimension size, pipeline for updating.
• Build hybrid search queries to mix semantic + business filters.
• Keep embedding tables small and well-partitioned (e.g., by date or region) if you expect high volumes.
• Automate index rebuilds/maintenance during low traffic periods.
• Cache top results where appropriate if you have frequent similar queries.
• Perform A/B testing: compare semantic search vs keyword search to measure lift.
• Document and govern vector fields: vector type, model version, embedding timestamp.

5. Use-Cases and Business Value

Use-case: Contract Search & Compliance

Imagine a legal department with thousands of contracts. Traditional keyword search misses meaning (“vendor terminated for cause”) if wording varies. With vector search you embed all contracts, allow semantic queries (“supplier termination risk Europe”), retrieve relevant ones quickly, and then feed into an LLM to summarise risk across contracts.

Use-case: Product Recommendation & RAG-enabled chatbot

Retailer: store product embeddings + user behaviour embeddings in vector table. When a user asks “What new hiking boots would you recommend given my past purchases?”, the system vector-searches similar items + user profile, then uses RAG+LLM to explain recommendations (“Based on your past purchase of Trailblazer 200 and preference for Gore-Tex, here are these three options…”).

Business value

• Faster time-to-insight from unstructured data.
• More relevant search & recommendations → higher engagement or productivity.
• Better AI confidence: feeding enterprise data through vector search into LLM reduces hallucinations by anchoring responses.
• Unified cost & architecture: no separate vector store means less operational overhead and fewer data-movement risks.

who didn’t face an issue with database or query and wants to know more about it ? what is going on behind that query or application ?

Oracle provides different method to do that, one of them is to enable and generate trace called 10046, the idea from using this kind of trace is that we can track the execution plan for the session and provide more information such as bin variable, more information about wait time parse and a lot of other information related to performance issues.

to generate the trace you have to follow these steps , you can use “SYS” user or any other user depends on the session, be notice that you should turn off the trace to complete gathering information, same as the below

spool check.out 
set timing on 
alter session set tracefile_identifier='NAME_OF_TRACE'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10046 trace name context forever, level 12'; 
######
Run the query or the code here
#####
select 'close the cursor' from dual; 
alter session set events '10046 trace name context off'; 
spool off 
exit; 


Important hint :-

• exit is very important to complete and close the trace.
• you can change the name of the trace depends on what you want
• Close the trace after you finished to complete gathering information.
• We select from dual to ensure ensure the previous cursor is closed.

For Trace 10053 which is also provide information but it’s can be generated only for Hard parse SQL, which mean you should add Oracle Hint to the query to ensure the hard parse will be working.

spool check.out 
set timing on 
alter session set tracefile_identifier='NAME_OF_THE_TRACE'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10053 trace name context forever'; 

run the problematic statement 

select 'close the cursor' from dual; 
alter session set events '10053 trace name context off'; 
spool off 
exit; 

Important hint :-

• exit is very important to complete and close the trace.
• you can change the name of the trace depends on what you want
• Close the trace after you finished to complete gathering information.
• We select from dual to ensure ensure the previous cursor is closed.

Now you can use the tkprof to make the trace more readable, tkprof located under $ORACLE_HOME/bin, Run the following command after generate the above trace

tkprof <trace-file> <output file> <Username/password>

cheers

Thank you

The following error appeared when you are trying to conenct to database using toad applicaion: –

Make sure of the following :-

• Database is up and running
• Listener is up and database is registered using.

lsnrctl status

if the above steps is done and still facing the same issue then do the following :-

• Right Click on my computer and choose properties.
• Advance system settings.
• Environment Variable.

check the below entry is exsits, if not add it by press on new and follow the same steps by adding TNS_ADMIN and the location of the tnsnames.ora, sqlnet.ora into the 2nd box like the picture below.

Thanks

Osama