Oracle 23ai Feature Deep Dive: Native AI Vector Search, JSON-Relational Duality, and SQL Firewall

Executive Summary: The Next-Generation Oracle 23ai Engine

ather than bolting on external vector databases, document stores, or web application firewalls, Oracle 23ai integrates:

1. AI Vector Search: Native VECTOR data types, distance metrics (COSINE, EUCLIDEAN, DOT), and Hierarchical Navigable Small World (HNSW) vector indexes for Retrieval-Augmented Generation (RAG).
2. JSON-Relational Duality Views: Exposing relational tables as fully updateable JSON documents, eliminating Object-Relational Mapping (ORM) overhead.
3. Built-in SQL Firewall: Real-time kernel-level SQL statement monitoring, allow-listing, and SQL injection blocking.

This guide provides DBAs and enterprise architects with an in-depth technical walkthrough and production runbook for deploying these three flagship 23ai technologies as documented in Oracle Database 23ai New Features Guide.

---

🏗️ Architecture & Feature Integration Workflow

---

🚀 Step-by-Step 23ai Feature Implementation Runbooks

1. AI Vector Search: Storing & Querying Embeddings

Oracle 23ai introduces the native VECTOR data type to store high-dimensional mathematical embeddings generated by Large Language Models (LLMs) such as OCI GenAI, OpenAI, or Cohere.

Step 1: Create a Table with VECTOR Data Type

-- Create Knowledge Base Table with Native 1536-Dimension Vector Column
CREATE TABLE enterprise_documents (
    doc_id          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title           VARCHAR2(255) NOT NULL,
    category        VARCHAR2(50),
    document_chunk  CLOB,
    embedding       VECTOR(1536, FLOAT32)
);

Step 2: Create an HNSW Vector Index

Vector indexes dramatically accelerate similarity searches across millions of vector embeddings:

-- Create Hierarchical Navigable Small World (HNSW) Vector Index
CREATE VECTOR INDEX idx_doc_embedding_hnsw 
ON enterprise_documents(embedding) 
ORGANIZATION INMEMORY NEIGHBOR GRAPH 
DISTANCE COSINE 
WITH TARGET ACCURACY 95;

Step 3: Execute Similarity Search with Vector Distance Functions

Query top-K relevant document chunks using VECTOR_DISTANCE:

-- Query Top 3 Most Relevant Document Chunks for a User Query Embedding
SELECT 
    doc_id, 
    title, 
    VECTOR_DISTANCE(embedding, :user_query_vector, COSINE) AS distance_score,
    document_chunk
FROM enterprise_documents
ORDER BY distance_score ASC
FETCH FIRST 3 ROWS ONLY;

---

2. JSON-Relational Duality Views: Unifying Relational & Document Data

JSON-Relational Duality Views allow developers to access normalized relational tables as single, fully updateable JSON documents via REST APIs or MongoDB drivers, while preserving ACID transactions, foreign keys, and SQL relational performance.

Step 1: Define Underlying Relational Tables

-- Relational Table 1: Students
CREATE TABLE students (
    student_id   NUMBER PRIMARY KEY,
    name         VARCHAR2(100),
    email        VARCHAR2(100)
);

-- Relational Table 2: Course Registrations
CREATE TABLE course_registrations (
    registration_id NUMBER PRIMARY KEY,
    student_id      NUMBER REFERENCES students(student_id),
    course_code     VARCHAR2(20),
    grade           VARCHAR2(2)
);

Step 2: Create a JSON-Relational Duality View

-- Create Updateable JSON Duality View
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW student_portal_dv AS
SELECT JSON {
    '_id': s.student_id,
    'studentName': s.name WITH UPDATE,
    'emailAddress': s.email WITH UPDATE,
    'courses': [
        SELECT JSON {
            'registrationId': r.registration_id,
            'courseCode': r.course_code,
            'currentGrade': r.grade WITH UPDATE
        }
        FROM course_registrations r WITH INSERT UPDATE DELETE
        WHERE r.student_id = s.student_id
    ]
}
FROM students s WITH INSERT UPDATE DELETE;

Developers can now execute standard SQL UPDATE or INSERT statements using raw JSON documents directly against student_portal_dv, and Oracle automatically updates the underlying students and course_registrations tables.

---

3. Built-in SQL Firewall: Real-Time Kernel Security Enforcement

Oracle 23ai embeds a SQL Firewall directly inside the database kernel. It monitors SQL statements, captures baseline behavior during a “training” phase, and subsequently blocks unauthorized SQL injection attempts or anomalous query execution.

Step 1: Enable SQL Firewall & Train Application Schema

-- Step 1: Enable SQL Firewall Engine as SYSDBA
EXEC DBMS_SQL_FIREWALL.ENABLE_FIREWALL;

-- Step 2: Create Capture Allowed SQL Execution Baseline for Schema HR_APP
EXEC DBMS_SQL_FIREWALL.CREATE_CAPTURE_IP_LIST(
    username => 'HR_APP', 
    client_ips => '10.200.4.15,10.200.4.16'
);

-- Step 3: Start Baseline Capture (Training Mode)
EXEC DBMS_SQL_FIREWALL.START_CAPTURE(username => 'HR_APP');

Step 4: Stop Capture & Enforce SQL Firewall Rules

After running standard application load tests to train the baseline, stop capture and enforce allow-list rules:

-- Stop Baseline Capture
EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE(username => 'HR_APP');

-- Generate Allowed SQL Rule Set from Baseline
EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST(username => 'HR_APP');

-- Enable Strict Firewall Enforcement (Block Unauthorized SQL & Log Violations)
EXEC DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
    username => 'HR_APP', 
    enforce => DBMS_SQL_FIREWALL.ENFORCE_ALL, 
    block => TRUE
);

Step 5: Audit SQL Firewall Violations

Query DBA_SQL_FIREWALL_VIOLATIONS to monitor blocked SQL injection attempts:

-- Diagnostic: Inspect Blocked SQL Injection & Anomalous Queries
SELECT 
    username, 
    client_ip, 
    top_level_sql_text, 
    firewall_action, 
    cause, 
    occurred_at 
FROM dba_sql_firewall_violations 
ORDER BY occurred_at DESC;

---

🔍 Diagnostic Checklist: Oracle 23ai Component Status

Verify that AI Vector Search and SQL Firewall components are active in your 23ai instance:

-- Diagnostic 1: Check Database 23ai Version and In-Memory Vector Status
SELECT version_full, status FROM v$instance;

-- Diagnostic 2: Verify SQL Firewall Enabled Status
SELECT status FROM dba_sql_firewall_status;

---

📚 Official Documentation & Technical References

• Oracle Database 23ai New Features Guide: Oracle Database 23ai New Features — Comprehensive overview of 23ai core capabilities.
• Oracle Database AI Vector Search User’s Guide: Oracle AI Vector Search Documentation — Official documentation for VECTOR data type, HNSW indexes, distance metrics, and RAG integration.
• Oracle Database JSON-Relational Duality Developer’s Guide: JSON-Relational Duality Views Guide — Architecture and syntax specifications for updateable JSON duality views over relational tables.
• Oracle Database Security Guide: DBMS_SQL_FIREWALL Package Reference — Administering and enforcing kernel-level SQL allow-listing and threat blocking.

---

Need assistance implementing Oracle 23ai AI Vector Search, Duality Views, or SQL Firewall across your enterprise architecture? Contact our Oracle Infrastructure Specialists or explore our Enterprise Health Audits.