Semantic Search in Oracle AI Database - Part I

 

Traditional text search works well when the search term exists exactly in the source data. For example, if a user searches for THEFT, a LIKE '%THEFT%' query can return records containing that word.

 

However, real-world data is rarely that consistent. The same concept can be represented using different words. A crime related to larceny might be described as retail theft, property crime, embezzlement, or attempt theft. A normal keyword search does not understand those relationships.

 

Oracle Database vector search solves this problem by converting text into embeddings. An embedding is a numeric representation of the meaning of text. Similar meanings produce vectors that are close to each other, even if they do not use the same words.

 

In this example, I load a large crime-case dataset from OCI Object Storage, generate vector embeddings for every case description, and use semantic search to find records related to the word LARCENY.

 

The source file is a CSV file stored in OCI Object Storage. Instead of downloading the file locally and loading it manually, Autonomous Database can read the file directly from Object Storage using the ORACLE_BIGDATA external-table driver.

 

The following statement creates a relational table named CASE_DETAILS from the CSV file.

 

demo@ADB26AI> create table case_details

  2  as

  3  select *

  4  from external(

  5      ( case_id number

  6      , cast_number varchar2(30)

  7      , case_date date

  8      , case_block varchar2(80)

  9      , case_primary_type varchar2(80)

 10      , case_description varchar2(2000)

 11      , case_loc_description varchar2(100)

 12      , case_domestic varchar2(20)

 13      , case_beat varchar2(60)

 14     )

 15      type oracle_bigdata

 16      access parameters(

 17          com.oracle.bigdata.fileformat = csv

 18          com.oracle.bigdata.credential.name = OCI$RESOURCE_PRINCIPAL

 19          com.oracle.bigdata.csv.skip.header = 1

 20          com.oracle.bigdata.trimspaces = ldrtrim

 21          com.oracle.bigdata.removequotes = true

 22          com.oracle.bigdata.ignoreblanklines = true

 23          com.oracle.bigdata.dateformat = 'mm/dd/yyyy hh12:mi:ss am'

 24             com.oracle.bigdata.conversionerrors = reject_record

 25          )

 26  location ('https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DEMO07/Sample_data_for_Vector_Search.csv')

 27  );

 

Table created.

 

The external-table definition includes the structure of the CSV file: case identifier, date, category, description, location, and related attributes.

 

A few access parameters are important here:

• fileformat = csv tells the database that the source is a CSV file.
• skip.header = 1 skips the first row containing column names.
• trimspaces and removequotes clean common CSV formatting issues.
• dateformat tells Oracle how to convert the source date value.
• conversionerrors = reject_record prevents one malformed row from stopping the entire load.
• OCI$RESOURCE_PRINCIPAL uses the database resource principal to access Object Storage, avoiding hard-coded Object Storage credentials in the SQL script. 

After the table is created, verify that the data was loaded successfully.

 

demo@ADB26AI> select count(*) from case_details;

 

            COUNT(*)

--------------------

           1,427,029

 

The CASE_DESCRIPTION column contains short textual descriptions of the crime. To perform semantic search, each description needs to be converted into a vector.

 

Next, create a new table that contains all source data along with an embedding column.

 

demo@ADB26AI> create table vector_search_demo

  2  as

  3  select a.*,

  4     vector_embedding(MY_DEMO_MODEL using case_description as data ) case_embeddings

  5* from case_details a;

 

Table VECTOR_SEARCH_DEMO created.

 

The VECTOR_EMBEDDING function sends the value of CASE_DESCRIPTION to MY_DEMO_MODEL and returns a vector representation of that text.

 

For example, descriptions such as:

RETAIL THEFT

ATTEMPT THEFT

EMBEZZLEMENT

OTHER CRIME INVOLVING PROPERTY

 

will be stored as vectors. The vectors are not manually interpreted; they are used by the database to calculate semantic similarity.

 

The resulting table contains:

The original case information

The text in CASE_DESCRIPTION

The generated vector in CASE_EMBEDDINGS

 

For a dataset containing more than one million rows, embedding generation is a substantial operation. In a production system, this would normally be managed through a batch pipeline or incremental process that only generates embeddings for new or changed records.

 

Now search for the word LARCENY using a normal SQL predicate.

 

demo@ADB26AI> select * from vector_search_demo where upper(case_description) like '%LARCENY%';

 

no rows selected

 

There are no records that contain the literal word LARCENY. This does not mean the dataset contains no larceny-related records. It only means the source system classified or described those records differently. This is a common limitation of keyword search. The query only knows about matching characters, not matching meaning.

 

To perform semantic search, Oracle first generates an embedding for the search term LARCENY.

It then compares that query embedding to every stored value in CASE_EMBEDDINGS.

 

demo@ADB26AI> select case_id,case_description,vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using 'LARCENY' as data ) , cosine ) vd

  2  from vector_search_demo

  3  order by vd

  4* fetch exact first 10 rows only ;

 

