Building a Vector Search Pipeline in Oracle Database - Part I

 
One of the most exciting enhancements introduced in Oracle REST Data Services (ORDS) 26.1 is the ability to perform native vector search directly through REST API endpoints. This significantly simplifies the process of exposing semantic search capabilities over HTTP, allowing developers to build AI-powered applications without having to implement custom API layers.
 
In this article, we'll first build the complete vector search pipeline inside Oracle Database. We'll start by loading textual data from Oracle Cloud Object Storage, generate vector embeddings using OCI Generative AI, store those embeddings in a vector-enabled table, and finally perform semantic search using native SQL. In the next article, we'll extend this implementation by exposing the same functionality through the new ORDS REST endpoints introduced in version 26.1.
 
Loading the Sample Dataset
 
For this demonstration, I have a sample text dataset stored in Oracle Cloud Object Storage. Instead of importing the file into the database, Oracle Database allows us to query it directly using the EXTERNAL table feature. This makes it very convenient to work with large datasets stored in Object Storage.
 
rajesh@ADBS26ai> col sentence for a45 trunc
rajesh@ADBS26ai> select *
  2  from external(
  3      ( sentence varchar2(4000))
  4      type oracle_bigdata
  5      access parameters(
  6          com.oracle.bigdata.fileformat = textfile
  7          com.oracle.bigdata.credential.name = OCI$RESOURCE_PRINCIPAL
  8          com.oracle.bigdata.csv.rowformat.fields.terminator='\n'
  9          )
 10  location ('https://objectstorage.us-ashburn-1.oraclecloud.com/n/ax3h6mpnbnhx/b/tinno-etl-data-backup-dev/o/TEST/dataset_200K.txt')
 11  )
 12  where sentence is not null
 13  and rownum <= 10 ;
 
SENTENCE
---------------------------------------------
BOGOTA, Colombia  - A U.S.-made helicopter on
UNIONTOWN, Pa. - A police officer used a Tase
French soccer star Zidane apologized for head
Iraqi Prime Minister Iyad Allawi said on Sund
Relatives of loved ones whose bodies were fou
Hank Kuehne knows he is something of a curios
TOKYO -- The release of Sony's PlayStation 3
The American singer Frankie Laine, who sang t
President Bush will tell the nation's largest
Tapas &#151; the Spanish finger-food that is
 
10 rows selected.
 
Now that we have access to our textual data, the next step is to generate vector embeddings that can later be used for semantic search.
 
Configuring OCI Generative AI Access
 
Oracle Database communicates with OCI Generative AI over HTTPS, so the database user must first be granted permission to connect to the OCI inference endpoint. This is accomplished by configuring the appropriate Network ACL entries.
 
admin@ADBS26ai> BEGIN
  2    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  3      host => 'inference.generativeai.us-chicago-1.oci.oraclecloud.com',
  4      ace  => xs$ace_type(
  5        privilege_list => xs$name_list('resolve'),
  6        principal_name => 'RAJESH',
  7        principal_type => xs_acl.ptype_db
  8      )
  9    );
 10
 11    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
 12      host       => 'inference.generativeai.us-chicago-1.oci.oraclecloud.com',
 13      lower_port => 443,
 14      upper_port => 443,
 15      ace        => xs$ace_type(
 16        privilege_list => xs$name_list('connect'),
 17        principal_name => 'RAJESH',
 18        principal_type => xs_acl.ptype_db
 19      )
 20    );
 21  END;
 22  /
 
PL/SQL procedure successfully completed.
 
Once these privileges are in place, the database is able to establish secure connections to the OCI Generative AI service.
 
The next requirement is to create a credential (very similar to the way how we did in the earlier post) containing the OCI API authentication details. Oracle Database uses this credential whenever it invokes the embedding service.
 
