How to Use the VECTOR Data Type in Oracle AI Database 26ai

Every AI search feature in Oracle stores its embeddings in a column of the VECTOR data type. Choosing the right declaration decides how much space the vectors take, which mistakes the database catches for you, and which vectors can be compared.

This guide covers the VECTOR type in Oracle AI Database 26ai: declaring columns, the four number formats and what they cost, dense and sparse vectors, converting vectors to and from text, describing them, what a vector cannot do, and vectors in PL/SQL.

Code for This Guide

The examples are in the examples/ch04 folder of the Oracle AI code repository on GitHub, each with its output. They run in the sample schema ATLAS, installed by the setup/atlas scripts, because the first table refers to its KB_ARTICLES table.

These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.

To keep the numbers readable, the examples use hand-made vectors of three dimensions instead of real embeddings. Each help desk article gets three scores between 0 and 1: how much it is about account problems, billing, and technical problems. Everything shown applies unchanged to embeddings of 384 or 3,072 dimensions.

Declare a VECTOR Column

A VECTOR declaration says how many numbers each vector has (its dimensions), what kind of number each one is (its format), and optionally whether it is stored dense or sparse.

Syntax:

vector [( { number_of_dimensions | * }
         [, { float32 | float64 | int8 | binary | * }
         [, { dense | sparse } ] ] )]

The example table stores a topic vector for six articles. A vector is written as text in square brackets, and the database converts it on insert.

Example:

-- each knowledge base article scored from 0 to 1 on three topics:
-- [account, billing, technical]
create table article_topics (
  article_id  varchar2(10) constraint article_topics_pk primary key
              constraint article_topics_fk references kb_articles,
  topics      vector(3, float32) not null
);

insert into article_topics values ('KB-101', '[0.90, 0.05, 0.30]');  -- sign-in problems
insert into article_topics values ('KB-105', '[0.85, 0.05, 0.40]');  -- suspicious sign-in
insert into article_topics values ('KB-201', '[0.10, 0.95, 0.10]');  -- duplicate charges
insert into article_topics values ('KB-203', '[0.05, 0.90, 0.20]');  -- taxes and VAT
insert into article_topics values ('KB-301', '[0.10, 0.05, 0.95]');  -- pipeline page bug
insert into article_topics values ('KB-401', '[0.15, 0.00, 0.90]');  -- mobile app crash
commit;

select article_id, topics
from   article_topics
order  by article_id;

Output:

Table ARTICLE_TOPICS created.

1 row inserted.

1 row inserted.

1 row inserted.

1 row inserted.

1 row inserted.

1 row inserted.

Commit complete.

ARTICLE_ID    TOPICS
_____________ ____________________________________________________
KB-101        [8.99999976E-001,5.00000007E-002,3.00000012E-001]
KB-105        [8.50000024E-001,5.00000007E-002,4.00000006E-001]
KB-201        [1.00000001E-001,9.49999988E-001,1.00000001E-001]
KB-203        [5.00000007E-002,8.99999976E-001,2.00000003E-001]
KB-301        [1.00000001E-001,5.00000007E-002,9.49999988E-001]
KB-401        [1.50000006E-001,0,8.99999976E-001]

6 rows selected.

The scores went in as 0.90, 0.05, and 0.30 and come back as 8.99999976E-001 and so on. SQLcl prints vector numbers in scientific notation, and a FLOAT32 number keeps about seven significant digits, so 0.9 is stored as the nearest 32-bit value. That rounding is far smaller than any difference that matters between embeddings.

A Fixed Size Catches Mixed Models

Embeddings from one model always have the same number of dimensions, so a fixed-size column catches the most common mistake early: mixing vectors from two models.

Example:

insert into article_topics values ('KB-102', '[0.80, 0.10]');

Output:

Error starting at line : 1
In command -
insert into article_topics values ('KB-102', '[0.80, 0.10]')
Error at Command Line : 1 Column : 46
Error report -
SQL Error: ORA-51803: Vector dimension count must match the dimension count specified in
the column definition (expected 3 dimensions, specified 2 dimensions).

Flexible Columns

VECTOR(*, *) accepts any size and format. It suits a table that keeps the vectors of several models side by side while you compare them, at the price of the size check above.

Example:

create table embeddings_any (
  id     number,
  model  varchar2(40),
  v      vector(*, *)          -- any number of dimensions, any format
);

