SELECT Without FROM Clause - A new feature in Oracle 23ai

Starting from Oracle 23ai, you can now query the DUAL table without FROM clause. Let us demontsrate the same with some examples.

Let's query the the DUAL table in Oracle 19c database. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> select sysdate from dual; SYSDATE --------- 10-NOV-24 SQL> select abs(-27) from dual;   ABS(-27) ----------         27 SQL> select acos(.9) from dual;   ACOS(.9) ---------- .451026812 SQL> select asciistr('ORACLE') from dual; ASCIIS ------ ORACLE SQL> col SESSIONTIMEZONE for a18 SQL> col CURRENT_TIMESTAMP for a35 SQL> select sessiontimezone, current_date from dual; SESSIONTIMEZONE    CURRENT_D ------------------ --------- +05:30             10-NOV-24 SQL> select sessiontimezone, current_timestamp from dual; SESSIONTIMEZONE    CURRENT_TIMESTAMP ------------------ ----------------------------------- +05:30             10-NOV-24 01.02.02.167000 AM +05:30 #Execute query without FROM clause SQL> select sysdate; select sysdate              * ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL> select abs(-27); select abs(-27)               * ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL> select acos(.9); select acos(.9)               * ERROR at line 1: ORA-00923: FROM keyword not found where expected Here, we get ORA-00923 error message which indicates that we can't run queries without FROM cluase in older releases. Let's try in Oracle 23ai.

Let's query the the DUAL table in Oracle 23ai database. SQL> select sysdate; SYSDATE --------- 10-NOV-24 SQL> select abs(-27);   ABS(-27) ----------         27 SQL> select acos(.9);   ACOS(.9) ---------- .451026812 SQL> select asciistr('ORACLE'); ASCIIS ------ ORACLE SQL> col SESSIONTIMEZONE for a18 SQL> col CURRENT_TIMESTAMP for a35 SQL> select sessiontimezone, current_date; SESSIONTIMEZONE    CURRENT_D ------------------ --------- +05:30             10-NOV-24 SQL> select sessiontimezone, current_timestamp; SESSIONTIMEZONE    CURRENT_TIMESTAMP ------------------ ----------------------------------- +05:30             10-NOV-24 01.08.00.239000 AM +05:30 Here, you can see the above output. It is possible in Oracle 23ai to query the DUAL table without FROM cluase.

Visit "Oracle Database 23ai Free - All in one page" to access all blog posts on Oracle 23ai in one page.

Thanks for reading this post ! Please comment if you like this post ! Click FOLLOW to get future blog updates !