The VECTOR_DISTANCE function calculates the distance between two vectors:

1. The stored embedding for a case description.
2. The embedding generated for the search term LARCENY.

 

This example uses cosine distance. A lower distance means the records are semantically closer to the search phrase.

 

The top results are:

 

 

    CASE_ID CASE_DESCRIPTION                  VD

___________ _________________________________ ______________________

   12707163 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   13069849 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   13111350 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   12706126 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   12699911 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   12702212 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   12702566 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   12705023 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   12705511 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

   12706198 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975

 

10 rows selected.

 

Although none of these descriptions uses the word LARCENY, the embedding model identifies other crime involving property as closely related to the meaning of larceny.

 

The previous query returns individual case records. To understand the different types of results, retrieve distinct combinations of CASE_PRIMARY_TYPE and CASE_DESCRIPTION.

 

 

demo@ADB26AI> select case_primary_type , case_description

  2  from (

  3  select case_primary_type

  4     , case_description

  5     , vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using 'LARCENY' as data ) , cosine ) vd

  6  from vector_search_demo

  7  order by vd

  8  fetch exact first 50000 rows only

  9     )

 10  group by case_primary_type , case_description ,vd

 11  order by vd

 12* fetch first 15 rows only;

 

CASE_PRIMARY_TYPE         CASE_DESCRIPTION

_________________________ _________________________________

OTHER OFFENSE             OTHER CRIME INVOLVING PROPERTY

OTHER OFFENSE             OTHER CRIME AGAINST PERSON

THEFT                     ATTEMPT THEFT

INTIMIDATION              EXTORTION

PUBLIC PEACE VIOLATION    RECKLESS CONDUCT

DECEPTIVE PRACTICE        EMBEZZLEMENT

CRIMINAL DAMAGE           CRIMINAL DEFACEMENT

SEX OFFENSE               CRIMINAL SEXUAL ABUSE

PROSTITUTION              OTHER PROSTITUTION OFFENSE

DECEPTIVE PRACTICE        FORGERY

HOMICIDE                  RECKLESS HOMICIDE

OTHER OFFENSE             COMPOUNDING A CRIME

SEX OFFENSE               ATTEMPT CRIMINAL SEXUAL ABUSE

THEFT                     RETAIL THEFT

 

14 rows selected.

 

The important results are:

 

ATTEMPT THEFT

RETAIL THEFT

EMBEZZLEMENT

OTHER CRIME INVOLVING PROPERTY

 

These descriptions are not exact matches for LARCENY, but they are conceptually related. This is the key benefit of semantic search.

 

A normal keyword search answers the question:

Which records contain the word LARCENY?

The result in this dataset is:

 

demo@ADB26AI> select * from vector_search_demo where upper(case_description) like '%LARCENY%';

 

no rows selected

 

A vector search answers a different question:

Which records are most similar in meaning to LARCENY?

That search returns records related to theft, property crimes, fraud, and similar offense descriptions.

 

This is useful when:

• The source data has inconsistent terminology.
• Different users describe the same concept differently.
• The user does not know the exact wording used in the database.
• The data includes business descriptions, support tickets, legal cases, product documentation, or customer comments.
• A search application needs to understand intent rather than only keywords. 

This example creates embeddings using only CASE_DESCRIPTION. That is sufficient for a basic demonstration, but a production implementation may generate embeddings from several business fields together.

 

For example:

case_primary_type || ' - ' || case_description

 

This can give the model more context by including both the broad crime category and the specific description.

 

The search can also be combined with traditional SQL filters. For example, a user could perform semantic search for LARCENY while limiting results to a date range, neighborhood, location type, or specific crime category.

 

demo@ADB26AI> select case_id,

  2         case_date,

  3         case_primary_type,

  4         case_description,

  5         vector_distance(

  6           case_embeddings,

  7           vector_embedding(

  8             MY_DEMO_MODEL using 'LARCENY' as data

  9           ),

 10           cosine

 11         ) as distance

 12  from vector_search_demo

 13  where case_date >= date '2024-01-01'

 14    and case_primary_type = 'THEFT'

 15  order by distance

 16* fetch first 5 rows only;

 

    CASE_ID CASE_DATE      CASE_PRIMARY_TYPE    CASE_DESCRIPTION    DISTANCE

___________ ______________ ____________________ ___________________ ______________________

   14090434 22-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593

   14083242 14-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593

   14086550 17-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593

   14086324 17-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593

   14088859 20-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593

 

demo@ADB26AI>

 

This hybrid approach combines the strength of relational filtering with semantic ranking.

 

Oracle AI Database vector search makes it possible to search based on meaning instead of exact words.

 

In this example:

• A CSV file was loaded from OCI Object Storage.
• More than 1.4 million case records were stored in Autonomous Database.
• Embeddings were generated for every case description.
• A keyword search for LARCENY returned no rows.
• A vector search found semantically related records such as attempt theft, retail theft, embezzlement, and other crime involving property.

 

