Now that Oracle DB 23ai has been GA for a while I figure it is time to dig into it and talk about the features för DBA and developers that I find most interesting to talk about. Yes, you’ve read about some of them as info has seeped out during the beta-test and and pre-GA. I however think most people are only looking at it when it is GA as that is when they start looking at when and how to upgrade.

First off a feature that gets a lot of press and I personally feel is a lot ado about nothing. If I can quote William Shakespeare in a technical post I of course will. 🙂

The documentation explains it as “Use the optional FROM clause to specify the objects from which data is selected.“. While it is 100% correct it does not tell you when you would skip it as it is mandatory for about 99.99999% of all queries.

What is optional is pretty much just one situation, being when you do a select and your FROM-clause is “from dual”. Then you can skip it. That in turn means when you want to return data from an expression and not from a table, that is the case when it is no longer mandatory.

One example of this would be:

select trunc(sysdate, 'mm') + 5 + 9/24 from dual;

That is how we have had to write it pre 23ai. It of course results in a datetime five days after the beginning of the month (i e the 6th of the month) and the time set to 9:00 / 9am.

In 23ai the same could be shortened to:

select trunc(sysdate, 'mm') + 5 + 9/24;

Not too big of a difference but the extra characters are not adding any value either.

For some time I’m sure this will be SQLs version of the USB-A that can only fit two ways and you still end up trying three times before it works. You write it with “from dual” remembering you don’t need it so you erase it only to realize you are in a 19c database so you write it again.

If you felt you needed this before for your adhoc SQL, you’d probably have an alias that makes it even shorter to use.

alias s=declare
  a varchar2(20);
begin
  execute immediate 'select ' || :1 || ' from dual' into a;
  dbms_output.put_line(a);
end;
/

SQL> s trunc(sysdate,'mm')+5+9/24
2024-06-06 09:00:00

SQL> s 30+12
42

Why write all of select if yoiu don’t wnat to write all of “from dual”? 🙂