For more information on what’s new in Oracle Database 23.8, see: What’s new for Developers in 23.8
Oracle Database 23.9 has been my favorite RU so far. The features introduced in 23.9 are some of those that we had long had on our list, but simply took a while to get to and to get right. The Non-Positional INSERT INTO syntax changes a fundamental core construct of SQL that, frankly, should have been there since SQL’s inception. Together with the highly popular GROUP BY ALL and support for UUIDv4, the DBMS_DEVELOPER.GET_METADATA() package method and materialized columns in 23.7, the Sessionless Transactions in 23.6, and the many improvements in the 23ai GA version put Oracle Database on the right track to deliver powerful yet simple features for the everyday DBA, developer, and any other kind of user.
Table of Contents
- GROUP BY ALL
- Non-Positional INSERT INTO syntax
- EXECUTE ON JAVASCRIPT Privilege Not Required For User-Owned JavaScript
- JavaScript Compile-Time Syntax Checking
- UUID version 4 support
GROUP BY ALL
A popular and long-sought-after feature is the addition of GROUP BY ALL, which automatically groups by all non-aggregated columns present in the SELECT list, hence no longer requiring the user to specify these columns multiple times. Instead of writing the traditional query:
SELECT re.name, co.country_id, co.country_code, co.name, COUNT(*) AS cnt
FROM currencies cu
INNER JOIN currencies_countries cc ON (cu.currency_id=cc.currency_id)
INNER JOIN countries co ON (cc.country_id=co.country_id)
INNER JOIN regions re ON (co.region_id=re.region_id)
GROUP BY re.name, co.country_id, co.country_code, co.name
ORDER BY cnt DESC, co.name;
NAME COU CO NAME CNT
-------------------- --- -- -------------------- ----------
Asia BTN BT Bhutan 2
North America CUB CU Cuba 2
North America SLV SV El Salvador 2
North America HTI HT Haiti 2
Europe XKX XK Kosovo 2
Africa LSO LS Lesotho 2
Africa NAM NA Namibia 2
Asia AFG AF Afghanistan 1
Europe ALB AL Albania 1
Africa DZA DZ Algeria 1
Europe AND AD Andorra 1
Africa AGO AO Angola 1
North America ATG AG Antigua and Barbuda 1
South America ARG AR Argentina 1
Asia ARM AM Armenia 1
Oceania AUS AU Australia 1
Europe AUT AT Austria 1
Asia AZE AZ Azerbaijan 1
...
Users can now spare themselves from having to type the same column list again, and instead use GROUP BY ALL:
SELECT re.name, co.country_id, co.country_code, co.name, COUNT(*) AS cnt
FROM currencies cu
INNER JOIN currencies_countries cc ON (cu.currency_id=cc.currency_id)
INNER JOIN countries co ON (cc.country_id=co.country_id)
INNER JOIN regions re ON (co.region_id=re.region_id)
GROUP BY ALL
ORDER BY cnt DESC, co.name;
NAME COU CO NAME CNT
-------------------- --- -- -------------------- ----------
Asia BTN BT Bhutan 2
North America CUB CU Cuba 2
North America SLV SV El Salvador 2
North America HTI HT Haiti 2
Europe XKX XK Kosovo 2
Africa LSO LS Lesotho 2
Africa NAM NA Namibia 2
Asia AFG AF Afghanistan 1
Europe ALB AL Albania 1
Africa DZA DZ Algeria 1
Europe AND AD Andorra 1
Africa AGO AO Angola 1
North America ATG AG Antigua and Barbuda 1
South America ARG AR Argentina 1
Asia ARM AM Armenia 1
Oceania AUS AU Australia 1
Europe AUT AT Austria 1
Asia AZE AZ Azerbaijan 1
...
GROUP BY ALL will take “all” non-aggregated columns in the SELECT list (everything but COUNT(*) in the example above) and automatically group by them.
Watch out for another, more detailed blog post on GROUP BY ALL from me soon. Having been directly involved in this feature, I will share some backstory and reasons for when and when not to use it.
Non-Positional INSERT INTO syntax
Another powerful feature is the non-positional INSERT INTO syntax. This feature enables users to:
- Use the same
SETclause that theUPDATEstatement has - Match columns in
INSERT INTO ... SELECTvia their names - Condense non-uniform multi-row INSERT statements
- Group logical entities
- Provide a rigorous column list
Consider the following statement that inserts a row into a parks table:
INSERT INTO parks
(park_id, park_code, name, full_name, url, description, designation, latitude,
longitude, states, directions_info, directions_url, weather_info, country_id)
VALUES
('77E0D7F0-1942-494A-ACE2-9004D2BDC59E', 'abli', 'Abraham Lincoln Birthplace',
'Abraham Lincoln Birthplace National Historical Park',
'https://www.nps.gov/abli/index.htm', 'For over a century people from around the
world have come to rural Central Kentucky to honor the humble beginnings of our
16th president, Abraham Lincoln. His early life on Kentucky''s frontier shaped
his character and prepared him to lead the nation through Civil War. Visit our
country''s first memorial to Lincoln, built with donations from young and old,
and the site of his childhood home.', 'National Historical Park', 37.5858662, -
85.67330523, 'KY', 'The Birthplace Unit of the park is located approximately 2
miles south of the town of Hodgenville on U.S. Highway 31E South. The Boyhood
Home Unit at Knob Creek is located approximately 10 miles northeast of the
Birthplace Unit of the park.',
'http://www.nps.gov/abli/planyourvisit/directions.htm', 'There are four distinct
seasons in Central Kentucky. However, temperature and weather conditions can vary
widely within those seasons. Spring and Fall are generally pleasant with frequent
rain showers. Summer is usually hot and humid. Winter is moderately cold with
mixed precipitation.', 'USA');
Due to the number of columns and the length of their values, this statement has drastically declined in readability and maintainability. Specifically, it is unclear which value corresponds to which column. Now, consider the same statement with the SET clause:
INSERT INTO parks
SET
park_id = '77E0D7F0-1942-494A-ACE2-9004D2BDC59E',
park_code = 'abli',
name = 'Abraham Lincoln Birthplace',
full_name = 'Abraham Lincoln Birthplace National Historical Park',
url = 'https://www.nps.gov/abli/index.htm'
description = 'For over a century people from around the world have come to rural Central Kentucky to honor the humble beginnings of our 16th president, Abraham Lincoln. His early life on Kentucky''s frontier shaped his character and prepared him to lead the nation through Civil War. Visit our country''s first memorial to Lincoln, built with donations from young and old, and the site of his childhood home.',
designation = 'National Historical Park',
latitude = 37.5858662,
longitude = -85.67330523,
states = 'KY',
directions_info = 'The Birthplace Unit of the park is located approximately 2 miles south of the town of Hodgenville on U.S. Highway 31E South. The Boyhood Home Unit at Knob Creek is located approximately 10 miles northeast of the Birthplace Unit of the park.',
directions_url = 'http://www.nps.gov/abli/planyourvisit/directions.htm',
weather_info = 'There are four distinct seasons in Central Kentucky. However, temperature and weather conditions can vary widely within those seasons. Spring and Fall are generally pleasant with frequent rain showers. Summer is usually hot and humid. Winter is moderately cold with mixed precipitation.',
country_id = 'USA';
The SET clause adds tremendous value to the statement’s readability and maintainability, making it immediately obvious which value belongs to which column, and easy to add or remove columns.
Watch out for another, much more detailed blog post on the non-positional INSERT INTO syntax from me soon. Having been directly involved in this feature, I will share the backstory, the reasoning behind it, when and how to use it, and why you will rarely want to go back to the traditional positioned INSERT INTO syntax.
EXECUTE ON JAVASCRIPT Privilege Not Required For User-Owned JavaScript
Before 23.9, for a user to create a JavaScript stored function or procedure in its schema, the EXECUTE ON JAVASCRIPT privilege was required. Even though the function belonged to the user, without that privilege the user would get a PLS-00904: insufficient privilege to access object JAVASCRIPT error, for example:
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL 🚀 > conn gerald@localhost/freepdb1
Password? (**********?) *********
Connected.
SQL 🚀 > CREATE OR REPLACE FUNCTION NOW
RETURN TIMESTAMP
AS MLE LANGUAGE JAVASCRIPT
{{
return new Date();
}};
/
Function NOW compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
3/17 PLS-00904: insufficient privilege to access object JAVASCRIPT
To remedy that, the GRANT EXECUTE ON JAVASCRIPT TO <user>; command was necessary:
SQL 🚀 > conn system@localhost/freepdb1
Password? (**********?) *********
Connected.
SQL 🚀 > GRANT EXECUTE ON JAVASCRIPT TO gerald;
Grant succeeded.
SQL 🚀 > conn gerald@localhost/freepdb1
Password? (**********?) *********
Connected.
SQL 🚀 > CREATE OR REPLACE FUNCTION NOW
RETURN TIMESTAMP
AS MLE LANGUAGE JAVASCRIPT
{{
return new Date();
}};
/
Function NOW compiled
SQL 🚀 >
Starting with 23.9, this privilege is no longer required to execute JavaScript in the user’s schema:
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.9.0.25.09
SQL 🚀 > conn gerald@localhost/freepdb1
Password? (**********?) *********
Connected.
SQL 🚀 > CREATE OR REPLACE FUNCTION NOW
RETURN TIMESTAMP
AS MLE LANGUAGE JAVASCRIPT
{{
return new Date();
}};
/
Function NOW compiled
SQL 🚀 >
For more information, see Changes in This Release for the JavaScript Developer’s Guide.
JavaScript Compile-Time Syntax Checking
JavaScript stored procedures and functions will now be syntax checked at compile time. That means that when you create your JavaScript procedure or function, you will immediately see whether your code has valid syntax. Because JavaScript is a dynamically typed language, it is typically only evaluated when the program is executed. You may have noticed that behavior in your browser, when you open a page and you get a JavaScript error and you wonder why that error shows up now and hasn’t been captured by the developer. Starting with 23.9, if your JavaScript code includes syntax errors, the function or procedure will be created, but it will exist in an invalid state.
Note: While syntax errors will be caught before runtime, it is still recommended to use a linting tool of your choice to perform analysis of your code before executing the inline call specification.
Before 23.9, this function compiled successfully:
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL 🚀 > CREATE OR REPLACE FUNCTION NOW
RETURN TIMESTAMP
AS MLE LANGUAGE JAVASCRIPT
{{
return ne Date();
}};
/
Function NOW compiled
SQL 🚀 >
Yet when the function is invoked, a syntax error is encountered (there is a missing w for the new keyword):
SQL 🚀 > select now();
Error at Command Line : 1 Column : 12
Error report -
SQL Error: ORA-04160: SyntaxError: <inline-src-js>:2:12 Expected ; but found Date
return ne Date();
^
https://docs.oracle.com/error-help/db/ora-04160/04160. 00000 - "%s"
Starting with 23.9, the syntax is checked at compile time:
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.9.0.25.07
SQL 🚀 > CREATE OR REPLACE FUNCTION NOW
RETURN TIMESTAMP
AS MLE LANGUAGE JAVASCRIPT
{{
return ne Date();
}};
/
Function created with compilation errors.
SQL 🚀 > show errors;
LINE/COL ERROR
--------- -------------------------------------------------------------
0/0 SyntaxError: NOW:2:12 Expected ; but found Date return ne Date(); ^
Errors: check compiler log
SQL 🚀 >
UUID version 4 support
Oracle Database can now generate and convert Universally Unique IDentifiers (UUIDs) of Version 4 using the new UUID() function. Oracle Database, for the longest time (9i), supported the generation of UUID Version 3 via the SYS_GUID() function. However, over the years, Version 3 has fallen more and more out of favor as it draws its generation sequences from names within a unique namespace. For SYS_GUID() it is a combination of a host identifier and a process or thread identifier. While a UUIDv3 will still contain some random, unique bytes to always generate a different value, the randomness of the resulting UUID is not very high. This is easily demonstrated by the following query:
SQL 🚀 > SELECT SYS_GUID() CONNECT BY level <= 10;
SYS_GUID()
___________________________________
3A3D88FCBC4A5471E0634901000A4700
3A3D88FCBC4B5471E0634901000A4700
3A3D88FCBC4C5471E0634901000A4700
3A3D88FCBC4D5471E0634901000A4700
3A3D88FCBC4E5471E0634901000A4700
3A3D88FCBC4F5471E0634901000A4700
3A3D88FCBC505471E0634901000A4700
3A3D88FCBC515471E0634901000A4700
3A3D88FCBC525471E0634901000A4700
3A3D88FCBC535471E0634901000A4700
10 rows selected.
At first, these UUIDs all look the same, but two bits are variable, making them unique:
3A3D88FCBC4A5471E0634901000A4700
3A3D88FCBC4B5471E0634901000A4700
3A3D88FCBC4C5471E0634901000A4700
3A3D88FCBC4D5471E0634901000A4700
3A3D88FCBC4E5471E0634901000A4700
3A3D88FCBC4F5471E0634901000A4700
3A3D88FCBC505471E0634901000A4700
3A3D88FCBC515471E0634901000A4700
3A3D88FCBC525471E0634901000A4700
3A3D88FCBC535471E0634901000A4700UUIDv4 UUIDs, on the other hand, are truly random:
SQL 🚀 > SELECT UUID() CONNECT BY level <= 10;
UUID()
___________________________________
BAFC93BF3B664FF7BF7ADF00DB26F15C
23F1793C4C364F11BF30FC286AB74DAA
FF3BC4CB7DC34F3FBFBF612C8A55328D
7FFF7F38B41F4FAEBF7751C1DD298B3F
E36B6760FC084F9ABF29EDE2167CA26D
5602CCA4D5DE4F44BF2040FBA6521141
A5D8EF7DBD414F9DBFE40B413BFB51E4
43A86A908AB84F54BF712F484F2C53A8
D8EE047FF4CD4FEBBFC6F3E1E9E3609C
F3C1F9A3A42B4FE4BFF61BDBB9B5E26A
10 rows selected.
There are several benefits to having this randomness of the entire UUID, and hence UUIDv4 has become the de facto standard in many programming languages, such as:
UUIDs are 16-byte binary values. Hence, Oracle Database returns them as RAW(16) as the RAW data type is for representing “raw” binary data. However, often you may want to see the character equivalent of the hexadecimal displayed bytes. For that, you can use the RAW_TO_UUID() function:
SQL 🚀 > SELECT RAW_TO_UUID(UUID());
RAW_TO_UUID(UUID())
_______________________________________
6cef7511-c0ac-4f1a-bfc0-9319c0f5a7c7
If you prefer uppercase, use UPPER() around it:
SQL 🚀 > SELECT UPPER(RAW_TO_UUID(UUID()));
UPPER(RAW_TO_UUID(UUID()))
_______________________________________
06B73DA9-7865-4FDB-BF3E-162F13B83705
More likely, however, you will want to convert a text representation of a UUID back to bytes. Text representations are widely used, and it’s not uncommon to find them as some form of ID in a JSON document or other external data source or payload. To convert them back to bytes, you can use the UUID_TO_RAW() function. Note, text representations of UUIDs are valid in upper or lowercase and can, but do not have to, contain -, meaning all four of these are the same UUID:
06B73DA9-7865-4FDB-BF3E-162F13B8370506b73da9-7865-4fdb-bf3e-162f13b8370506B73DA978654FDBBF3E162F13B8370506b73da978654fdbbf3e162f13b83705
Even mixed cases are fine, like 06b73da9-7865-4FDB-bf3e-162f13b83705UUID_TO_RAW() will convert all of them back to the same bytes:
SQL 🚀 > SELECT * FROM (
VALUES
(UUID_TO_RAW('06b73da9-7865-4fdb-bf3e-162f13b83705')),
(UUID_TO_RAW('06B73DA9-7865-4FDB-BF3E-162F13B83705')),
(UUID_TO_RAW('06b73da978654fdbbf3e162f13b83705')),
(UUID_TO_RAW('06B73DA978654FDBBF3E162F13B83705')),
(UUID_TO_RAW('06b73da9-7865-4FDB-bf3e-162f13b83705'))
) AS examples(uuid);
UUID
___________________________________
06B73DA978654FDBBF3E162F13B83705
06B73DA978654FDBBF3E162F13B83705
06B73DA978654FDBBF3E162F13B83705
06B73DA978654FDBBF3E162F13B83705
06B73DA978654FDBBF3E162F13B83705
UUIDv4 was yet another project I was directly involved with, so watch out for another blog post on a more detailed explanation of why RAW, how and when to use them and whether we are already finished there or not.
GROUP BY ALL is for lazy developers and leads to bad code 😉
Everything has its place and when one uses something outside of what it is intended for then there is only one to blame. 🙂
I wonder why “group by all” could not just be the default, if group by clause is omitted. Basically group by all is what we do most of the time.
You should definitely watch out for my follow-on post on GROUP BY ALL where I will explain why we (and others) decided not to do that and don’t recommend anyone doing it.