Building a Vector Search Pipeline in Oracle Database - Part II

In Part I, we loaded text from Oracle Cloud Object Storage, generated embeddings with OCI Generative AI, stored those vectors alongside their source text, and queried the NEWS_DATA table with Oracle Database native vector search. That proved the semantic-search workflow inside the database. This article takes the next practical step: expose the same search as a REST API that an application can call over HTTP.
 
The implementation uses a custom Oracle REST Data Services (ORDS) module. A caller passes a search phrase in the URL; ORDS binds that value into the SQL statement, the database generates its embedding, and VECTOR_DISTANCE returns the nearest matching rows.
 
The following PL/SQL block enables the schema for ORDS, creates the vector_search module, defines a ccnews/:input_text route, and attaches a GET handler that returns a JSON collection. The handler reuses the NEWS_DATA table and the same OCI Generative AI embedding configuration used in Part I.
 
rajesh@ADBS26ai> BEGIN
  2    ORDS.ENABLE_SCHEMA(
  3        p_enabled             => TRUE,
  4        p_schema              => 'RAJESH',
  5        p_url_mapping_type    => 'BASE_PATH',
  6        p_url_mapping_pattern => 'rajesh',
  7        p_auto_rest_auth      => FALSE);
  8
  9    ORDS.DEFINE_MODULE(
 10        p_module_name    => 'vector_search',
 11        p_base_path      => '/vector_search/',
 12        p_items_per_page =>  25,
 13        p_status         => 'PUBLISHED',
 14        p_comments       => NULL);
 15    ORDS.DEFINE_TEMPLATE(
 16        p_module_name    => 'vector_search',
 17        p_pattern        => 'ccnews/:input_text',
 18        p_priority       => 0,
 19        p_etag_type      => 'HASH',
 20        p_etag_query     => NULL,
 21        p_comments       => NULL);
 22    ORDS.DEFINE_HANDLER(
 23        p_module_name    => 'vector_search',
 24        p_pattern        => 'ccnews/:input_text',
 25        p_method         => 'GET',
 26        p_source_type    => 'json/collection',
 27        p_items_per_page =>  25,
 28        p_mimes_allowed  => '',
 29        p_comments       => NULL,
 30        p_source         =>
 31  'select id,info
 32  from news_data
 33  order by vector_distance( vec, dbms_vector_chain.utl_to_embedding( :input_text , json(''{
 34      "provider": "ocigenai",
 35      "credential_name": "OCI_GENAI_CRED",
 36      "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
 37      "model": "cohere.embed-english-v3.0",
 38      "batch_size": 100
 39     }'') ) )
 40  fetch first 5 rows only'
 41        );
 42
 43
 44    COMMIT;
 45  END;
 46  /
 
PL/SQL procedure successfully completed.
 
 
ORDS binds the path parameter to :input_text; it is not concatenated into the SQL statement. DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING converts that text into a query vector using the configured OCI Generative AI model. VECTOR_DISTANCE compares that query vector with the VEC column for every NEWS_DATA row, then the query returns the five nearest matches.
 
After the module is published, send a URL-encoded search phrase to the route. The example below uses the phrase from Part I, little red corvette.
 
$ curl --location 'https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/vector_search/ccnews/little%20red%20corvette'
{"items":[{"id":39,"info":"The Toyota Camry, the nation's most popular car has now been rated as its best new model."},{"id":45,"info":"The Carolina Panthers entered the season thrilled about their depth at running back."},{"id":50,"info":"The US and Venezuela say they have made a positive start to improving relations, after talks in Caracas."},{"id":65,"info":"Nella notte 73 interventi dei vigili nel Napoletano"},{"id":89,"info":"Mibtel +0,75%, SP/Mib +0,76%, All Stars +0,65%"}],"hasMore":false,"limit":25,"offset":0,"count":5,"links":[{"rel":"self","href":"https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/vector_search/ccnews/little%20red%20corvette"},{"rel":"describedby","href":"https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/metadata-catalog/vector_search/ccnews/item"},{"rel":"first","href":"https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/vector_search/ccnews/little%20red%20corvette"}]}
 

 
 
The response is an ORDS JSON collection. Each item contains the row ID and source text selected by the vector search, while the collection metadata reports the result count and pagination state.
 
ORDS can generate an OpenAPI catalog for the custom module. This gives API consumers a machine-readable description of the route, including the required input_text path parameter and JSON response schema
 
$ curl --location 'https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/open-api-catalog/vector_search/'
{"openapi":"3.0.0","info":{"title":"ORDS generated API for vector_search","version":"1.0.0"},"servers":[{"url":"https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/vector_search"}],"paths":{"/ccnews/{input_text}":{"get":{"description":"Retrieve records from vector_search","responses":{"200":{"description":"The queried record.","content":{"application/json":{"schema":{"type":"object","properties":{"items":{"type":"array","items":{"type":"object","properties":{"id":{"$ref":"#/components/schemas/NUMBER"},"info":{"$ref":"#/components/schemas/VARCHAR2"}}}}}}}}}},"parameters":[{"name":"input_text","in":"path","required":true,"schema":{"type":"string","pattern":"^[^/]+$"},"description":"implicit"}]}}},"components":{"schemas":{"NUMBER":{"type":"number"},"VARCHAR2":{"type":"string"}}}}
 
 
The first screenshot shows the OpenAPI document returned by the catalog endpoint. Notice that it describes the GET /ccnews/{input_text} route and identifies the API server URL.

 
Import that JSON into Swagger Editor to turn the generated contract into an interactive API reference. The overview confirms that the module exposes a single GET endpoint and documents the response types returned by the handler.
 
 

Swagger exposes input_text as a required path parameter. Enter little red corvette, choose Try it out, and Swagger constructs the encoded request URL automatically. This makes it easy to validate the route without manually composing a cURL command.
 

The server response returns five matching NEWS_DATA records. The results do not need to contain the exact phrase; vector search ranks content by semantic similarity, which is the behavior established in Part I.
 
 

 
What this gives an application
 

• A simple HTTP interface: clients send natural-language input instead of database SQL or vector values.
• A reusable semantic-search service: embedding generation and similarity ranking remain inside Oracle Database. 
• A documented contract: the generated OpenAPI definition can be imported into Swagger, API clients, and testing tools. 
• A clear extension point: add authentication, filters, pagination, response fields, or a POST request body as your application requirements grow.

  
The vector pipeline from Part I is now available as a REST service. ORDS receives a search phrase, binds it safely into the handler, Oracle Database generates an embedding through OCI Generative AI, and the database returns the nearest NEWS_DATA records as JSON. From there, the endpoint can be consumed by a web application, assistant, or any HTTP-capable client.