10 Oracle 23ai Features That Make Everyday SQL Easier

Oracle 23ai has many new features, but the enhancements that most impact day-to-day SQL usage are relatively modest. Ten of the most frequently used improvements are outlined below.

1. A native BOOLEAN data type provides a true BOOLEAN column type that can be used directly in tables, PL/SQL and JSON handling. This eliminates the need to translate 'Y'/'N' or other encoding schemes.

2. The SELECT 1+1 syntax works without the FROM DUAL table that was traditionally required. It's a small change, but it removes a common source of confusion for developers moving between database platforms.

3. Multi-row INSERT statements let you populate tables in a single statement: INSERT INTO t (a,b) VALUES (1,2), (3,4), (5,6); This cuts down on script length and reduces the chance of errors when generating seed or migration data.

4. CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS are now supported directly, simplifying deployment scripts. The error handling previously required is no longer needed.

5. UPDATE and DELETE statements can reference joined tables more naturally, removing the need for correlated subqueries or MERGE statements in many cases.

6. The extended VARCHAR2 data types have been improved for easier use, especially with JSON and PL/SQL collections. This makes working with larger strings in Oracle more straightforward.

7. JSON syntax has been made more convenient with dot-notation access and cleaner constructors, reducing the amount of boilerplate code needed to work with semi-structured data.

8. Small syntax improvements help clarify query grouping and ordering, reducing obscure errors that previously required workarounds.

9. Column defaults and generated columns are more flexible, allowing default logic to be pushed back into the table definition rather than triggers or application code.

10. The SQLcl tool has received significant improvements, including better autocomplete, formatting and change tracking. This makes the routine workflow of writing, testing and tweaking SQL queries noticeably more efficient.

While AI capabilities get the most attention, these smaller SQL enhancements represent the day-to-day improvements that actually show up in code reviews and migration scripts. They make Oracle 23ai feel like a more modern, developer-friendly database platform.