Working with Vectors from SQL, PL/SQL, JDBC, and JSON in Oracle AI Database 26ai

Oracle AI Database 26ai

Working with Vectors from SQL, PL/SQL, JDBC, and JSONOne datatype, four interface surfaces, and the rules that keep your vector workflows coherent

The same VECTOR value works across SQL, PL/SQL, JDBC, and Oracle JSON. The practical challenge is knowing where the type contract stays identical, where representation changes, and where type fidelity gets lost if you are not deliberate about it.

SQL is the canonical layerDDL, distance functions, arithmetic, aggregates, and query shape all start here.

PL/SQL is the in-database glueUse it when vectors participate in stored logic, validation, or orchestration.

JDBC and JSON solve different edgesJDBC handles typed client bindings; JSON handles document interoperability and typed OSON round-trips.

Table of Contents

Starts with the shared type contract, works through SQL, PL/SQL, JDBC, and JSON behavior, then covers interface selection, an end-to-end lab, and a troubleshooting section.

1. Interface Mental ModelThe same vector value viewed through four execution surfaces 2. SQL Surface AreaDDL, literals, search, arithmetic, aggregates, and operational boundaries 3. PL/SQL WorkflowsStored logic, arithmetic rules, and where version caution matters 4. JDBC BindingsTyped binds, fetch behavior, sparse APIs, and compatibility paths 5. JSON and OSONVector scalars versus JSON arrays, and how to round-trip safely 6. Choosing the Right InterfaceDecision matrix, workflow patterns, and division of responsibility 7. Build and Validate a WorkflowA compact lab covering SQL, PL/SQL, JDBC, and JSON together 8. Caveats, Diagnostics, and FAQWhere real projects usually drift off contract9. Knowledge checkFive questions based on the post9. Knowledge checkFive questions based on the post

Section 1

One vector contract, four execution surfaces

1

SQL, PL/SQL, JDBC, and JSON do not each define their own vector semantics. The contract is the vector itself: dimension count, element format, dense or sparse storage. Each interface is just a different way to work with the same underlying value.

Contract Axis

Dimensions must line up everywhere

A 768-dimensional embedding is not just an array with 768 numbers. It is a contract that determines whether arithmetic, distance, and indexing are even legal.

Format Axis

Float, integer, and binary are not interchangeable

Oracle documents multiple vector element formats, and binary vectors carry extra rules such as dimension counts that must align to byte boundaries.

Representation Axis

Dense and sparse change how you move data

Dense vectors fit ordinary arrays well. Sparse vectors need index-and-value representations, especially in JDBC and text conversions.

Interop Axis

Transport form is not storage semantics

A vector can travel as a SQL literal, a PL/SQL value, a Java array, a sparse helper object, or a JSON scalar. The transport changes; the underlying contract should not.

The easiest mistake to make

Letting the client library or JSON shape define the database contract by accident. Decide the column contract first, then choose the interface representation that preserves it.

Section 2

SQL is the control plane: type declaration, search behavior, arithmetic, and aggregates all start here

2

Regardless of how vectors arrive — JDBC, JSON, PL/SQL — SQL is where the column contract is defined. It sets the shape, the legal function set, and the query patterns every other interface has to respect.

Declare the vector contract explicitly

For production tables, fixed dimensions usually make intent clearer than leaving the dimension count unconstrained. Dense embeddings, sparse term-space vectors, and binary feature vectors are different data contracts and should usually live in different columns rather than being mixed into one flexible column.

SQL • DDL contract

CREATE TABLE vector_interface_demo (
  doc_id            NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  tenant_id         NUMBER NOT NULL,
  title             VARCHAR2(200) NOT NULL,
  dense_embedding   VECTOR(3, FLOAT32),
  sparse_terms      VECTOR(30000, FLOAT32, SPARSE),
  feature_bits      VECTOR(1024, BINARY),
  payload           JSON
);

VECTOR(3, FLOAT32)