rajesh@ADBS26ai> declare
  2     l_str json_object_t := json_object_t();
  3  begin
  4     l_str.put('user_ocid' , 'ocid1.user.oc1..aaaaaaaaizox65xk2rml7r7sui7cm45sbl63l7mcp3n47pajoio6oxdq2kcq' );
  5       l_str.put('tenancy_ocid' , 'ocid1.tenancy.oc1..aaaaaaaajsbvps46z5s52liti5uk3keanjc7drotset4gnoepohyfg7opnjq');
  6       l_str.put('compartment_ocid' , 'ocid1.compartment.oc1..aaaaaaaaazb4nuukyvbwtiur63tkoncdjrzvnsqlefvwasntf2ubbxfa2wqq');
  7       l_str.put('private_key' , 'MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQC0py5Ql/gRG315
  8  yqFJj/SEuhhi+L5s+tiOCWezfRG/l2kmcHcIVaHMzRA6OwdClkFNiLEYQYQjdVHf
  9  T+CKgGlKXIP/9zTqEA1deNySm1g+JstbG7u9wWBjvD2I9wKGXMaBP71gC4blqCir
       .......
       .......
       .......
 30  4PGw04XANOJgavvoL+5y+Q0vj0S8ujpku2lNcGcqacr8b38r8KkTy1SZR/mpoYS2
 31  kersujzZKtmhnxHlqH4RxvWvYXLY6UYKS9wcLVOIuymQ0UCEsfvPKzA2YuubCFSH
 32  m3nohNxcmelNF4UwxIhK1ilI');
 33      l_str.put('fingerprint' , '92:a7:ef:ba:50:d4:a1:7a:f1:e1:30:fc:f0:8e:b8:73' );
 34     dbms_vector.create_credential( credential_name => 'OCI_GENAI_CRED', params  => json(l_str.to_String) );
 35  end;
 36  /
 
PL/SQL procedure successfully completed.
 
Configuring the Embedding Model
 
With authentication configured, we can now define the embedding provider and specify the model that will generate our vector embeddings. In this example, we're using the Cohere English Embed v3 model hosted on OCI Generative AI.
 
rajesh@ADBS26ai> var params clob;
rajesh@ADBS26ai> begin
  2   :params := '
  3  {
  4    "provider": "ocigenai",
  5    "credential_name": "OCI_GENAI_CRED",
  6    "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
  7    "model": "cohere.embed-english-v3.0",
  8    "batch_size": 10
  9  }';
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
Before processing the dataset, it's always a good idea to verify that the configuration is working correctly by generating an embedding for a simple piece of text
 
rajesh@ADBS26ai> col c1 for a40 trunc
rajesh@ADBS26ai> select dbms_vector.utl_to_embedding('hello', json(:params)) c1 ;
 
C1
----------------------------------------
[-1.44386292E-003,1.67694092E-002,-3.720
 
Receiving a valid vector confirms that the database is successfully communicating with OCI Generative AI and that the credentials have been configured correctly.
 
Creating and Populating the Vector Table
 
Now that embedding generation has been verified, we can create a table that stores both the original text and its corresponding vector representation.
 
rajesh@ADBS26ai> create table if not exists news_data( id number, info varchar2(4000), vec vector );

 
Table NEWS_DATA created.
 
Instead of loading the data first and generating embeddings later, Oracle Database allows us to generate embeddings during the insert operation itself. The following statement reads the data directly from Object Storage while simultaneously invoking the OCI embedding model for every row.
 
rajesh@ADBS26ai> insert into news_data( id,info,vec )
  2  with rws as (
  3  select *
  4  from external(
  5      ( sentence varchar2(4000))
  6      type oracle_bigdata
  7      access parameters(
  8          com.oracle.bigdata.fileformat = textfile
  9          com.oracle.bigdata.credential.name = OCI$RESOURCE_PRINCIPAL
 10          com.oracle.bigdata.csv.rowformat.fields.terminator='\n'
 11          )
 12  location ('https://objectstorage.us-ashburn-1.oraclecloud.com/n/ax3h6mpnbnhx/b/tinno-etl-data-backup-dev/o/TEST/dataset_200K.txt')
 13  ) )
 14  select rownum, sentence
 15     , dbms_vector_chain.utl_to_embedding( rws.sentence , json(:params) )
 16  from rws
 17  where rws.sentence is not null
 18* and rownum <=100;
 
100 rows inserted.
 
For this demonstration, I'm only processing the first 100 rows, but the same approach scales to much larger datasets.
 
Performing Semantic Vector Search
 
With the vectors stored in the database, semantic search becomes remarkably straightforward. Unlike traditional keyword searches, vector search compares the semantic meaning of the query vector against the stored embeddings, making it possible to retrieve relevant results even when the exact words don't appear in the data.
 
Let's use the phrase "little red corvette" as our search query.
 
rajesh@ADBS26ai> variable query_vec varchar2(80)
rajesh@ADBS26ai> exec :query_vec := 'little red corvette';
 
PL/SQL procedure successfully completed.
 
rajesh@ADBS26ai> select id,info
  2  from news_data
  3  order by vector_distance( vec, dbms_vector_chain.utl_to_embedding( :query_vec , json(:params) ) )
  4* fetch first 5 rows only;
 
   ID INFO
_____ ___________________________________________________________________________________________________________
   39 The Toyota Camry, the nation's most popular car has now been rated as its best new model.
   45 The Carolina Panthers entered the season thrilled about their depth at running back.
   50 The US and Venezuela say they have made a positive start to improving relations, after talks in Caracas.
   65 Nella notte 73 interventi dei vigili nel Napoletano
   89 Mibtel +0,75%, SP/Mib +0,76%, All Stars +0,65%
 
 
The query returns the five closest matches based on semantic similarity.
 
Although the results don't contain the exact search phrase, they demonstrate how vector search identifies content based on meaning rather than simple lexical matching. This is the foundation of semantic search and one of the reasons vector databases have become so important in AI-powered applications.
 
At this point, we've successfully built an end-to-end semantic search pipeline inside Oracle Database. We loaded textual data directly from Oracle Cloud Object Storage, generated vector embeddings using OCI Generative AI, stored those embeddings alongside the source text, and queried them using Oracle Database's native vector search capabilities.
 
In the next article, we'll build on this foundation and explore the new capability introduced in ORDS 26.1, where the same vector search functionality can be exposed directly through REST endpoints, enabling AI-powered semantic search over standard HTTP APIs with minimal additional development.