SELECT Without FROM Clause in Oracle Database 23c

From Oracle 23c onward we can use a SELECT without a FROM clause in some circumstances.

◉ SELECT Without FROM Clause

From Oracle 23c onward, rather than selecting expressions from the DUAL table, we can just omit the FROM clause completely. So these are functionally identical.

select sysdate;

SYSDATE

---------

04-APR-23

SQL>

select sysdate from dual;

SYSDATE

---------

04-APR-23

SQL>

◉ PL/SQL Support

The omission of the FROM clause is also valid in PL/SQL.

set serveroutput on

declare

  v_date date;

begin

  select sysdate

  into v_date;

  dbms_output.put_line(v_date);

end;

/

04-APR-23

PL/SQL procedure successfully completed.

SQL>

Normally we would expect to achieve the above with a direct assignment, not a SELECT ... INTO, but it wouldn't illustrate the point.

◉ Implicit Statement Results

In other database engines we often see this type of syntax used for passing results out of procedures, so we might expect this to be possible, but unfortunately it's not.

create or replace procedure get_date as

begin

  select sysdate;

end;

/

Warning: Procedure created with compilation errors.

SQL>show errors

Errors for PROCEDURE GET_DATE:

LINE/COL ERROR

-------- -----------------------------------------------------------------

3/3      PLS-00428: an INTO clause is expected in this SELECT statement

SQL>

We can of course replicate the expected functionality using implicit statement results, introduced in Oracle 12.1.

create or replace procedure get_date as

  l_cursor sys_refcursor;

begin

  open l_cursor for

    select sysdate;

  dbms_sql.return_result(l_cursor);

end;

/

exec get_date;

PL/SQL procedure successfully completed.

ResultSet #1

SYSDATE

---------

04-APR-23

SQL>

That allows us to mimic what we see in other database engines, but it's not as simple as we might have wanted.

◉ Query Transformation

Let's see what happens behind the scenes when we use this new syntax.

First we flush the shared pool and identify the trace file that will be created for our new session.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

alter system flush shared_pool;

conn testuser1/testuser1@//localhost:1521/freepdb1

set linesize 100

column value format a65

select value

from   v$diag_info

where  name = 'Default Trace File';

VALUE

-----------------------------------------------------------------

/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_17498.trc

SQL>

Now we do a 10053 trace of the statement.

alter session set events '10053 trace name context forever';

select sysdate; 

alter session set events '10053 trace name context off';

We check the resulting trace file, searching for the section beginning with "Final query after transformations", and we see the following statement.

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT SYSDATE@! "SYSDATE" FROM "SYS"."DUAL" "DUAL"

The statement has been transformed to include FROM DUAL, so this is syntax candy. The feature lets us type less, but the query we are used to runs on the server.

Source: oracle-base.com