A compact demo shape. In production, pin this to the actual model dimension count your application standardizes on.

VECTOR(30000, FLOAT32, SPARSE)

A high-dimensional sparse signal where only a small percentage of positions are non-zero.

VECTOR(1024, BINARY)

A bit-oriented representation. Oracle documents that binary vectors require dimensions that are a multiple of eight.

Use SQL functions to convert and inspect values safely

TO_VECTOR is the conservative entry point when you are moving a textual or JSON representation into a typed vector value. FROM_VECTOR is the matching round-trip tool when you need a stable serialized form for inspection, testing, or compatibility.

SQL • Insert and inspect

INSERT INTO vector_interface_demo (
  tenant_id,
  title,
  dense_embedding,
  sparse_terms,
  payload
) VALUES (
  10,
  'Quarterly product memo',
  TO_VECTOR('[0.12, -0.08, 0.34]', 3, FLOAT32),
  TO_VECTOR('[30000, [12, 98, 4201], [0.9, 0.6, 0.4]]', 30000, FLOAT32, SPARSE),
  JSON_OBJECT('source' VALUE 'sql-seed')
);

SELECT
  doc_id,
  FROM_VECTOR(dense_embedding)  AS dense_literal,
  FROM_VECTOR(sparse_terms)     AS sparse_literal
FROM vector_interface_demo;

Nearest-neighbor SQL is still ordinary SQL

The only vector-specific part is the distance expression. Everything around it is normal Oracle SQL: relational filters, joins, JSON predicates, security filters, top-N logic. That same pattern is what PL/SQL blocks and JDBC clients execute.

SQL • Similarity search with relational filters

SELECT
  doc_id,
  title,
  COSINE_DISTANCE(
    dense_embedding,
    TO_VECTOR('[0.11, -0.10, 0.37]', 3, FLOAT32)
  ) AS distance
FROM vector_interface_demo
WHERE tenant_id = 10
ORDER BY distance
FETCH FIRST 5 ROWS ONLY;

Arithmetic and aggregates help when the database is also doing vector preparation

Oracle documents vector arithmetic and aggregate support in SQL, but the rules matter. Arithmetic is for dense numeric vectors and does not extend to sparse or binary vectors. Division is not supported. Aggregate functions such as SUM and AVG operate on vectors and return a FLOAT64 vector result, which is useful for centroids or averaged embeddings.

SQL • Arithmetic and aggregates

SELECT
  TO_VECTOR('[1, 2, 3]', 3, FLOAT32) + TO_VECTOR('[4, 5, 6]', 3, FLOAT32) AS added_vector,
  TO_VECTOR('[1, 2, 3]', 3, FLOAT32) * 0.5                                 AS scaled_vector
FROM dual;

SELECT
  tenant_id,
  AVG(dense_embedding) AS average_embedding,
  SUM(dense_embedding) AS sum_embedding
FROM vector_interface_demo
GROUP BY tenant_id;

What SQL does not let you treat as ordinary scalar behavior

Area	Practical meaning
Direct comparison and ordering	A vector value is not something you sort or compare directly like a number or date. Sort by a distance expression, not by the vector column itself.
Grouping and joining	Group by business keys or dimensions, then aggregate vectors inside the group. Join on relational keys, not on the vector value itself.
Mixed-representation arithmetic	Do not assume a sparse or binary vector can flow through the same arithmetic path as a dense numeric vector. Oracle documents separate boundaries here.
Metric ambiguity	Be explicit about the distance function when readability matters. Hidden metric assumptions become hard to spot once SQL is embedded in PL/SQL packages or Java code.

Section 3

PL/SQL is where vector-aware server logic becomes maintainable

3

PL/SQL is less about ad hoc retrieval and more about keeping logic inside the database: validation packages, pre-insert checks, query wrappers, and application APIs that need to stay close to the data.

Dense vector arithmetic works naturally in PL/SQL when the vector contract already fits

