Wait, look at these FAB syntaxes in 23+!

Clark Pearson
Jul 14
2 min read

Updated: Jul 23

No more DUAL! 23c+

Like many other databases, you can now simply enter:

SELECT USER, SYSDATE;

Thank you Oracle! There’s more?! You betcha!

INSERT-SET, 23.9+

I’ve always been frustrated by the disconnect in syntax between INSERT and UPDATE, much preferring the latter’s 1-to-1 association between column-name and value:

UPDATE <table>

SET COL1 = :val1

, COL2 = :val2;

Clearly, COL1 receives the value from :val1, etc. But with INSERT, of course it’s:

INSERT INTO <tbl>

( ...

, COL23 ...

VALUES

( ...

, :val_ooh_what_column_am_I_on ...

You just know, with UPDATEs, which value is going into which column, but with INSERTs, you have to count. And that slows me down! What if you could do an insert the same way as an update? You can from 23.9!!

INSERT INTO <tbl>

SET COL1 = :val1

, COL2 = :val2;

Thank you, Oracle! There’s more?! You betcha!

INSERT-multiple from binds, 23ai+

VALUES is now a Constructor function and not an insert keyword

VALUES remains syntactically aligned with ANSI standards, but it can also define multiple rows to insert:

INSERT INTO emp

( ID, NAME )

VALUES

( 1, ‘Clark’ ),

( 2, ‘Ruth’ );

Great for loading data, with fewer context switches. Thank you Oracle! There’s more?! You betcha!

Generate inline views more quickly with VALUES

You can now ‘orphan off’ VALUES from its INSERT origins and use it in an inline view. This borrows from the recursive-join syntax to name these anonymous column values:

WITH emplist(id, name) AS (

VALUES

( 1, ‘Clark’ ),

( 2, ‘Ruth’ )

)

SELECT * FROM emplist;

Note you can achieve the above with an alternative and, IMO, less readable syntax, so my gut tells me this is how virtually everyone will choose to code it, should they use the construct.

Thank you, Oracle! There’s more?! You betcha!