8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

SELECT Without FROM Clause in Oracle Database 23ai

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

SELECT Without FROM Clause
PL/SQL Support
Implicit Statement Results
Query Transformation

SELECT Without FROM Clause

From Oracle 23ai 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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.