insert into embeddings_any values (1, 'topic scores',  vector('[0.9, 0.05, 0.3]'));
insert into embeddings_any values (2, 'small model',   vector('[1, 2, 3, 4, 5]', 5, int8));
insert into embeddings_any values (3, 'another model', vector('[0.5, 0.25]', 2, float64));

select id, model, vector_dimension_count(v) as dims, vector_dimension_format(v) as format
from   embeddings_any;

Output:

Table EMBEDDINGS_ANY created.

1 row inserted.

1 row inserted.

1 row inserted.

   ID MODEL               DIMS FORMAT
_____ ________________ _______ __________
    1 topic scores           3 FLOAT32
    2 small model            5 INT8
    3 another model          2 FLOAT64

Vectors of different sizes in such a column cannot be compared with each other. In production tables, declare the exact size of your model.

Choose a Number Format

FLOAT32, FLOAT64, and INT8

FLOAT64 keeps about sixteen significant digits, FLOAT32 about seven, and INT8 rounds each value to a whole number.

Example:

select vector('[0.123456789]', 1, float64) as float64_value,
       vector('[0.123456789]', 1, float32) as float32_value,
       vector('[1.6, -2.4, 100]', 3, int8) as int8_value
from   dual;

Output:

FLOAT64_VALUE                FLOAT32_VALUE        INT8_VALUE
____________________________ ____________________ _____________
[1.2345678900000001E-001]    [1.23456791E-001]    [2,-2,100]

An INT8 value must lie between -128 and 127.

Example:

select vector('[1.6, -2.4, 300]', 3, int8) from dual;

Output:

Error starting at line : 1
In command -
select vector('[1.6, -2.4, 300]', 3, int8) from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-51806: Vector dimension value at position 2 is outside the range allowed by
the dimension format FLEX.

The position in ORA-51806 counts from 0, so position 2 is the third number. In practice the error usually means a FLOAT32 embedding was passed where an INT8 one was expected.

BINARY

A BINARY vector stores one bit per dimension, eight in each byte, and you write it as byte values: 170 is the bits 10101010 and 15 is 00001111. The number of dimensions must be a multiple of 8.

Example:

-- 170 = 10101010 and 15 = 00001111: two bytes hold 16 binary dimensions
select to_vector('[170, 15]', 16, binary)                         as bits,
       vector_dimension_count(to_vector('[170, 15]', 16, binary)) as dims
from   dual;

select to_vector('[170, 15]', 12, binary) from dual;

Output:

BITS           DIMS
___________ _______
[170,15]         16

Error starting at line : 6
In command -
select to_vector('[170, 15]', 12, binary) from dual
Error at Command Line : 6 Column : 35
Error report -
SQL Error: ORA-51813: Vector of BINARY format should have a dimension count that is a
multiple of 8.

Binary vectors are compared with the HAMMING or JACCARD metrics. Few embedding models produce them; they matter for collections of hundreds of millions of vectors.

What the Format Costs in Storage

This example fills three tables with 2,000 random vectors of 384 dimensions each, one per format, and compares their size.

Example:

create table size_f32    (id number, v vector(384, float32));
create table size_int8   (id number, v vector(384, int8));
create table size_binary (id number, v vector(384, binary));

-- 2,000 random vectors of 384 dimensions in each table
insert into size_f32
select level, (select to_vector('[' || listagg(round(dbms_random.value(-1, 1), 4), ',')
                                       within group (order by null) || ']')
               from dual connect by level <= 384)
from   dual connect by level <= 2000;

insert into size_int8
select level, (select to_vector('[' || listagg(round(dbms_random.value(-127, 127)), ',')
                                       within group (order by null) || ']', 384, int8)
               from dual connect by level <= 384)
from   dual connect by level <= 2000;

insert into size_binary          -- 384 bits = 48 bytes per vector
select level, (select to_vector('[' || listagg(trunc(dbms_random.value(0, 256)), ',')
                                       within group (order by null) || ']', 384, binary)
               from dual connect by level <= 48)
from   dual connect by level <= 2000;
commit;

select segment_name as table_name, round(sum(bytes) / 1024 / 1024, 2) as mb
from   user_segments
where  segment_name in ('SIZE_F32', 'SIZE_INT8', 'SIZE_BINARY')
group  by segment_name
order  by mb desc;

Output:

Table SIZE_F32 created.

Table SIZE_INT8 created.

Table SIZE_BINARY created.

2,000 rows inserted.

2,000 rows inserted.

2,000 rows inserted.

Commit complete.

TABLE_NAME          MB
______________ _______
SIZE_F32             5
SIZE_INT8            2
SIZE_BINARY       0.31