Oracle documents vector variables, arithmetic operators, and distance functions in PL/SQL. That makes it possible to build compact database-side routines for rescaling vectors, combining multiple signals, or checking similarity thresholds before a row is committed to a downstream workflow.

PL/SQL • Dense vector logic

SET SERVEROUTPUT ON;

DECLARE
  base_vec    VECTOR := TO_VECTOR('[0.35, 0.11, -0.08]', 3, FLOAT32);
  delta_vec   VECTOR := TO_VECTOR('[0.02, -0.01, 0.05]', 3, FLOAT32);
  blended_vec VECTOR;
  similarity  BINARY_DOUBLE;
BEGIN
  blended_vec := base_vec + delta_vec;
  similarity  := COSINE_DISTANCE(base_vec, delta_vec);

  DBMS_OUTPUT.PUT_LINE('Blended  : ' || FROM_VECTOR(blended_vec));
  DBMS_OUTPUT.PUT_LINE('Distance : ' || TO_CHAR(similarity));
END;
/

What works well

Validation and orchestration

PL/SQL is a good place to enforce dimension checks, route between dense and sparse processing branches, and keep query assembly close to data security and transactional logic.

What to avoid

Assuming every vector path is arithmetic-friendly

Oracle documents that arithmetic operators do not apply to sparse or binary vectors, and division is not supported. Keep those limitations visible in package design.

PL/SQL is also the right place to wrap query patterns your application should not handcraft repeatedly

A common production pattern is to expose a package procedure that accepts business filters and a query vector, while the package owns the exact SQL shape. That centralizes tenant predicates, metric choice, and top-N policy instead of letting every client assemble its own similarity SQL.

PL/SQL • Package-style query wrapper

