Everyone wants to talk about vector search and AI-powered everything when Oracle 23ai comes up. Fair enough, that’s the headline feature. But after spending real hours in SQL*Plus and SQLcl against a 23ai instance, the stuff that’s actually changed my day-to-day isn’t the AI layer; it’s a batch of small SQL fixes that Oracle should honestly have shipped a decade ago. Here are ten of them that I use constantly now, along with the reasons they matter.
1. Native BOOLEAN type
For years, every Oracle shop I’ve worked in had its own house style for representing true/false: a NUMBER(1), a CHAR(1) with ‘Y’/’N’, sometimes both in the same schema depending on who wrote the table. 23ai finally gives you a real BOOLEAN column type, usable in tables, PL/SQL, and JSON handling without the translation layer. It sounds trivial until you’re the one writing a CASE WHEN just to convert ‘Y’ to TRUE for an API response, for the hundredth time.
CREATE TABLE feature_flags (
flag_name VARCHAR2(50),
is_enabled BOOLEAN
);
INSERT INTO feature_flags VALUES ('dark_mode', TRUE);
BEGIN
IF (SELECT is_enabled FROM feature_flags WHERE flag_name = 'dark_mode') THEN
DBMS_OUTPUT.PUT_LINE('Dark mode is on');
END IF;
END;
/
2. SELECT without FROM
SELECT 1+1; now just works, no FROM DUAL required. Small thing. But if you’ve ever had to explain to a Postgres or SQL Server developer why Oracle needs a fake table to add two numbers, you know how much friction this removes. It’s one less gotcha when onboarding people from other database backgrounds onto an Oracle stack.
SELECT 1+1;
SELECT SYSDATE, 42 AS answer;
3. Multi-row inserts
You can now write INSERT INTO t (a,b) VALUES (1,2), (3,4), (5,6); instead of chaining separate INSERT statements or reaching for INSERT ALL. Anyone who’s had to generate seed data or write migration scripts knows this cuts down both the line count and the chance of copy-paste errors.
INSERT INTO employees (id, name, dept) VALUES
(1, 'Alice', 'Engineering'),
(2, 'Bob', 'Sales'),
(3, 'Carla', 'Marketing');
4. IF [NOT] EXISTS for DDL
CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS are now supported directly, without wrapping everything in a PL/SQL block that catches ORA-00942 or ORA-00955. Deployment scripts get a lot cleaner, and you stop littering your code with exception handlers whose only job is to swallow already-exists errors.
CREATE TABLE IF NOT EXISTS audit_log (
id NUMBER GENERATED ALWAYS AS IDENTITY,
event VARCHAR2(200)
);
DROP TABLE IF EXISTS temp_staging;
5. Direct joins for UPDATE and DELETE
23ai lets you reference joined tables more naturally in UPDATE and DELETE statements instead of forcing everything through correlated subqueries or MERGE. If you’ve ever built a MERGE statement just to do a simple conditional update based on another table, this is a welcome shortcut.
-- Oracle 23ai allows more natural join syntax in UPDATE/DELETE
UPDATE orders o
SET o.status = 'CANCELLED'
WHERE EXISTS (
SELECT 1 FROM customers c
WHERE c.id = o.customer_id
AND c.is_blacklisted = TRUE
);
DELETE FROM order_items oi
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.id = oi.order_id
AND o.status = 'CANCELLED'
);
6. Extended data types made easier to use
The 32K-plus VARCHAR2 extended data types have technically been around since 12c, but 23ai smooths out a lot of the rough edges around enabling and using them, especially in combination with JSON and PL/SQL collections. Worth a second look if your team dismissed extended types as too much hassle a few years back.
-- ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=SPFILE;
CREATE TABLE documents (
id NUMBER,
content VARCHAR2(32000)
);
7. Simplified JSON syntax improvements
JSON handling gets several small syntax conveniences – easier dot-notation access, cleaner constructors – that reduce the amount of JSON_VALUE/JSON_QUERY boilerplate you need to write for common cases. If your app stores any semi-structured data in Oracle, this shaves real lines off your queries.
CREATE TABLE orders_json (
id NUMBER,
data JSON
);
SELECT o.data.customer.name, o.data.total
FROM orders_json o
WHERE o.data.status = 'shipped';
8. GROUP BY column position clarity and other query syntax tightening
Small syntax and parser improvements around grouping and ordering reduce ambiguity in queries that used to require extra aliasing or restructuring to satisfy the optimizer. Not flashy, but it means fewer weird ORA errors on queries that should work.
SELECT dept, COUNT(*)
FROM employees
GROUP BY 1
ORDER BY 2 DESC;
9. Improved default values and generated columns
Column defaults and generated columns are more flexible in 23ai, meaning less need to push default logic into triggers or application code. If you’ve ever debugged a trigger whose only job was to populate a timestamp or a default status column, you’ll appreciate moving that logic back into the table definition itself.
CREATE TABLE tasks (
id NUMBER GENERATED ALWAYS AS IDENTITY,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
status VARCHAR2(20) DEFAULT 'PENDING',
days_open NUMBER GENERATED ALWAYS AS (
TRUNC(SYSDATE - created_at)
) VIRTUAL
);
10. Better developer ergonomics in SQLcl
Not a SQL language feature exactly, but 23ai’s timing lines up with a much-improved SQLcl, and the two together make the daily loop of writing, testing, and tweaking queries noticeably less painful. Autocomplete, formatting, and change tracking built in mean less time spent context-switching to other tools.
SQL> set sqlformat ansiconsole
SQL> info employees
SQL> history
SQL> ed my_query.sql
None of this is the AI story Oracle wants on stage at CloudWorld, but it’s the stuff that actually shows up in a code review or a migration script on a random Tuesday. If you’ve been putting off testing 23ai because you assumed it was all vector embeddings and LLM integration, these ten are worth the upgrade on their own.