The INT8 table is less than half the size of the FLOAT32 one, and the BINARY table about a sixteenth; the rest is row overhead every table has. At ten million rows, that is roughly 25 GB as FLOAT32, 10 GB as INT8, and under 2 GB as BINARY.

Oracle stores a VECTOR column as a SecureFile LOB, kept inside the row while it fits, up to about 4,000 bytes. That covers 384 FLOAT32 dimensions but not 3,072, so tables of large embeddings have a large LOB segment beside them.

Dense or Sparse Vectors

A dense vector stores every dimension. A sparse vector stores only the nonzero ones, as the total number of dimensions, the positions of the nonzero values (counting from 0), and the values. Keyword-based models such as SPLADE produce vectors of 30,000 or more dimensions that are almost all zero; stored sparse, each takes a few hundred bytes instead of 120 KB.

Syntax:

'[ number_of_dimensions, [ position, ... ], [ value, ... ] ]'

Example:

-- 10 dimensions, values only at positions 2 and 7
select to_vector('[10, [2, 7], [0.5, 0.25]]', 10, float32, sparse) as sparse_vector,
       from_vector(to_vector('[10, [2, 7], [0.5, 0.25]]', 10, float32, sparse)
                   returning varchar2(200) format dense)          as as_dense
from   dual;

select vector_distance(to_vector('[10, [2, 7], [0.5, 0.25]]', 10, float32, sparse),
                       to_vector('[0, 0.5, 0, 0, 0, 0, 0.25, 0, 0, 0]', 10, float32)) as d
from   dual;

Output:

SPARSE_VECTOR                     AS_DENSE
_________________________________ ______________________________________
[10,[2,7],[5.0E-001,2.5E-001]]    [0,0,5.0E-001,0,0,0,0,2.5E-001,0,0]

Error starting at line : 7
In command -
select vector_distance(to_vector('[10, [2, 7], [0.5, 0.25]]', 10, float32, sparse),
                       to_vector('[0, 0.5, 0, 0, 0, 0, 0.25, 0, 0, 0]', 10, float32)) as d
from   dual
Error at Command Line : 7 Column : 8
Error report -
SQL Error: ORA-51834: Distance computation between sparse and dense vector is not
supported.

A sparse vector cannot be compared with a dense one (ORA-51834). Use one storage throughout a column: dense for almost every embedding model, sparse only for models that say they produce sparse vectors.

Convert Vectors to and from Text

TO_VECTOR

TO_VECTOR turns text into a vector, and VECTOR is the same function under the type's name. You can state the dimensions and format of the result, which helps when the text comes from outside, such as a JSON response from an AI provider.

Syntax:

to_vector( text | vector [, { number_of_dimensions | * }
                         [, { float32 | float64 | int8 | binary | * }
                         [, { dense | sparse } ] ] ] )

Example:

select to_vector('[0.9, 0.05, 0.3]') as from_text from dual;

select to_vector(json_serialize(json_array(0.9, 0.05, 0.3))) as from_json_array from dual;

select to_vector(vector('[0.9, -0.4, 0.05]'), 3, int8) as float32_to_int8 from dual;

Output:

FROM_TEXT
____________________________________________________
[8.99999976E-001,5.00000007E-002,3.00000012E-001]

FROM_JSON_ARRAY
____________________________________________________
[8.99999976E-001,5.00000007E-002,3.00000012E-001]

FLOAT32_TO_INT8
__________________
[1,0,0]

Converting [0.9, -0.4, 0.05] to INT8 gives [1, 0, 0], because every value is rounded to a whole number. That is why embeddings are not simply cast to INT8: a model meant for INT8 scales its values to the range -128 to 127 first.

FROM_VECTOR

FROM_VECTOR turns a vector back into text, as VARCHAR2 or CLOB, and VECTOR_SERIALIZE is the same function. For a sparse vector, FORMAT DENSE or FORMAT SPARSE chooses the text form.

Syntax:

from_vector( vector [ returning { varchar2(size) | clob } ]
                    [ format { dense | sparse } ] )
vector_serialize( vector [ returning { varchar2(size) | clob } ]
                         [ format { dense | sparse } ] )

Example:

select from_vector(topics returning varchar2(100)) as as_varchar2
from   article_topics
where  article_id = 'KB-201';

select vector_serialize(topics returning clob) as as_clob
from   article_topics
where  article_id = 'KB-201';

Output:

AS_VARCHAR2
____________________________________________________
[1.00000001E-001,9.49999988E-001,1.00000001E-001]

