Oracle SQL Syntax Improvements

Oracle has been busy making a number of syntax changes in recent versions of Oracle. This blog aims to be a one stop shop for a few of the best ones (in my opinion).

No More Dual

Ok, that's Duel rather than Dual (and only oldies will get it anyway). Moving on...

Since version 23c Oracle has made it no longer necessary to do this:

SELECT SYSDATE
FROM DUAL;

Instead you can just do this:

SELECT SYSDATE;

This can look a little bit strange till you get used to it but the above queries are equivalent. A couple of other examples without FROM DUAL:

SELECT 1
UNION ALL
SELECT 2;

SELECT LEVEL CONNECT BY ROWNUM <= 4;

Direct Joins in Update and Delete Statements

You can now (v23ai/26ai) directly join tables within updates and deletes. These are more readable than using subqueries:

UPDATE EMP E
SET E.SAL = E.SAL + (E.SAL * .1)
FROM DEPT D
WHERE E.DEPTNO = D.DEPTNO
  AND D.LOC = 'YORKSHIRE';

Or this:

DELETE FROM EMP E
FROM DEPT D
WHERE E.DEPTNO = D.DEPTNO
  AND D.DNAME = 'RESEARCH';

Insert Improvements

The SET clause - INSERT statements like UPDATE statements...

The following:

INSERT INTO CLAIMS (ID, CREATED_DT, CREATED_BY, RECEIVED_DT, STATUS)
VALUES
(CLAIM_SEQ.NEXTVAL, SYSDATE, USER, :1, :2);

Can be replaced by:

INSERT INTO CLAIMS 
SET ID = CLAIM_SEQ.NEXTVAL, CREATED_DT=SYDATE, CREATED_BY=USER RECEIVED_DT=:1, STATUS=:2;

The SET clause is much easier for column to value alignment making them more readable and far less likely to set any columns to the wrong value.

Oracle also supports multirow inserts like these:

INSERT INTO CLAIMS (ID, CREATED_DT, CREATED_BY, RECEIVED_DT, STATUS)
VALUES
(CLAIM_SEQ.NEXTVAL, SYSDATE, USER, :1, :2),
(CLAIM_SEQ.NEXTVAL, SYSDATE, USER, :3, :4);

And (with SET):

INSERT INTO CLAIMS 
SET (ID = CLAIM_SEQ.NEXTVAL, CREATED_DT=SYDATE, CREATED_BY=USER RECEIVED_DT=:1, STATUS=:2), (ID = CLAIM_SEQ.NEXTVAL, CREATED_DT=SYDATE, CREATED_BY=USER RECEIVED_DT=:3, STATUS=:4);

Note the use of parenthesis here to divide the rows.

I was curious to see if it was possible to use the returning clause returning into a collection e.g. RETURNING ID BULK COLLECT INTO r_ids; but this was not possible for either of the above inserts and resulting in the following errors respectively:

ORA-63853: RETURNING clause is not allowed with a multi-row INSERT INTO SET clause.

ORA-63809: returning clause is not allowed with INSERT and Table Value Constructor

Follow my blog and YouTube Channel for more Oracle coding tips.