CREATE OR REPLACE PROCEDURE fetch_similar_docs (
  p_tenant_id   IN  NUMBER,
  p_query_vec   IN  VECTOR,
  p_limit       IN  NUMBER,
  p_result      OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_result FOR
    SELECT *
    FROM (
      SELECT doc_id,
             title,
             COSINE_DISTANCE(dense_embedding, p_query_vec) AS distance
      FROM   vector_interface_demo
      WHERE  tenant_id = p_tenant_id
      ORDER  BY distance
    )
    WHERE ROWNUM <= p_limit;
END;
/

Sparse support is powerful, but version awareness matters

Sparse-vector support in PL/SQL is a newer 26ai capability. If your project uses sparse vectors inside stored procedures, check the release update level of the target estate before locking the package contract — SQL features sometimes move ahead of database patching in application-owned PL/SQL packages.

PL/SQL review points

• Keep type enforcement in one package boundary so client code does not have to rediscover vector rules.
• Use FROM_VECTOR when logging or debugging values; it is clearer than ad hoc string handling.
• Separate dense arithmetic workflows from sparse retrieval workflows instead of trying to share one procedure signature for everything.
• Be explicit about release-update dependencies before promising sparse PL/SQL behavior to application teams.

Section 4

JDBC gives Java applications a typed path into the vector model, including sparse helpers

4

JDBC is where a clean database design can break down in practice. If Java code is guessing at types, building vector literals as strings, or losing sparse semantics on the way in, the application fails regardless of how well the SQL side is designed.

Dense bindings are straightforward when you use typed setObject calls

Oracle documents JDBC support for Java primitive arrays and OracleType vector constants. That lets a client bind dense vectors without going through fragile string serialization.

Java • Dense vector bind and search

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.OracleType;

PreparedStatement insert = conn.prepareStatement(
    "insert into vector_interface_demo(doc_id, tenant_id, title, dense_embedding) " +
    "values (?, ?, ?, ?)");

insert.setLong(1, 101L);
insert.setLong(2, 10L);
insert.setString(3, "Java-bound embedding");
insert.setObject(4, new float[] {0.12f, -0.08f, 0.34f}, OracleType.VECTOR_FLOAT32);
insert.executeUpdate();

PreparedStatement search = conn.prepareStatement(
    "select doc_id, title, cosine_distance(dense_embedding, ?) as distance " +
    "from vector_interface_demo where tenant_id = ? order by distance fetch first 5 rows only");

search.setObject(1, new float[] {0.11f, -0.10f, 0.37f}, OracleType.VECTOR_FLOAT32);
search.setLong(2, 10L);

ResultSet rs = search.executeQuery();
while (rs.next()) {
  long docId = rs.getLong("doc_id");
  String title = rs.getString("title");
  double distance = rs.getDouble("distance");
}

Fetching vectors is safer with explicit target classes

Oracle documents ResultSet.getObject(column, Class) mappings for vector values. That matters because a default getObject call is not the place to discover how the driver chose to represent a vector. Decide the expected Java type and request it explicitly.

Java • Explicit fetch mapping

PreparedStatement fetch = conn.prepareStatement(
    "select dense_embedding from vector_interface_demo where doc_id = ?");

fetch.setLong(1, 101L);

ResultSet oneRow = fetch.executeQuery();
if (oneRow.next()) {
  float[] embedding = oneRow.getObject(1, float[].class);
}

Sparse vectors need the sparse APIs, not home-grown conventions

Oracle extends JDBC support with sparse helper types such as VECTOR.SparseFloatArray and VECTOR.SparseBooleanArray. This is more than a convenience feature. It prevents every Java service from inventing its own shape for index arrays and value arrays, which is exactly how sparse workflows become inconsistent across teams.

Java • Sparse vector bind and fetch

import oracle.sql.VECTOR;

VECTOR.SparseFloatArray sparse = VECTOR.SparseFloatArray.of(
    30000,
    new int[]   {12, 98, 4201},
    new float[] {0.9f, 0.6f, 0.4f}
);

PreparedStatement sparseInsert = conn.prepareStatement(
    "update vector_interface_demo set sparse_terms = ? where doc_id = ?");

sparseInsert.setObject(1, sparse);
sparseInsert.setLong(2, 101L);
sparseInsert.executeUpdate();

PreparedStatement sparseFetch = conn.prepareStatement(
    "select sparse_terms from vector_interface_demo where doc_id = ?");

sparseFetch.setLong(1, 101L);
ResultSet sparseRow = sparseFetch.executeQuery();
if (sparseRow.next()) {
  VECTOR.SparseFloatArray sparseValue =
      sparseRow.getObject(1, VECTOR.SparseFloatArray.class);
}

JDBC concern	Recommended pattern	What goes wrong otherwise
Dense insert/update	Bind primitive arrays with the matching OracleType.VECTOR_* constant.	String literals spread serialization rules across the codebase and hide type mismatches until runtime.
Dense fetch	Use getObject(..., float[].class), double[].class, or another explicit class mapping.	Default fetch behavior becomes driver-configuration dependent instead of application-contract dependent.
Sparse bind/fetch	Use VECTOR.Sparse* helper classes and keep the database length explicit.	Each service invents a different sparse payload shape and interoperability collapses.
Older client estate	Use Oracle’s documented compatibility path with string or CLOB representations where newer vector-aware JDBC support is not yet available.	Mixed driver fleets fail unpredictably once teams assume every Java node supports the newest mappings.

Sparse index convention

Sparse indices in Oracle's string form are zero-based. The sparse helper APIs carry an explicit total length. Keep that convention consistent across SQL test fixtures, JDBC code, and debug output — mixing conventions is a common source of silent bugs.

Section 5

JSON support and OSON typed behavior: what changes and what to watch

5

Oracle's JSON type in OSON includes vector as a scalar type. That changes how faithfully a vector moves between relational storage and document-oriented application structures — a vector scalar and a JSON array of numbers are not the same thing.

A JSON array of numbers is not the same thing as a JSON vector scalar

Applying the SQL JSON constructor to a vector produces a JSON array of numbers. JSON_SCALAR preserves the value as a vector scalar inside the JSON type. Both convert back to a SQL vector, but they are different representations with different interoperability implications.

SQL • JSON array versus vector scalar

UPDATE vector_interface_demo
SET payload = JSON_OBJECT(
  'docId'            VALUE doc_id,
  'embeddingArray'   VALUE JSON(dense_embedding),
  'embeddingScalar'  VALUE JSON_SCALAR(dense_embedding)
)
WHERE doc_id = 101;

SELECT
  JSON_VALUE(payload, '$.embeddingArray'  RETURNING VECTOR) AS array_roundtrip,
  JSON_VALUE(payload, '$.embeddingScalar' RETURNING VECTOR) AS scalar_roundtrip
FROM vector_interface_demo
WHERE doc_id = 101;

Use an array when

Text-first interoperability matters more than preserving vector identity inside JSON

A JSON array is easy for generic tools and non-Oracle clients to inspect. It is a good interchange form when the JSON document must remain conventional everywhere.

Use JSON_SCALAR when

You want JSON type fidelity inside Oracle

A vector scalar in OSON preserves stronger typing and makes the document a better host for database-native vector values rather than just number arrays.

JSON support is broader than one extraction function

Oracle documents vector-aware behavior across the JSON stack, including JSON_VALUE, JSON_OBJECT, JSON_ARRAY, JSON_TABLE, JSON_SCALAR, JSON_TRANSFORM, and JSON_SERIALIZE. That gives you more than just a round-trip trick. It means vector values can sit inside hybrid document-relational workflows without immediately collapsing into plain text.

SQL • JSON extraction inside a query

SELECT
  doc_id,
  COSINE_DISTANCE(
    JSON_VALUE(payload, '$.embeddingScalar' RETURNING VECTOR),
    TO_VECTOR('[0.11, -0.10, 0.37]', 3, FLOAT32)
  ) AS json_distance
FROM vector_interface_demo
WHERE JSON_EXISTS(payload, '$.embeddingScalar')
ORDER BY json_distance
FETCH FIRST 5 ROWS ONLY;

JSON design checklist

• Choose between array representation and vector-scalar representation deliberately.
• Keep one authoritative vector source if both a vector column and a JSON copy exist in the same row.
• Use RETURNING VECTOR explicitly during extraction instead of hoping an implicit conversion is obvious to future maintainers.
• Remember that JSON is excellent for interoperability, but it should not become a shadow schema for the same vector contract.

Section 6

Choose interfaces by responsibility, not by habit

6

Give each interface a clear job. SQL defines and queries. PL/SQL encapsulates server logic. JDBC moves typed values in and out. JSON exposes vector-bearing documents without flattening everything to strings.

Best Fit

SQL

Schema design, nearest-neighbor queries, aggregate vector calculations, hybrid relational filtering, and canonical validation.

Best Fit

PL/SQL

Stored routines, package-owned query shapes, dimension checks, and controlled orchestration close to transactional logic.

Best Fit

JDBC

Application-facing inserts, updates, fetches, and typed dense or sparse vector bindings from Java services.

Best Fit

JSON

Document-centric models, API payload assembly, OSON interoperability, and vector-aware JSON extraction inside SQL.

Decision point	Prefer this interface	Reason
Column definition and type rules	SQL	Only SQL can define the storage contract that every other interface must honor.
Application writes dense embeddings from Java	JDBC	Typed array binds are cleaner and safer than constructing vector literals in application code.
Application writes sparse vectors from Java	JDBC sparse helpers	Oracle’s sparse classes keep index/value structure explicit and consistent.
Reusable search policy shared by many clients	PL/SQL wrapping SQL	One package can own tenant filters, metric choice, top-N policy, and query stability.
Expose vectors inside JSON documents while staying typed in Oracle	JSON with JSON_SCALAR	It preserves vector identity inside the JSON type rather than flattening to a plain array immediately.
Cross-tool interchange with generic JSON clients	JSON array form	Arrays are more conventional for external tooling even when the database can preserve a vector scalar.

Interface discipline

JDBC moves vectors. SQL reasons about them. PL/SQL enforces policy. JSON exposes them for document workflows. Systems get fragile when one layer tries to do another layer's job.

📘

eBook

Exadata DBA Guide

A comprehensive PDF guide for Oracle DBAs covering Exadata architecture, Smart Scan, Flash Cache, Storage HA and performance tuning.

Get the PDF →

📘

eBook

Exadata DBA Guide

A comprehensive PDF guide for Oracle DBAs covering Exadata architecture, Smart Scan, Flash Cache, Storage HA and performance tuning.

Get the PDF →

Section 7

A compact workflow for building and validating a vector-aware application path

7

Validate in order. Establish the SQL contract first. Then prove PL/SQL handling. Then bind from JDBC. Then round-trip through JSON. Skipping steps is where most interface bugs get introduced.

Step 1

Freeze the column contract

Pick dimensions, element format, and dense-versus-sparse semantics before any application code appears.

Step 2

Validate with SQL only

Insert values with TO_VECTOR, inspect them with FROM_VECTOR, and run distance and aggregate queries.

Step 3

Add PL/SQL wrappers

Centralize filters, package signatures, and debug-friendly logging before clients multiply.

Step 4

Bind from JDBC with explicit types

Use dense arrays or sparse helper types directly and keep fetch mappings explicit.

Step 5

Test JSON round-trips separately

Verify array form and vector-scalar form independently so you do not conflate them later.

Step 6

Lock the diagnostics path

Make sure operators can inspect values with FROM_VECTOR and identify which interface wrote the row.

Validation SQL

Confirm that the row inserted through JDBC produces the same FROM_VECTOR output shape as a row inserted through SQL fixtures.

Validation PL/SQL

Call the package wrapper with a known query vector and verify that tenant predicates and top-N logic match the direct SQL result.

Validation JSON

Store both JSON(dense_embedding) and JSON_SCALAR(dense_embedding), then extract each with RETURNING VECTOR and compare distance results.

Operational proof

Capture which client path wrote the row so debugging can answer whether a defect comes from SQL logic, PL/SQL wrapping, JDBC binding, or JSON conversion.

SQL • A minimal end-to-end verification query

SELECT
  doc_id,
  title,
  FROM_VECTOR(dense_embedding) AS dense_sql,
  FROM_VECTOR(JSON_VALUE(payload, '$.embeddingScalar' RETURNING VECTOR)) AS dense_from_json,
  COSINE_DISTANCE(
    dense_embedding,
    JSON_VALUE(payload, '$.embeddingScalar' RETURNING VECTOR)
  ) AS sql_vs_json_distance
FROM vector_interface_demo
WHERE doc_id = 101;

Release-readiness checklist

• Every interface writes the same dimension count and numeric format for the same logical column.
• JDBC uses explicit bind and fetch types rather than relying on default object mappings.
• PL/SQL packages own policy-heavy query shapes that should not be duplicated in clients.
• JSON documents use either vector scalars or arrays by design, not by accident.
• Debugging paths can serialize vectors without custom application-side formatting code.

Section 8

Caveats, diagnostics, and common questions

8

Most failures are not vector search failures — they are interface drift failures. One layer writes dense values, another expects sparse. One service binds typed arrays, another sends strings. One JSON path preserves vector identity, another silently converts it to a plain number array.

Symptom	Likely cause	What to check first
Dimension mismatch errors during insert or search	The client-side representation no longer matches the SQL column contract.	Check the declared vector dimensions and the exact shape being sent by TO_VECTOR, PL/SQL variables, or JDBC binds.
Arithmetic works in one path but not another	A sparse or binary vector is hitting code written for dense numeric arithmetic.	Verify the column format and whether the logic is using arithmetic operators that Oracle documents only for dense numeric vectors.
Java service can write vectors but cannot read them cleanly	Fetch logic is using ambiguous default object mapping.	Switch to getObject(..., targetClass) or an Oracle-documented default mapping configuration.
JSON payload looks right, but extraction does not behave as expected	The workflow stored a JSON array when it meant to preserve a vector scalar, or vice versa.	Inspect whether the value came from JSON(...) or JSON_SCALAR(...).
Mixed clients disagree on representation	One estate uses newer JDBC vector support while another still relies on compatibility serialization.	Inventory driver versions and normalize the contract per deployment tier.

Should I expose vectors through JSON if I already have a vector column?

Only when the document model adds real value. JSON is useful for payload assembly, API integration, and document-centric workflows, but duplicating the same vector in two places without a clear ownership rule creates drift quickly.

When is PL/SQL worth using instead of letting JDBC send raw SQL?

Use PL/SQL when many clients need the same search policy, tenant restrictions, validation logic, or debug behavior. If every service reassembles the same similarity SQL independently, correctness will drift.

Should JDBC code ever build vector literals as strings?

Prefer typed binds when the current driver supports them. String literals are best kept for compatibility paths, fixtures, or troubleshooting rather than normal application writes.

Is a JSON array good enough if I never use OSON-specific semantics?

Often yes. If your priority is broad interoperability, arrays are simpler. If you want Oracle JSON type fidelity and vector-aware operations inside the JSON type, use the vector scalar route deliberately.

What should I prove before I add vector indexes or more elaborate retrieval logic?

Prove that vectors arrive with the right dimensions, the right representation, the right metric assumptions, and the same meaning across SQL fixtures, PL/SQL wrappers, JDBC clients, and JSON payloads. Fast retrieval on the wrong contract only makes mistakes more expensive.

Knowledge check

Quick quiz

?

Five questions on Oracle vector interfaces. Pick one answer then hit Submit.

Q1. What is the core vector contract that every interface must preserve?

The JDBC driver version used by the application The JSON serialization format chosen at the API layer Dimension count, element format, and dense or sparse storage — as defined by the SQL column declaration The embedding model name stored in application metadata

Correct: C. SQL, PL/SQL, JDBC, and JSON do not each define their own semantics. The contract is the vector itself: dimension count, element format, and storage type. Every interface is a different way to work with that same underlying value — and any layer that silently changes it causes drift.

Q2. What is the difference between using the SQL JSON() constructor and JSON_SCALAR() on a vector value?

They produce identical output — both create a JSON array of numbers JSON() produces a JSON array of numbers; JSON_SCALAR() preserves the value as a vector scalar inside the OSON type JSON_SCALAR() is only for binary vectors; JSON() works for all formats JSON() requires a FLOAT32 column; JSON_SCALAR() works with any format

Correct: B. Applying JSON() to a vector produces a plain JSON array of numbers — good for broad interoperability but it loses vector type identity. JSON_SCALAR() preserves the value as a vector scalar in OSON. Both convert back to a SQL vector, but they behave differently inside JSON-aware processing.

Q3. When is PL/SQL the right layer to implement vector logic rather than leaving it in JDBC clients?

Always — PL/SQL is faster than JDBC for all vector operations Only when the vector column uses FLOAT64 format Only when the table has a vector index When multiple clients share the same search policy, validation logic, or tenant restrictions and you don't want each service to reassemble it independently

Correct: D. PL/SQL is valuable when many clients need the same behavior — search policy, tenant isolation, validation, or debug logic. If every service assembles its own similarity SQL independently, correctness drifts over time. A shared PL/SQL wrapper prevents that.

Q4. What is the recommended approach for binding vector values in JDBC?

Use typed binds with Oracle's documented vector types rather than building string literals in application code Always serialize to a JSON string before binding Use PreparedStatement.setString() with a vector literal for best portability JDBC does not support vector types — use a REST API instead

Correct: A. Typed binds preserve vector semantics cleanly and avoid the risk of malformed string literals reaching the database. String literals are acceptable for compatibility paths or test fixtures but should not be the normal production approach when the driver supports typed vector binding.

Q5. What does the post recommend proving before adding vector indexes or more elaborate retrieval logic?

That HNSW index creation completes without errors That vectors arrive with the right dimensions, format, metric assumptions, and consistent meaning across SQL, PL/SQL, JDBC, and JSON paths That the embedding model is loaded into ONNX format That JSON_SCALAR is used consistently instead of JSON()

Correct: B. Fast retrieval on the wrong contract only makes mistakes more expensive. Before adding indexes or retrieval complexity, prove that vectors arrive with the right shape and carry the same meaning across every interface path — SQL fixtures, PL/SQL wrappers, JDBC clients, and JSON payloads.

Knowledge check

Quick quiz

?

Five questions on working with vectors across SQL, PL/SQL, JDBC, and JSON. Pick one answer then hit Submit.

Q1. Which interface defines the canonical vector contract that all other interfaces must respect?

JDBC, because it is the primary application entry point JSON, because it carries the vector between services SQL, because only SQL DDL can define column shape, element format, and the function set every other interface must honor PL/SQL, because stored packages control data access

Correct: C. SQL is the canonical layer. DDL defines the column shape, element format, and legal functions. PL/SQL, JDBC, and JSON all operate within those constraints — none of them can redefine the contract that SQL establishes.

Q2. What is the key difference between using the JSON constructor and JSON_SCALAR when working with vectors in Oracle JSON?

JSON constructor produces a JSON array of numbers; JSON_SCALAR preserves the value as a typed vector scalar in OSON JSON_SCALAR compresses the vector; JSON constructor preserves precision They are identical — Oracle treats both as the same OSON type internally JSON constructor only works for dense vectors; JSON_SCALAR is required for sparse

Correct: A. Applying the JSON constructor to a vector produces a JSON array of numbers — it loses the typed vector identity. JSON_SCALAR preserves the value as a vector scalar inside the OSON type. Both can be converted back, but they are not the same JSON representation and the round-trip behavior differs.

Q3. What is PL/SQL best suited for in a vector workflow — and what should it not be used for?

Ad hoc nearest-neighbour queries and embedding generation Client-side type binding and JDBC result set handling JSON document assembly and OSON round-trips Server-side validation, orchestration, dimension checks, and stored query wrappers — not ad hoc retrieval

Correct: D. PL/SQL is the right tool for durable server-side logic: validation routines, pre-insert dimension checks, orchestration packages, and stored query wrappers that need to stay close to the data. It is not the right tool for ad hoc retrieval — that belongs in SQL.

Q4. What is the most common cause of vector failures in multi-interface systems according to this post?

The wrong distance metric in the HNSW index Interface drift — layers writing different types, formats, or representations than the contract specifies Insufficient SGA memory for the vector pool Using FLOAT32 instead of FLOAT64 for embeddings

Correct: B. The post identifies interface drift as the dominant failure mode. One layer writes dense, another expects sparse. One service binds typed arrays, another sends strings. One JSON path preserves vector identity, another silently turns it into a plain number array. These are type contract failures, not vector search algorithm failures.

Q5. What order does the post recommend for validating a multi-interface vector workflow?

SQL contract first, then PL/SQL, then JDBC, then JSON JDBC first, then JSON, then PL/SQL, then SQL All interfaces at the same time using an integration test JSON first because it is the most common transport layer

Correct: A. SQL first — establish the storage contract. Then PL/SQL — prove server-side handling. Then JDBC — confirm typed bindings work. Then JSON — round-trip the vector through document structures. Each step depends on the previous one being confirmed, which is why validating all interfaces simultaneously makes failures harder to isolate.