Building an Embedding-Driven Similarity API Using a Vector Database on Oracle Database 23 ai

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