For more information on what’s new in Oracle AI Database 23.26.0, see: What’s new for Developers in Oracle AI Database 23.26.0
This release introduces new SQL constructs, new functions, and extensions to existing functionality, making analytic SQL cleaner, data integrity even better, and SQL syntax easier. What follows are my favorite features of this release.
Table of Contents
- Aggregation Filters
- Calendar Functions
- JavaScript Web API
- Partition by Expression
- Simplified Dot Notation Syntax for JSON Index Creation
- DATEDIFF Function
- Assertions
Aggregation Filters
Aggregation filters allow developers to restrict the rows considered in aggregate functions, such as SUM, COUNT, and AVG. The FILTER clause, which lets developers attach a WHERE clause like predicate, has already been available in the SQL standard and is now also available in Oracle AI Database. Previously, the same functionality required either CASE statements or subqueries.
Let’s take a look at a practical example. Consider a retail operations team that wants to see the status of all order fulfilment states in a report. The team wants a single summary row per product category that shows the number of each order fulfilment state alongside the total number of orders altogether. The fulfilment states are a domain enum (also introduced in 23ai): SHIPPED, PENDING, CANCELED. Before the FILTER clause, a developer would have to resort to a CASE inside the aggregate function, or worse, write three separate subqueries joined together:
Setup
-- Create the enum domain for order status
CREATE DOMAIN order_status_d AS ENUM (shipped = 'SHIPPED', pending = 'PENDING', canceled = 'CANCELED');
-- Create the category table
CREATE TABLE categories (
category_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR2(50) NOT NULL UNIQUE
);
-- Create the orders table with a foreign key to categories
CREATE TABLE orders (
order_id NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
customer_id NUMBER NOT NULL,
category_id NUMBER NOT NULL,
status order_status_d NOT NULL,
order_date DATE NOT NULL,
amount NUMBER(10, 2) NOT NULL,
CONSTRAINT fk_orders_category FOREIGN KEY (category_id) REFERENCES categories (category_id)
);
-- Insert categories using table-value constructor
INSERT INTO categories (name) VALUES
('Electronics'),
('Home and Garden'),
('Clothing'),
('Sports and Outdoors');
-- Insert orders for each category
INSERT INTO orders (customer_id, category_id, status, order_date, amount) VALUES
(101, 1, order_status_d.SHIPPED, TO_DATE('2025-01-03', 'YYYY-MM-DD'), 199.99),
(102, 1, order_status_d.SHIPPED, TO_DATE('2025-01-07', 'YYYY-MM-DD'), 449.00),
(103, 1, order_status_d.SHIPPED, TO_DATE('2025-01-15', 'YYYY-MM-DD'), 89.95),
(104, 1, order_status_d.PENDING, TO_DATE('2025-02-01', 'YYYY-MM-DD'), 329.00),
(105, 1, order_status_d.PENDING, TO_DATE('2025-02-14', 'YYYY-MM-DD'), 799.99),
(106, 1, order_status_d.CANCELED, TO_DATE('2025-02-20', 'YYYY-MM-DD'), 149.50),
(107, 1, order_status_d.SHIPPED, TO_DATE('2025-03-02', 'YYYY-MM-DD'), 259.00),
(108, 1, order_status_d.CANCELED, TO_DATE('2025-03-10', 'YYYY-MM-DD'), 59.99),
(201, 2, order_status_d.SHIPPED, TO_DATE('2025-01-05', 'YYYY-MM-DD'), 34.99),
(202, 2, order_status_d.SHIPPED, TO_DATE('2025-01-18', 'YYYY-MM-DD'), 119.00),
(203, 2, order_status_d.SHIPPED, TO_DATE('2025-02-03', 'YYYY-MM-DD'), 79.50),
(204, 2, order_status_d.PENDING, TO_DATE('2025-02-22', 'YYYY-MM-DD'), 249.99),
(205, 2, order_status_d.PENDING, TO_DATE('2025-03-01', 'YYYY-MM-DD'), 44.00),
(206, 2, order_status_d.CANCELED, TO_DATE('2025-03-08', 'YYYY-MM-DD'), 89.95),
(207, 2, order_status_d.SHIPPED, TO_DATE('2025-03-15', 'YYYY-MM-DD'), 29.00),
(208, 2, order_status_d.CANCELED, TO_DATE('2025-03-20', 'YYYY-MM-DD'), 55.00),
(301, 3, order_status_d.SHIPPED, TO_DATE('2025-01-02', 'YYYY-MM-DD'), 49.99),
(302, 3, order_status_d.SHIPPED, TO_DATE('2025-01-11', 'YYYY-MM-DD'), 89.00),
(303, 3, order_status_d.SHIPPED, TO_DATE('2025-01-25', 'YYYY-MM-DD'), 29.95),
(304, 3, order_status_d.PENDING, TO_DATE('2025-02-04', 'YYYY-MM-DD'), 139.00),
(305, 3, order_status_d.PENDING, TO_DATE('2025-02-17', 'YYYY-MM-DD'), 59.99),
(306, 3, order_status_d.CANCELED, TO_DATE('2025-02-28', 'YYYY-MM-DD'), 79.50),
(307, 3, order_status_d.SHIPPED, TO_DATE('2025-03-03', 'YYYY-MM-DD'), 109.00),
(308, 3, order_status_d.CANCELED, TO_DATE('2025-03-14', 'YYYY-MM-DD'), 34.00),
(309, 3, order_status_d.SHIPPED, TO_DATE('2025-03-21', 'YYYY-MM-DD'), 64.99),
(310, 3, order_status_d.CANCELED, TO_DATE('2025-03-28', 'YYYY-MM-DD'), 19.99),
(401, 4, order_status_d.SHIPPED, TO_DATE('2025-01-08', 'YYYY-MM-DD'), 149.99),
(402, 4, order_status_d.SHIPPED, TO_DATE('2025-01-19', 'YYYY-MM-DD'), 39.95),
(403, 4, order_status_d.SHIPPED, TO_DATE('2025-02-06', 'YYYY-MM-DD'), 299.00),
(404, 4, order_status_d.PENDING, TO_DATE('2025-02-25', 'YYYY-MM-DD'), 89.99),
(405, 4, order_status_d.PENDING, TO_DATE('2025-03-05', 'YYYY-MM-DD'), 199.00),
(406, 4, order_status_d.CANCELED, TO_DATE('2025-03-12', 'YYYY-MM-DD'), 74.50),
(407, 4, order_status_d.SHIPPED, TO_DATE('2025-03-19', 'YYYY-MM-DD'), 54.99);
COMMIT;
Before
SQL> -- Query that uses the 'CASE' approach to apply an input filter to an aggregation function
SQL> SELECT c.name,
COUNT(CASE WHEN o.status = order_status_d.SHIPPED THEN 1 END) AS shipped,
COUNT(CASE WHEN o.status = order_status_d.PENDING THEN 1 END) AS pending,
COUNT(CASE WHEN o.status = order_status_d.CANCELED THEN 1 END) AS canceled,
COUNT(*) AS total
FROM orders o
JOIN categories c ON (o.category_id=c.category_id)
GROUP BY c.name
ORDER BY c.name;
NAME SHIPPED PENDING CANCELED TOTAL
______________________ __________ __________ ___________ ________
Clothing 5 2 3 10
Electronics 4 2 2 8
Home and Garden 4 2 2 8
Sports and Outdoors 4 2 1 7
Using the FILTER clause, there is no more need for the THEN 1 END part. Instead, an input filter can be expressed in the very same style that a filter can be expressed on a table, i.e., an ordinary WHERE clause. But the FILTER clause comes with the added benefit that the intent is clearly documented (no more guessing “why THEN 1“, “why no ELSE“, etc.):
After
SQL> -- Query that leverages the FILTER clause to apply an input filter to an aggregate function
SQL> SELECT c.name,
COUNT(*) FILTER (WHERE o.status = order_status_d.SHIPPED) AS shipped,
COUNT(*) FILTER (WHERE o.status = order_status_d.PENDING) AS pending,
COUNT(*) FILTER (WHERE o.status = order_status_d.CANCELED) AS canceled,
COUNT(*) AS total
FROM orders o
JOIN categories c ON (o.category_id=c.category_id)
GROUP BY c.name
ORDER BY c.name;
NAME SHIPPED PENDING CANCELED TOTAL
______________________ __________ __________ ___________ ________
Clothing 5 2 3 10
Electronics 4 2 2 8
Home and Garden 4 2 2 8
Sports and Outdoors 4 2 1 7
Calendar Functions
Calendar logic has always been one of those areas where things can get a bit more complex than one would have thought at first. Leap year, leap second, daylight savings, anyone? Add things like fiscal calendars to the mix, and things usually start to become interesting. Oh, and by the way, did you know that there is also such a thing as a retail calendar? Programming languages don’t really align with real-world dates. We have come up with Unix epoch and ISO standard to define calendars, yet anybody coming from a non-Western place will tell you that even these are based on calendars (Gregorian, usually) that do not apply to other parts of the world. So we default back to calendar-magic with creative arithmetics in the statement or user-defined PL/SQL functions to accommodate for the gap.
Oracle AI Database is the first database to try to alleviate some of these pains for the SQL programmer and data analyst. It now comes with a set of new calendar functions that are aware of calendar, fiscal, and retail time hierarchies. That can be a pretty big deal for anybody doing analytics, reporting, planning, or finance-oriented work.
Let’s look at an example of an online store whose finance team uses a fiscal year starting on June 1. Orders placed only a few days apart can belong to different fiscal years even though they sit in the same normal calendar year. That is exactly the kind of reporting logic that usually turns into custom expressions or a date dimension table:
Setup
SQL> -- Create demo table
SQL> CREATE TABLE sales (
order_id NUMBER PRIMARY KEY,
order_date DATE,
amount NUMBER
);
Table SALES created.
SQL> -- Insert some test data
SQL> INSERT into sales
(order_id, order_date, amount)
VALUES
( 1, DATE '2025-05-28', 1200),
( 2, DATE '2025-05-31', 800),
( 3, DATE '2025-06-01', 1500),
( 4, DATE '2025-06-03', 700);
4 rows inserted.
SQL> -- Commit transaction
SQL> commit;
Commit complete.
With a tiny dataset in place, let’s look at how the same orders roll up by regular calendar month. Note the new CALENDAR_MONTH() function:
Regular calendar month reporting:
SQL> -- Report sales per calendar month
SQL> SELECT CALENDAR_MONTH(order_date) AS cal_month,
SUM(amount) AS revenue
FROM sales
GROUP BY cal_month
ORDER BY 1;
CAL_MONTH REVENUE
____________ __________
JUN-2025 2200
MAY-2025 2000
Now let’s compare that with a fiscal calendar whose year starts on the 1st of June. Note the FISCAL_MONTH() function:
Fiscal calendar month reporting:
SQL> -- Report on fiscal calendar month
SQL> SELECT FISCAL_MONTH(order_date, DATE '2025-06-01') AS fiscal_month,
SUM(amount) AS revenue
FROM sales
GROUP BY fiscal_month
ORDER BY 1;
FISCAL_MONTH REVENUE
_______________ __________
JUN-FY2026 2200
MAY-FY2025 2000
That is where the feature becomes interesting. The June dates are not just “June 2025” anymore, but they have become part of FY2026. This is exactly the kind of thing finance teams care about and application code often gets wrong. To make that boundary behavior even clearer, here is the same data next to each other row by row:
Row-by-row comparison between regular and fiscal calendar
SQL> -- Row-by-row cmparison between calendar and fiscal
SQL> SELECT order_date,
CALENDAR_YEAR(order_date, 'YYYY') AS cal_year,
FISCAL_YEAR(order_date, DATE '2025-06-01') AS fiscal_year,
FISCAL_MONTH(order_date, DATE '2025-06-01') AS fiscal_month
FROM sales
ORDER BY order_date;
ORDER_DATE CAL_YEAR FISCAL_YEAR FISCAL_MONTH
_____________ ___________ ______________ _______________
28-MAY-25 2025 FY2025 MAY-FY2025
31-MAY-25 2025 FY2025 MAY-FY2025
01-JUN-25 2025 FY2026 JUN-FY2026
03-JUN-25 2025 FY2026 JUN-FY2026
Oracle AI Database also introduces new boundary helper functions, so that you can ask directly for the relevant period boundaries instead of deriving them yourself:
Calendar boundaries helper functions
SQL> -- Show calendar boundaries
SQL> SELECT order_date,
CALENDAR_QUARTER_START_DATE(order_date) AS calendar_qtr_start,
FISCAL_YEAR_END_DATE(order_date, DATE '2025-06-01') AS fiscal_year_end
FROM sales
ORDER BY order_date;
ORDER_DATE CALENDAR_QTR_START FISCAL_YEAR_END
_____________ _____________________ __________________
28-MAY-25 01-APR-25 31-MAY-25
31-MAY-25 01-APR-25 31-MAY-25
01-JUN-25 01-APR-25 31-MAY-26
03-JUN-25 01-APR-25 31-MAY-26
There are a lot of functions that have been introduced, and I urge you to check out the documentation.
JavaScript Web API
Oracle’s Multilingual Engine (MLE) has been steadily closing the gap between in-database JavaScript and the outside world experience. 23.26.1 takes a big step by exposing a subset of the standard Web API inside MLE: fetch(), URL, URLSearchParams, Headers, and Request/Response are now available under the mle-js-fetch import
As a quick example, let’s just gather my latest blog posts from my RSS feed, all from inside the database, of course:
ACL setup
SQL> -- Allow outbound https traffic
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'www.geraldonit.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http'),
principal_name => 'GERALD', -- change to the database user you use
principal_type => xs_acl.ptype_db
)
);
END;
/
PL/SQL procedure successfully completed.
JavaScript function
SQL> -- Create JS function using mle-js-fetch
SQL> CREATE OR REPLACE FUNCTION get_rss_feed (
p_url IN VARCHAR2,
p_max_items IN NUMBER
)
RETURN VARCHAR2
AS MLE LANGUAGE JAVASCRIPT
{{
await import ('mle-js-fetch');
const response = await fetch(P_URL);
if (!response.ok) return null;
const xml = await response.text();
const itemBlocks = xml.split(/<item[\s>]/);
itemBlocks.shift();
const results = [];
const limit = Math.min(Math.floor(P_MAX_ITEMS ?? 10), itemBlocks.length);
for (let i = 0; i < limit; i++) {
const block = itemBlocks[i];
const titleMatch = block.match(/<title>(?:<!\[CDATA\[(.*?)\]\]>|(.*?))<\/title>/s);
const linkMatch = block.match(/<link>(.*?)<\/link>/s);
results.push({
title: titleMatch ? (titleMatch[1] ?? titleMatch[2]) : null,
link: linkMatch ? linkMatch[1].trim() : null
});
}
return JSON.stringify(results);
}};
/
Function created.
SQL query
SQL> -- Query the last 5 RSS entries
SQL> SELECT get_rss_feed('https://www.geraldonit.com/feed/', 5);
GET_RSS_FEED('HTTPS://WWW.GERALDONIT.COM/FEED/',5)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"title":"Reading List – March 2026 #3","link":"https://www.geraldonit.com/reading-list-march-2026-3-2/"},{"title":"Reading List – March 2026 #3","link":"https://www.geraldonit.com/reading-list-march-2026-3/"},
{"title":"Reading List – March 2026 #2","link":"https://www.geraldonit.com/reading-list-march-2026-2/"},{"title":"Reading List – March 2026 #1","link":"https://www.geraldonit.com/reading-list-march-2026-1/"},{"t
itle":"Reading List – February 2026 #4","link":"https://www.geraldonit.com/reading-list-february-2026-4/"}]
SQL> -- Query RSS feed via JSON_TABLE
SQL> SELECT j.title, j.link
FROM JSON_TABLE(
get_rss_feed('https://www.geraldonit.com/feed/', null),
'$[*]'
COLUMNS (
title VARCHAR2(40) PATH '$.title',
link VARCHAR2(75) PATH '$.link'
)
) j;
TITLE LINK
---------------------------------------- ---------------------------------------------------------------------------
Reading List – March 2026 #3 https://www.geraldonit.com/reading-list-march-2026-3-2/
Reading List – March 2026 #3 https://www.geraldonit.com/reading-list-march-2026-3/
Reading List – March 2026 #2 https://www.geraldonit.com/reading-list-march-2026-2/
Reading List – March 2026 #1 https://www.geraldonit.com/reading-list-march-2026-1/
Reading List – February 2026 #4 https://www.geraldonit.com/reading-list-february-2026-4/
Reading List – February 2026 #3 https://www.geraldonit.com/reading-list-february-2026-3/
Reading List – February 2026 #2 https://www.geraldonit.com/reading-list-february-2026-2/
Reading List – February 2026 #1 https://www.geraldonit.com/reading-list-february-2026-1/
10 rows selected.
Neat, isn’t it? Although it seems I have to write some more blog posts again 🙂
Partition by Expression
Until now, Oracle range and list partitioning required a plain column as the partition key. If you wanted to partition by, for example, just the year extracted from a TIMESTAMP column, you had to add a virtual or generated column and partition on that. 23.26.1 removes this indirection by allowing any deterministic expression—including function calls—directly in the PARTITION BY clause.
Before 23.26.1, this wasn’t possible:
Prior partition by expression restriction
SQL> -- Create a table that is partitioned on an expression --> Not possible prior to 23.26.1
CREATE TABLE orders
(
id NUMBER GENERATED ALWAYS AS IDENTITY,
customer_id NUMBER NOT NULL,
order_date TIMESTAMP NOT NULL,
total_amount NUMBER(12, 2) NOT NULL
)
PARTITION BY LIST (EXTRACT(YEAR FROM order_date)) AUTOMATIC
(
PARTITION p_2024 VALUES (2024),
PARTITION p_2025 VALUES (2025),
PARTITION p_2026 VALUES (2026)
);
PARTITION BY LIST (EXTRACT(YEAR FROM order_date)) AUTOMATIC
*
ERROR at line 8:
ORA-00907: missing right parenthesis
Help: https://docs.oracle.com/error-help/db/ora-00907/
Starting with 23.26.1, you can use expressions now:
SQL> -- Create a table that is partitioned on an expression --> Now possible with 23.26.1
SQL> CREATE TABLE orders
(
id NUMBER GENERATED ALWAYS AS IDENTITY,
customer_id NUMBER NOT NULL,
order_date TIMESTAMP NOT NULL,
total_amount NUMBER(12, 2) NOT NULL
)
PARTITION BY LIST (EXTRACT(YEAR FROM order_date)) AUTOMATIC
(
PARTITION p_2024 VALUES (2024),
PARTITION p_2025 VALUES (2025),
PARTITION p_2026 VALUES (2026)
);
Table created.
SQL> -- Insert test data
SQL> INSERT INTO orders
(customer_id, order_date, total_amount)
VALUES
( 1, TO_TIMESTAMP('20240101', 'YYYYMMDD'), 100.20 ),
( 1, TO_TIMESTAMP('20251123', 'YYYYMMDD'), 100.20 ),
( 1, TO_TIMESTAMP('20260212', 'YYYYMMDD'), 100.20 );
3 rows created.
SQL> COMMIT;
Commit complete.
SQL> -- Check partitions
SQL> SELECT table_name, partition_name, high_value
FROM user_tab_partitions;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------- ----------
ORDERS P_2024 2024
ORDERS P_2025 2025
ORDERS P_2026 2026
Simplified Dot Notation Syntax for JSON Index Creation
Oracle supports JSON and the simplified dot notation syntax (e.g. data.customer.email) since 12.1.0.2. What was not streamlined was indexing those simple dot notation paths. Until now, you had to spell out a function-based index using JSON_VALUE. Starting with 23.26.1, there is a new JSON keyword for CREATE INDEX:
Before 23.26.1
SQL> -- CREATE JSON INDEX not available before 23.26.0
SQL> CREATE JSON INDEX idx_order_customer_email
ON orders (entry.customer.email);
CREATE JSON INDEX idx_order_customer_email
*
ERROR at line 1:
ORA-00922: missing or invalid option
Help: https://docs.oracle.com/error-help/db/ora-00922/
SQL> -- Instead, CREATE INDEX requires JSON_VALUE
SQL> CREATE INDEX idx_order_customer_email
ON orders (JSON_VALUE(entry, '$.customer.email'
RETURNING VARCHAR2(255)
ERROR ON ERROR));
Index created.
Since 23.26.1
SQL> -- Starting with 23.26.1, you can create a JSON index on the simple dot notation
SQL> CREATE JSON INDEX idx_order_customer_email
ON orders (entry.customer.email);
Index created.
DATEDIFF Function
The DATEDIFF function is a helper function that returns the number of whole date/time units between two datetime values. Using this function, developers will appreciate no longer having to remember whether to subtract two DATE/TIMESTAMP values to give days, seconds, or fractions thereof, depending on the data type involved.
Let’s consider a subscription platform that wants to know the following about its customers:
- How many days since the last login
- How many months since the last subscription
- How many years has the customer account been created
Setup DDL
SQL> -- Drop table if exists
SQL> DROP TABLE IF EXISTS customers;
Table CUSTOMERS dropped.
SQL> -- Create customers table
SQL> CREATE TABLE customers
(
customer_id NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
name VARCHAR2(25) NOT NULL,
account_created_date DATE NOT NULL,
last_subscription_date DATE NOT NULL,
last_login_tms TIMESTAMP NOT NULL,
active BOOLEAN DEFAULT true NOT NULL
);
Table CUSTOMERS created.
SQL> -- Seed with data
SQL> INSERT INTO customers
(name, account_created_date, last_subscription_date, last_login_tms)
VALUES
('John Doe', DATE '2025-01-15', DATE '2026-03-01', TIMESTAMP '2026-05-01 10:23:12.154459'),
('Jane Smith', DATE '2024-05-20', DATE '2026-02-15', TIMESTAMP '2026-04-13 14:30:23.487879'),
('Alice Johnson', DATE '2025-03-10', DATE '2026-03-10', TIMESTAMP '2026-05-03 09:45:38.874526'),
('Bob Brown', DATE '2022-07-25', DATE '2025-12-30', TIMESTAMP '2026-05-04 16:20:48.485792');
4 rows inserted.
SQL> COMMIT;
Commit complete.
Before the DATEDIFF function, developers had to write three different expressions, each with its own quirks:
Before DATEDIFF
SQL> -- Query report with arithmetic approach
SQL> SELECT customer_id,
name,
-- Days since last login
TRUNC(CURRENT_TIMESTAMP - last_login_tms) AS days_since_last_login,
-- Months since last subscription
TRUNC(MONTHS_BETWEEN(SYSDATE, last_subscription_date)) AS months_since_last_subscription,
-- Years since account creation
TRUNC(MONTHS_BETWEEN(SYSDATE, account_created_date) / 12) AS years_since_account_creation
FROM customers
WHERE active
ORDER BY account_created_date DESC;
CUSTOMER_ID NAME DAYS_SINCE_LAST_LOG MONTHS_SINCE_LAST_SUBSCRIPTION YEARS_SINCE_ACCOUNT_CREATION
----------- ------------------------- ------------------- ------------------------------ ----------------------------
3 Alice Johnson +25 00:00:00.000000 2 1
1 John Doe +27 00:00:00.000000 2 1
2 Jane Smith +45 00:00:00.000000 3 2
4 Bob Brown +23 00:00:00.000000 4 3
With DATEDIFF, the unit is an explicit, readable argument, and the calculation is identical for every unit type:
With DATEDIFF
SQL> -- Query report with DATEDIFF
SQL> SELECT customer_id,
name,
DATEDIFF('DAY', last_login_tms, SYSTIMESTAMP) AS days_since_last_login,
DATEDIFF('MONTH', last_subscription_date, SYSDATE) AS months_since_last_subscription,
DATEDIFF('YEAR', account_created_date, SYSDATE) AS years_since_account_creation
FROM customers
WHERE active
ORDER BY account_created_date DESC;
CUSTOMER_ID NAME DAYS_SINCE_LAST_LOGIN MONTHS_SINCE_LAST_SUBSCRIPTION YEARS_SINCE_ACCOUNT_CREATION
----------- ------------------------- --------------------- ------------------------------ ----------------------------
3 Alice Johnson 25 2 1
1 John Doe 27 2 1
2 Jane Smith 45 3 2
4 Bob Brown 24 5 4
If you look closely, you will note a discrepancy in the output. For Bob Brown, the DATEDIFF functions seem to be a day ahead over the MONTHS_BETWEEN functions. The explanation is the implied time boundary in time calculations, where 24 hours = 1. If you subtract yesterday 14:00 from today 08:00, you get 0.75 days apart:
Time boundary of traditional arithmetic operation based on 24 hours
SQL> -- Time boundary of traditional arithmetic operation based on 24 hours
SQL> SELECT TO_DATE('2026-01-02 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
-
TO_DATE('2026-01-01 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AS difference;
DIFFERENCE
----------
.75
Depending on how you round that, you might get 0 or 1 days. DATEDIFF on the other hand, uses well-defined time boundaries of midnight, the first day and month. Hence, jumping from the previous day to today, regardless of how many hours are in between, is calculated as 1 day:
Time boundary using DATEDIFF
SQL> -- Time boundary using DATEDIFF
SQL> SELECT DATEDIFF('DAY',
TO_DATE('2026-01-01 14:00:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2026-01-02 08:00:00', 'YYYY-MM-DD HH24:MI:SS'))
AS difference;
DIFFERENCE
----------
1
Most of the times, e.g., when your boss asks “how many days since we shipped the release?”, they do not care about what hour in the day. DATEDIFF makes date/time based calculations a lot easier by not having to worry. Of course, if more precision is required, HOUR can be used instead of DAY:
Time boundary using DATEDIFF HOUR
SQL> -- Time boundary using DATEDIFF HOUR
SQL> SELECT DATEDIFF('HOUR',
TO_DATE('2026-01-01 14:00:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2026-01-02 08:00:00', 'YYYY-MM-DD HH24:MI:SS'))
AS difference;
DIFFERENCE
----------
18
18 hours divided by 24 hours in the day would yield 0.75 again. The reason why the query above is 1 day ahead is because the traditional query simply ignores fractions by applying the TRUNC() function. Hence, whether a day since login or last subscription is near 24 hours or not does not matter, it is always assumed near 0. Removing the TRUNC() function makes that apparent:
SQL> -- Traditional query without TRUNC
SQL> SELECT customer_id,
name,
-- Days since last login
CURRENT_TIMESTAMP - last_login_tms AS days_since_last_login,
-- Months since last subscription
MONTHS_BETWEEN(SYSDATE, last_subscription_date) AS months_since_last_subscription,
-- Years since account creation
MONTHS_BETWEEN(SYSDATE, account_created_date) / 12 AS years_since_account_creation
FROM customers
WHERE name = 'Bob Brown';
CUSTOMER_ID NAME DAYS_SINCE_LAST_LOG MONTHS_SINCE_LAST_SUBSCRIPTION YEARS_SINCE_ACCOUNT_CREATION
----------- ------------------------- ------------------- ------------------------------ ----------------------------
4 Bob Brown +23 23:30:09.446006 4.96619512 3.84395712
Assertions
23.26.1 introduces Assertions, which are boolean expressions that have the semantics of a constraint. The database must ensure the truth of these boolean expressions while transactions change data and commit these changes. Often, Assertions are referred to as cross-row constraints. They provide a means to check that the current row doesn’t conflict with something that can only be answered by examining other rows. For example, that a department must have at least 1 employee, that a manager cannot report to himself via an indirection, or that a start date cannot overlap into another row’s end date. In theory, Assertions are a powerful mechanism but as of writing this post, they have so many limitations that trying to use Assertions will probably not bring the excitement that one expects. Hence, I will not cover Assertions in this blog post but feel free to take a peek into the documentation.