DEV Community

Vinish Kapoor
Vinish Kapoor

Posted on

What’s New in Oracle Database 23ai

#oracle #database #programming

Oracle Database 23ai is the latest version of Oracle’s powerful database. It is called “AI-native” because it introduces features that help you work with artificial intelligence, modern applications, and data in smarter ways. If you are just starting out, think of it like this: older Oracle versions could store and query data very well, but now Oracle 23ai lets you use that data for AI tasks, advanced search, and easier development — all inside the same database.

In this article, I’ll explain the main new features in Oracle Database 23ai in a way that’s simple to understand. I’ll show you one clear SQL example for JSON (so you see how it works in real life) and for the rest, I’ll explain the ideas step by step without overwhelming you with too much code.

JSON Relational Duality

In the past, you had to choose between storing your data in rows and columns (relational model) or storing it as JSON documents. Oracle 23ai introduces JSON Relational Duality, which means you can store the data once and use it both ways.

This is helpful when your application needs JSON flexibility but you also want strong relational queries.

Here’s a simple example to see it in action:

-- Create a table with a JSON column
DROP TABLE customers PURGE;

CREATE TABLE customers (
  customer_id NUMBER PRIMARY KEY,
  name        VARCHAR2(100),
  email       VARCHAR2(200),
  profile     JSON
);

-- Insert JSON data
INSERT INTO customers VALUES (
  101,
  'John Doe',
  'john.doe@example.com',
  '{"loyaltyLevel": "Gold", "preferences": {"newsletter": true}}'
);

-- Query the full JSON value
SELECT JSON_SERIALIZE(profile PRETTY) AS profile_json
FROM customers
WHERE customer_id = 101;

-- Extract individual fields from JSON
SELECT 
  JSON_VALUE(profile, '$.loyaltyLevel') AS loyalty_level,
  JSON_VALUE(profile, '$.preferences.newsletter') AS newsletter_pref
FROM customers
WHERE customer_id = 101;
Enter fullscreen mode Exit fullscreen mode

Here you can see how one column (profile) stores JSON, and you can pick values from it using JSON_VALUE. This makes it easy to mix both worlds.

Vector Search and AI Integration

One of the most exciting features in Oracle 23ai is Vector Search. Think of vectors as number lists that represent meaning. For example, an AI model can convert the sentence “Oracle Database is powerful” into a vector of numbers. Another sentence with a similar meaning will have a vector close to it.

Oracle 23ai can store these vectors and let you search by meaning, not just by exact words. This is called semantic search.

How it works:

  • You create a table with a VECTOR column.

  • You use a machine learning model (like ONNX) to generate vectors from text.

  • You insert both your text and its vector into the database.

  • When you search, Oracle compares vectors and finds the closest matches.

This means you could search for “database AI” and Oracle will also return documents that talk about “Oracle 23ai vector search” even if the exact words don’t match.

Easier SQL and PL/SQL

Oracle 23ai makes SQL easier to write by adding new shortcuts:

  • SELECT 10*5 AS result; -- Returns 50

  • SELECT IF(salary > 5000, 'High', 'Low') AS salary_band FROM employees;

  • Simpler PL/SQL: Better error handling, more JSON functions, and new search utilities.

These changes save time for developers and make code less complicated.

DBMS_SEARCH Package

Oracle 23ai introduces a new package called DBMS_SEARCH. It lets you do powerful text searches on your data without needing extra tools.

For example, if you want to search job descriptions for “developer,” you can create a search index and then run a query with just one function call.

This is much easier compared to older Oracle versions where you had to set up full text indexes manually.

Developer Productivity

Oracle 23ai also adds small improvements that make life easier:

  • User creation in multitenant databases is simpler. In older versions, you had to be careful about creating users in the right container. Now it’s more direct.

  • Automatic indexing is smarter. Oracle can create, tune, and even drop indexes for you if they are not useful.

  • Better integration with SQL Developer. The latest SQL Developer works smoothly with JSON and vector data, making it easier to debug and explore.

Data Security and Governance

Security is always a priority in Oracle. In 23ai:

  • Encryption defaults are stronger, so your data is safer automatically.

  • Auditing is easier to configure, and audit logs can be viewed in JSON format.

  • Data redaction is improved, which means sensitive data like salaries or credit card numbers can be hidden from unauthorized users automatically.

Real-Life Use Cases

With these updates, Oracle 23ai becomes useful for many modern projects:

  • AI-powered search: Storing vectors for product descriptions and allowing customers to search by meaning instead of just exact keywords.

  • Hybrid applications: Using JSON Relational Duality for apps that sometimes want JSON flexibility and sometimes want relational reliability.

  • Enterprise search: Searching through large amounts of employee records or documents quickly using DBMS_SEARCH.

  • Business analytics: Writing cleaner SQL with IF conditions and doing semantic queries for insights.

If you haven’t tried Oracle Database 23ai yet and want to know how to get started, then check this article on how to install Oracle Database 23ai on Windows and explore the Oracle Database 23ai tutorial for more.

Conclusion

Oracle Database 23ai is not just an upgrade. It is a step into the AI world while keeping Oracle’s traditional strengths. With features like JSON Relational Duality, Vector Search, easier SQL, DBMS_SEARCH, and better security, it helps developers build smarter applications faster.

If you are learning databases, think of Oracle 23ai as a toolbox that lets you work not only with data rows but also with documents, AI embeddings, and secure searches — all inside one system.

Top comments (1)