This is a useful pattern for any application where users search with natural language but the underlying data uses different terminology.

 

Building a Vector Search Pipeline in Oracle Database - Part III

In Part I, we built a semantic-search pipeline inside Oracle Database: loaded text from Object Storage, generated embeddings with OCI Generative AI, stored those vectors in the NEWS_DATA table, and queried them with native vector search. 

In Part II, we exposed that search through a custom ORDS module. The application sent a text phrase, and the handler generated the embedding and returned the nearest matches. 

ORDS 26.1 introduces another option: native vector search for AutoREST-enabled tables and views that contain VECTOR columns. Once the table is REST-enabled, ORDS automatically exposes a POST vectorSearch endpoint. There is no need to define an ORDS module, template, or custom SQL handler. 

REST-enabling the vector table 

The NEWS_DATA table created in Part I contains the source text in INFO and the embedding in VEC. Enable AutoREST for the table as follows:
 
rajesh@ADBS26ai> declare
  2    pragma autonomous_transaction;
  3  begin
  4      ords.enable_object(p_enabled => TRUE,
  5                         p_schema => 'RAJESH',
  6                         p_object => 'NEWS_DATA',
  7                         p_object_type => 'TABLE',
  8                         p_object_alias => 'news_data',
  9                         p_auto_rest_auth => FALSE);
 10      commit;
 11  end;
 12  /
 
PL/SQL procedure successfully completed.
 
The object alias becomes part of the REST path. With the settings above, ORDS creates the native vector-search endpoint at:
 
POST https://<your-ords-host>/ords/rajesh/news_data/vectorSearch
 
For this demonstration, authentication is disabled. In a production environment, protect the endpoint with the authentication and authorization controls appropriate for your ORDS deployment.
 
Exploring the generated OpenAPI definition
 
ORDS also generates an OpenAPI document for the AutoREST-enabled object. You can retrieve it using:
 
GET https://<your-ords-host>/ords/rajesh/open-api-catalog/news_data/
 

 
Import the returned OpenAPI JSON into Swagger Editor to explore the available operations.
 

 
In addition to the normal AutoREST operations, the definition includes:
 
POST /vectorSearch
 
The generated API documentation describes the request body fields, including:

• vector — the query embedding
• columns — the columns to return
• distanceMetric — the similarity metric
• limit — the maximum number of matches
• includeVectors — whether the response should include stored vectors

 
Generating the query vector
 
The native endpoint expects a vector rather than plain text. For this example, use the same embedding configuration from Part I to generate a vector for the phrase little red corvette.
One way to get the query vector as input for the above endpoint is the call the
 

 
The query returns the embedding as an array of numeric values. Copy that array into the request body for the vectorSearch call.
 

 
For production applications, generate query embeddings in a trusted service layer or approved inference flow. Do not expose OCI credentials to client-side applications.
 
Calling the native vector-search endpoint
 
Send the generated vector to the AutoREST endpoint in a JSON payload:
 
{
  "columns": ["ID", "INFO"],
  "distanceMetric": "EUCLIDEAN",
  "includeVectors": false,
  "limit": 5,
  "vector": [
    -0.026794434,
    -0.00776764,
    -0.0042381287
  ]
}
 
The vector array above is abbreviated for readability. In the actual request, provide the complete embedding returned by DBMS_VECTOR.UTL_TO_EMBEDDING.
 
POST https://<your-ords-host>/ords/rajesh/news_data/vectorSearch
Content-Type: application/json
 
ORDS returns the nearest records from NEWS_DATA, together with the vector-search distance:
 
{
  "items": [
    {
      "id": 39,
      "info": "The Toyota Camry, the nation's most popular car has now been rated as its best new model.",
      "vectorsearchdistance": 0.6485679418223137
    },
    {
      "id": 45,
      "info": "The Carolina Panthers entered the season thrilled about their depth at running back.",
      "vectorsearchdistance": 0.6679124700084889
    }
  ]
}
 
The results are consistent with the semantic search performed in Part I. The returned text does not need to contain the exact phrase little red corvette; the search ranks records by similarity between the query vector and the vectors stored in the VEC column.
 
Why use the native endpoint?
 
The native vectorSearch endpoint is useful when the table itself is the API surface.

•  No custom ORDS module or SQL handler is required.
  
• The OpenAPI definition is generated automatically.
• Clients can choose returned columns, result limits, and distance metrics through the request body.
• The response includes a distance value for each returned record.
• The same AutoREST object can support ordinary REST operations as well as vector search.

 
Part II remains useful when the API should accept a natural-language phrase directly, apply custom logic, or expose a carefully tailored response. The native endpoint is a simpler alternative when an application can provide the query vector and you want ORDS to expose vector search with minimal configuration.
 
With this final step, the semantic-search pipeline built in Part I is available through both custom ORDS handlers and the new native AutoREST vector-search endpoint in ORDS 26.1.
 

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.