Is the FROM Clause Really Optional?
Did you know that starting with Oracle 23c, the FROM clause is optional? Not totally optional, so let me explain.
In a SELECT statement, that retrieves data from an actual table or tables, the FROM clause is still mandatory. This makes sense, since Oracle database cannot read your mind to figure out which table you want to select from. However, if you want to select a character string or today’s date, or the database environment you are connected to, you no longer need to specify the
FROM clause, it is optional. Oracle will fill in the “blanks” with “FROM dual”, behind the scene.
Anything where you previously wanted to select something “FROM DUAL” can be left out now in your select statement. This is very cool, as it makes the code easier to read and shorter.
Here is a basic example on how to use this:
select sysdate;
SYSDATE
---------
14-DEC-23
In pre-23c versions you would have written this as:
select sysdate from dual;
SYSDATE
---------
14-DEC-23
And the original syntax with the FROM clause, still works in 23c.
Other uses of this
new feature would be in scripts. Usually at the beginning of my scripts I like to read into parameters the environment I am connected to. I would use the sys_context function to return various things: ie. db name, or host, with the purpose of populating some parameters. Here are some examples how you could use this feature now:
