I am keen to see what is new in SQL in 23c that could be adopted quickly, so I got myself an Oracle 23c Developer Release Database and what follows are some things I’ve experimented with in 2024.
There is a really good PDF here which links to the main documentation here and there’s a jam packed site here of a compendium of new features.
1. Group By Alias
Jan 1st 2024.
“You can now use column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally, the HAVING clause supports column aliases.”
I have to say I’ve never been a big fan of anything “positional” as it only takes someone to change your code to add in a another column in the select statement and not look at any of the “order by 1,2,3” or whatever you had there for the code to still compile, run and produce different results to what you originally intended. However, being able to use the ALIAS in a GROUP BY is very much something I have found myself wanting to be able to do very often. So the coder in me is quite excited to see this if it means we can use the alias and not have to put the “longhand” in the group by clause. Let’s give this a try by creating a simple test case :
SELECT
to_char(hire_date, 'YYYY') myyear,
COUNT(*) count_employees
FROM
employees emp
GROUP BY
myyear
ORDER BY
myyear
The results are :
Brilliant. So that does mean that in 23c we can refactor existing code to make use of this quite neat new feature.
2. Boolean Data Types in SQL
Jan 2nd 2024
The documentation tells us that the new data type stores true, false and, where it can be null, “unknown”. We’ve had the Boolean data type in PL/SQL for as long as I can remember, so having the database finally have a Boolean implemention (rather than a varchar2(1) with “Y” or “N”) is great.
Actually, looking at the documentation it appears that there are a load of synonyms too, so inserting “Y” or 1 or “on”, etc evaluate to true and “N”, 0, “off”, etc evaluate to false. Let’s give it a go by creating a simple table and populating it.
Create table guitars_I_own
(guitar_name Varchar2(30) not null,
owned_by_me Boolean
);
Firstly we will crearte 3 records for Guitars that I do not own. I will do one with a ‘no’, one as FALSE and one as ‘off’ and according to the documentation all three should be set to FALSE.Insert into Guitars_I_Own
(guitar_name,owned_by_me)
values ('Fender 1962 Strat','no');
Insert into Guitars_I_Own
(guitar_name,owned_by_me)
values ('Gibson 1959 Les Paul',FALSE);
Insert into Guitars_I_Own
(guitar_name,owned_by_me)
values ('Fender Broadcaster 1952','off');
Next I will insert 3 records for guitars that I do actually own in a similar way.
Insert into Guitars_I_Own
(guitar_name,owned_by_me)
values ('Gibson Flying V','YES');
Insert into Guitars_I_Own
(guitar_name,owned_by_me)
values ('Fender 60th Anniversary Strat',TRUE);
Insert into Guitars_I_Own
(guitar_name,owned_by_me)
values ('Gibson Explorer','ON');
Let’s query them back – Firstly in my SQL Developer 23.1 I have on my laptop desktop.
Next in SQL Plus in my 23.3 Instance.
The difference is due to the drivers in the older version of SQL Developer that doesn’t understand the Boolean data type – so if you see 0 and 1 it is due to that.
Now we have the data in the database in a Boolean format, we can do all the things we’d love to do with Boolean sensitive queries that aid readability, such as show me all the guitars that I own
Now, what about the ones that I do not?
Certainly a great addition and an opportunity for us to refactor older code to take advantage of this new data type.
3. IF [NOT] EXISTS Syntax
5th Jan 2024
From the documentation we see that DDL object creation, modification, and deletion now support the IF EXISTS and IF NOT EXISTS syntax modifiers. This enables you to control whether an error should be raised if a given object exists or does not exist.
This looks simple to use and would be great for SQL scripts which check for the existence of objects before creating them or supressing errors if they already do, so let’s give it a go. I do not have a table called “cheese”, so let’s see what happens when I try to drop it “normally”.
As expected, so let’s see what happens if we use the IF EXISTS syntax which will only issue the DDL of the object actually exists.
Now we no longer get an error. We do get a “table dropped” message – which is intentional behaviour as the error is supporessed, although I think I would have preferred a “no table to drop”.
The documentation is concise and gives some guidelines on usage, such as you can’t mix and match with “create or replace”, typically used for objects like views.
4. Select without a From Clause
Jan 11th 2024
Now we can write Select expressions without the need for a “from” clause. I think this is possibly for transportability between databases, but it means that we no longer need to use the “dual” table to complete the select syntax as a “dummy” from table – just becuase we know it will return 1 row.
That means that if I need to see what the day was 5 days ago, I can simply do this:
select sysdate-5
…and that is it. No need for a “from dual”.
We csn also select values from sequences, perform calculations, etc.
There must be 40 years of code out there all selecting from dual (so I still have to inform people new to Oracle about dual) but from the point we don’t need to backport our product code to 19c as well as 23c databases, then I’ll be adopting the more streamlined approach.