DBA Hub

📋Steps in this guide1/4

SELECT Without FROM Clause in Oracle Database 23ai/26ai

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

oracle 23configurationintermediate
by OracleDba
15 views
1

SELECT Without FROM Clause

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select sysdate;

SYSDATE
---------
04-APR-23

SQL>


select sysdate from dual;

SYSDATE
---------
04-APR-23

SQL>
2

PL/SQL Support

The omission of the clause is also valid in PL/SQL. Normally we would expect to achieve the above with a direct assignment, not a , but it wouldn't illustrate the point.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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>
3

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. We can of course replicate the expected functionality using implicit statement results, introduced in Oracle 12.1. That allows us to mimic what we see in other database engines, but it's not as simple as we might have wanted.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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>

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>
4

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. Now we do a 10053 trace of the statement. We check the resulting trace file, searching for the section beginning with "Final query after transformations", and we see the following statement. The statement has been transformed to include , so this is syntactic sugar . 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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>

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

select sysdate; 

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

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT SYSDATE@! "SYSDATE" FROM "SYS"."DUAL" "DUAL"

Comments (0)

Please to add comments

No comments yet. Be the first to comment!