Small Oracle 23ai Syntax Wins That Saved Me Real Time

Who this is for: Oracle developers and DBAs who write a lot of ad hoc SQL and data-seeding scripts, and who have spent years quietly annoyed by Oracle’s insert and DUAL table ceremony.

Two Fridays ago a business analyst handed me a spreadsheet: 34 rows of reference codes and descriptions that needed to land in a lookup table on QA before a release went out that afternoon. Nothing complicated, just a straight seed job. My old habit for this kind of task was to write a quick spreadsheet formula that turned each row into an INSERT INTO ref_codes VALUES (...) statement, paste all 34 into a SQL*Plus session, and watch them scroll by one at a time. It always worked. It always felt like more ceremony than the task deserved.

This time the QA instance was on Oracle 23ai, so I tried something different: one INSERT statement with all 34 rows in the VALUES clause, comma-separated, the way you’d write it in Postgres or MySQL. It worked on the first try, ran as a single round trip, and took about ninety seconds start to finish instead of the usual ten or fifteen minutes of generating and babysitting individual statements. Later that same afternoon, chasing a rounding discrepancy, I typed select 42*1.0825; out of muscle memory after a week of writing Postgres queries and forgot the from dual. It just ran. No ORA-00923, no reflexive fix. Neither of those is a headline feature. But they’re exactly the kind of small friction that adds up over years of writing ad hoc SQL, and Oracle finally sanded both edges down.

Why This Matters

Oracle’s insert and DUAL conventions go back decades and exist for real reasons: the relational model expects a FROM clause, and inserting one row at a time maps cleanly onto how the parser was originally built. But developers who move between Oracle and Postgres, MySQL, or SQL Server have had to context-switch every time, remembering that Oracle needs a FROM DUAL for a plain scalar SELECT, and that faking a multi-row insert meant reaching for INSERT ALL, a multitable statement built for a completely different use case and never a natural fit for “just insert these ten rows.” Data-seeding scripts, migration one-offs, and quick ad hoc checks are exactly the kind of work where that ceremony cost real time without buying anything, and it’s the sort of gap that made Oracle feel dated next to the open-source databases for routine day-to-day SQL, even when nothing architectural was actually wrong with it.

The Old Way

Before 23ai, getting several rows into a table in one statement meant reaching for INSERT ALL, which is really a multitable insert feature being pressed into service for a single table:

			
INSERT ALL
  INTO ref_codes (code, description) VALUES ('A1', 'Active')
  INTO ref_codes (code, description) VALUES ('A2', 'Archived')
  INTO ref_codes (code, description) VALUES ('A3', 'Pending')
SELECT * FROM dual;

		

It works, but it reads backwards for a simple seed job and it still requires that trailing SELECT * FROM dual just to give the statement something to select from. And a plain scalar query without a table always needed the same crutch:

			
SQL> select 42*1.0825;
select 42*1.0825
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select 42*1.0825 from dual;
42*1.0825
----------
   45.465

		

Every Oracle developer has hit that ORA-00923 at least once after a stretch of writing SQL on another database. It’s not a hard error to fix, but it’s a paper cut you pay for every single time.

Diagram comparing old Oracle INSERT ALL and FROM DUAL syntax against the simpler Oracle 23ai multi-row insert and select without from syntax

The 23ai Syntax

Oracle Database 23ai extended the table values constructor so a plain INSERT ... VALUES can carry multiple rows directly, comma-separated, no INSERT ALL and no trailing SELECT:

			
INSERT INTO ref_codes (code, description)
VALUES ('A1', 'Active'),
       ('A2', 'Archived'),
       ('A3', 'Pending');

That single statement is one network round trip, which is where most of the time savings on my 34-row seed script actually came from, not from the database doing less work per row, but from the client and server talking to each other once instead of 34 times. The same table values constructor also works in the FROM clause of a SELECT, in a WITH clause, and as the source for a MERGE, so you can build a small inline dataset anywhere you’d otherwise need a real table or a UNION ALL chain:

			
with a (id, code, description) as (
  values (7, 'SEVEN', 'Description for SEVEN'),
         (8, 'EIGHT', 'Description for EIGHT'),
         (9, 'NINE', 'Description for NINE')
)
select * from a;

		

And the DUAL requirement is gone for plain scalar selects. This is functionally identical to adding from dual yourself, just without having to type it:

			
SQL> select 42*1.0825;
42*1.0825
----------
   45.465

It works in PL/SQL too, so a select sysdate into v_date; without a FROM clause compiles and runs exactly like you’d expect.

Where This Requires Care

The multi-row insert is all-or-nothing. If any single row in the VALUES list fails validation, such as a value too long for a column, none of the rows are inserted, not even the ones that would have succeeded. I tested this deliberately: a three-row insert with a bad middle row threw ORA-12899 and left the table exactly as it was before. That’s worth knowing before you lean on this for a seed script with data you haven’t fully validated.
This needs Oracle Database 23ai or later. Scripts that use the multi-row VALUES syntax or a FROM-less SELECT will fail with a syntax error on 19c or 21c, so if you maintain code that has to run across a mixed fleet of database versions, don’t bake this in without a fallback path.
SELECT without FROM is syntactic sugar, not a performance feature. Oracle’s own trace output shows the optimizer rewriting the statement to include FROM "SYS"."DUAL" before it ever reaches the execution plan, so don’t expect it to behave any differently than the version you spelled out by hand.
It doesn’t give you implicit result sets from a stored procedure. A bare SELECT with no INTO clause inside a procedure body still fails to compile with PLS-00428. You still need a SELECT ... INTO or a ref cursor with DBMS_SQL.RETURN_RESULT if you want data out of a procedure.
Third-party tooling can lag behind. Some SQL builders and ORM query generators didn’t parse the new multi-row VALUES syntax right away, so if you’re generating SQL programmatically rather than typing it by hand, check that your library actually supports it before you standardize on it in shared code.

Quick Reference

Use INSERT INTO t (cols) VALUES (...), (...), (...) instead of INSERT ALL for straight multi-row seeds on 23ai and later.
Drop FROM DUAL from plain scalar SELECTs; it still works if you leave it in, so there’s no rush to rewrite old scripts.
The same VALUES constructor works in FROM clauses, WITH clauses, and as a MERGE source, useful for quick inline test data without a scratch table.
Multi-row inserts are all-or-nothing. Validate your data first, especially on seed scripts built from a spreadsheet.
Both features require 23ai or later and are unavailable on 19c and 21c.

My Take

Neither of these features will show up in a keynote, and neither one changes how you architect anything. That’s exactly why I like them. Most of my day-to-day friction with Oracle was never about the big engine-level stuff, it was about small syntax gaps that cost a few seconds each, dozens of times a day, for years. Multi-row inserts and FROM-less selects close two of the most common ones, and they read as a sign Oracle is finally willing to spend engineering effort on plain typing ergonomics instead of only on marquee AI and vector features. I’d like to see the same attention go toward a few other long-standing papercuts, quoted identifier handling and date literal parsing come to mind, but this release update is a genuinely welcome start.