AS_CLOB
____________________________________________________
[1.00000001E-001,9.49999988E-001,1.00000001E-001]

Return a CLOB for real embeddings: 3,072 numbers written as text take more than 4,000 characters.

Describe a Vector

Example:

select article_id,
       vector_dimension_count(topics)  as dims,
       vector_dimension_format(topics) as format,
       round(vector_norm(topics), 4)   as norm
from   article_topics
order  by article_id;

Output:

ARTICLE_ID       DIMS FORMAT          NORM
_____________ _______ __________ _________
KB-101              3 FLOAT32         0.95
KB-105              3 FLOAT32       0.9407
KB-201              3 FLOAT32       0.9605
KB-203              3 FLOAT32       0.9233
KB-301              3 FLOAT32       0.9566
KB-401              3 FLOAT32       0.9124

6 rows selected.

Many embedding models return vectors of length exactly 1, called normalized vectors, and VECTOR_NORM is a quick way to check.

What a Vector Cannot Do

A vector is not a value that can be equal to, greater than, or less than another. Two embeddings of the same text can differ in the last digit, and "greater" has no meaning for a direction in space. So Oracle refuses comparisons, sorting by the vector, and ordinary indexes on a vector column.

Example:

select count(*) from article_topics where topics = vector('[0.90, 0.05, 0.30]');

select article_id from article_topics order by topics;

create index article_topics_ix on article_topics (topics);

-- the same test, done with a distance
select article_id
from   article_topics
where  vector_distance(topics, vector('[0.90, 0.05, 0.30]'), euclidean) = 0;

Output:

Error starting at line : 1
In command -
select count(*) from article_topics where topics = vector('[0.90, 0.05, 0.30]')
Error at Command Line : 1 Column : 43
Error report -
SQL Error: ORA-22848: cannot use VECTOR type as comparison key

Error starting at line : 3
In command -
select article_id from article_topics order by topics
Error at Command Line : 3 Column : 48
Error report -
SQL Error: ORA-22848: cannot use VECTOR type as comparison key

Error starting at line : 5
In command -
create index article_topics_ix on article_topics (topics)
Error report -
ORA-02327: cannot create index on expression with data type VECTOR

ARTICLE_ID
_____________
KB-101

ORA-22848 also stops GROUP BY, DISTINCT, and UNION on a vector column. To find duplicates, compare distances, as the last query does; to index vectors, use a vector index.

Vectors in PL/SQL

VECTOR is a PL/SQL type too: variables, parameters, and function results can be vectors. A function that returns a vector can be used in SQL wherever a vector is expected.

Example:

create or replace function topic_vector (
  p_account   number,
  p_billing   number,
  p_technical number
) return vector
is
  v vector(3, float32);
begin
  v := to_vector('[' || p_account || ',' || p_billing || ',' || p_technical || ']');
  return v;
end;
/

select article_id,
       round(vector_distance(topics, topic_vector(0.1, 0.1, 0.9)), 4) as distance
from   article_topics
order  by distance
fetch  first 2 rows only;

declare
  v_ticket   vector(3, float32) := topic_vector(0.2, 0.8, 0.1);
  v_distance number;
begin
  select min(vector_distance(topics, v_ticket)) into v_distance from article_topics;
  dbms_output.put_line('Dimensions: ' || vector_dimension_count(v_ticket));
  dbms_output.put_line('Closest article is ' || round(v_distance, 4) || ' away');
end;
/

Output:

Function TOPIC_VECTOR compiled

ARTICLE_ID       DISTANCE
_____________ ___________
KB-301             0.0017
KB-401             0.0075

Dimensions: 3
Closest article is .0098 away

PL/SQL procedure successfully completed.

TOPIC_VECTOR builds the query vector from three scores. In a real application, a function like it calls an embedding model instead, and the queries around it stay the same.

Conclusion

Declare embedding columns with the model's exact size, such as VECTOR(384, FLOAT32), so the database rejects vectors from the wrong model. FLOAT32 is the default; INT8 and BINARY trade precision for a quarter and a thirty-second of the space. Keep a column all dense or all sparse, convert with TO_VECTOR and FROM_VECTOR, and check vectors with VECTOR_DIMENSION_COUNT, VECTOR_DIMENSION_FORMAT, and VECTOR_NORM. Vectors cannot be compared, sorted, or indexed like ordinary values; they are compared by distance, as shown in how to measure vector distance with VECTOR_DISTANCE in Oracle.