Author | Lightnews

SQLDaily @sqldaily.bsky.social · 1d

Oracle Database 23ai becomes Oracle AI Database 26ai when you apply the Oct 2025 Release Update

This adds a GRAPHQL table function that

Accepts #GraphQL queries
Maps fields to database tables and columns
Returns JSON

e.g.

SELECT * FROM GRAPHQL ( ' tab { col1 col2 } ' )

$GRAPHQL table function (23.26) Use in the FROM clause; accepts GraphQL query as a string and returns JSON Maps GraphQL fields to database tables and columns; returns the results as a one column named DATA Example query -- Get an array of employees in IT select * from graphql ( ' hr.departments ( department_name: "IT" ) { department_id hr.employees [ { first_name, last_name } ] }' ); -- Maps to a query like select json_object ( 'department_id' : department_id, 'employees' : ( select json_arrayagg ( json_object ( … ) ) from hr.employees e where e.department_id = d.department_id ) ) data from hr.departments d where department_name = 'IT';$

SQLDaily @sqldaily.bsky.social · 2d

Use Oracle Data Redaction to mask sensitive data in query results

Kajal Singh runs through the enhancements added to this in 23.6 for

Mathematical & set functions
Sorting & grouping
Views with expressions
Virtual columns in function-based indexes

Getting Oracle to Create List Partitions automatically

SQLDaily @sqldaily.bsky.social · 3d

Create new list partitions when you insert new partition key values in Oracle #SQL by defining the table

PARTITION BY LIST ( ... ) AUTOMATIC

Or convert existing tables with

ALTER TABLE ... SET PARTITIONING AUTOMATIC

@antikyte.bsky.social demos

I recently inherited the support of an application that had been written on Oracle 11g. One of the maintenance tasks was to create a new set of partitions every so often so that records with a new …

Soft Deletes via Partitioning

2

SQLDaily @sqldaily.bsky.social · 4d

Apply the Oct 2025 Release Update and Oracle Database 23ai becomes Oracle AI Database 26ai

This adds the QUALIFY clause

QUALIFY filters window functions after grouping
Like the HAVING clause does for aggregate functions

SELECT ... FROM ...
QUALIFY fn OVER ( ... ) > ...

QUALIFY clause (23.26)
Filter the result of window functions without subqueries; happens after grouping
QUALIFY is to window functions as HAVING is to aggregate functions & GROUP BY

SQL examples to return all the sales for customers who have placed 10+ orders and the amount_sold is greater than the overall mean amount_sold without qualify (23.9 and earlier) and with qualify (23.26 and later)

4 4

SQLDaily @sqldaily.bsky.social · 7d

Instead of removing rows via DELETE, many applications mark rows as deleted by setting a flag

With Oracle #SQL, you can partition on this deleted flag to separate active and inactive data

But there are nuances to this - @connormcd.bsky.social covers the details

Community friend Gwen Shapira tweeted this the other day This is a common design pattern in Oracle databases (and many others) use a DELETED_FLAG style column to handle “soft deletes.” …

SQLDaily @sqldaily.bsky.social · 8d

Got unaggregated columns you want to include in the SELECT list, but not the GROUP BY?

You can use ANY_VALUE, e.g.:

SELECT c1, ANY_VALUE ( c2 ), COUNT ...
FROM ...
GROUP BY c1

This returns the value from a random row (it's optimized to return the first) => non-deterministic

ANY_VALUE
A function to simplify and optimize the performance of GROUP BY statements
Returns a random value in a group and is optimized to return the first value in the group
Ensures no more comparisons for any incoming row
Eliminates the necessity to specify every column as part of the GROUP BY clause.

Example SQL statement:

SELECT c.country, ANY_VALUE ( s.sales_rep_name ), SUM ( s.amount_sold )
FROM customers c, sales s
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id;

Can we Detect Disable Trigger in the Oracle Database

SQLDaily @sqldaily.bsky.social · 9d

Database triggers often contain auditing or other security logic

So stopping bad actors from disabling triggers is vital

@petefinnigan.bsky.social shows how in Oracle Database:

Create an AFTER DDL ON DATABASE trigger
Check ALTER TRIGGER statements for DISABLE

Pete Finnigan's Oracle security weblog

Using Oracle Heat Map to Understand Table Access Patterns

2

SQLDaily @sqldaily.bsky.social · 10d

Define which prior rows to include in window functions with

ORDER BY ... [ frame ] ... PRECEDING

The frame states which sort keys to include (UNBOUNDED = all)

ROWS - Strict row count
RANGE - Logical value offset; only valid for numbers & datetimes
GROUPS - Unique values count

Window functions
Window preceding
Examples sorting the data by number. For windows of the 1, 2, and unbounded preceding, so the count included for the frames rows, range, and groups

Window functions
Window preceding
Examples sorting the data by letter. For windows of the 1, 2, and unbounded preceding, so the count included for the frames rows, range, and groups
The X shows where RANGE is invalid

2

SQLDaily @sqldaily.bsky.social · 11d

Use Heap Maps in Oracle Database to see:

Which tables are actively used vs. rarely/never used
Whether data is being read by full table scans or index lookups
Opportunities for compression, archiving, or other ILM policies

Matt DeMarco shows how to use it

Oracle Database includes a powerful feature called Heat Map, which tracks how segments are accessed and helps guide Information Lifecycle Management (ILM) decisions. This post walks through a pract…

Why does my call to json_table return null for a column despite it not being empty?

SQLDaily @sqldaily.bsky.social · 14d

Convert #JSON to rows & columns with JSON_Table

But if there are errors extracting a field, by default, Oracle Database sets its value to null

@martindba.bsky.social shows how to force an error instead in 23ai with

ALTER SESSION SET JSON_BEHAVIOR="ON_ERROR:ERROR"

I recently worked on a fun project involving JSON and Oracle Database 23ai. I had to store rather large JSON documents, individual field lengths exceeding what I usually see. No problem with that, …

10 Common Mistakes Java Developers Make when Writing SQL

SQLDaily @sqldaily.bsky.social · 15d

Oracle Database 21c enhanced FOR LOOPs in PL/SQL to enable you to use

Custom increments (BY)

Lists of values to loop through

Conditions to skip the loop body (WHEN)

Expressions to set the next value (REPEAT WHILE)

Mutable iterands to set their value inside the loop (MUTABLE)

PL/SQL Iterator Constructs
blogs.oracle.com/sql/better-loops-and-qualified-expressions-array-constructors-in-plsql
Step control (BY)
Iteration sequences (lists of values)
Mutable iterators (MUTABLE)
Skip iteration values (WHEN)
Repeated expressions (REPEAT)

SQLDaily @sqldaily.bsky.social · 16d

Are you making any of these common #SQL mistakes?

Forgetting about NULL
Processing data in Java memory
Using JDBC Pagination to paginate large results
Using aggregate instead of window functions

@lukaseder.bsky.social explains what to do instead & lists 7 more

This article is part of a series. You might also like: 10 More Common Mistakes Java Developers Make when Writing SQLYet Another 10 Common Mistakes Java Developers Make When Writing SQL Java develop…

oracle23ai and python - eliminate RoundTrips.

4 8

SQLDaily @sqldaily.bsky.social · 17d

Processing one row at a time leads to lots of round-trips to the #database

This is SLOOOOOOOW

It's faster to batch records and send many in one call

How much faster?

@pdevisser.bsky.social tested with Python and batching inserted 150x more rows!

TL;DR: Searching for RoundTrips to eliminate between python and Oracle. In this case, we "collect" the individual inserts to do them in a fe...

2 3

SQLDaily @sqldaily.bsky.social · 18d

You can subtract one year from a date in Oracle #SQL with either

ADD_MONTHS ( dt, -12 )
- INTERVAL '1' YEAR

But take care - these handle 29th Feb differently:

ADD_MONTHS => 29th Feb -> 28th Feb in previous year
INTERVAL => 29th Feb -> 29th Feb in previous year => error!

Looking Through Holes in Time - Sean D. Stuber

SQLDaily @sqldaily.bsky.social · 21d

The Gregorian calendar was adopted in 1582

In this year 4 Oct was followed by 15 Oct

The dates between (5-14 Oct) don't exist in Oracle Database

This can lead to surprising results, e.g.

SELECT DATE'1582-10-10' => 15 Oct

@sdstuber.bsky.social investigates

While testing some date/time functions today I came across an interesting quirk in Oracle's Gregorian calendar and different date representations. In the Gregorian calendar, the day after October 4,…

SQLDaily @sqldaily.bsky.social · 22d

Index scalar values in #JSON arrays stored in Oracle Database with

CREATE MULTIVALUE INDEX ... ON t ( t.json_data.array. )

The optimizer can use these indexes when searching the array with

JSON_EXISTS ( t.json_data, ' $.array? ( @. == ... ) '

$JSON enhancements JSON data type: a new data type for binary JSON representation making JSON reads & writes faster Multi-value index for JSON enables fast searches within JSON arrays Example: CREATE TABLE json_data ( data JSON ); CREATE MULTIVALUE INDEX j_array_i ON json_data j ( j.data.arr.number() ); INSERT INTO json_data VALUES ( JSON ( '{ arr : [1,2,3] }' ) ); SELECT * FROM json_data j WHERE JSON_EXISTS ( j.data, '$.arr?(@.number() == 1)' ); --------------------------------------------------------- | Id | Operation | Name | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | | 2 | INDEX RANGE SCAN (MULTI VALUE) | J_ARRAY_I | ---------------------------------------------------------$

DETERMINISTIC and RESULT_CACHE PL/SQL functions

SQLDaily @sqldaily.bsky.social · 23d

To cache the results of PL/SQL function calls, declare them

DETERMINISTIC => cache within a statement
RESULT_CACHE => cache across sessions

Ensure the functions have few unique inputs & are deterministic before doing this!

@antikyte.bsky.social explores

Recent events here in the UK have once again emphasised the value of being careful with your “cache”. To this end, I’m going to take a look at how PL/SQL functions defined using t…

Partition Pruning/Elimination on Reference Partitioned Tables

4

SQLDaily @sqldaily.bsky.social · 24d

Filter tables on their partition key to get partition pruning in Oracle #SQL

=> your query is more efficient

But with reference partitioning, this key is in the parent table!

@go-faster.co.uk shows how filtering these by foreign key enables pruning

I discussed locally partitioning the unique index on a reference partitioned table in a previous blog . Having implemented it, I wanted to ...

5

SQLDaily @sqldaily.bsky.social · 25d

Get values from the initial/nth/final row with

FIRST_VALUE => inital
NTH_VALUE => Nth
LAST_VALUE => final

The default window ends at the current value =>

NTH_VALUE ( v, N ) is null for rows before N
LAST_VALUE gives the final value with the same sort key, not the very last row

Visualization of the values returned for the functions

FIRST_VALUE ( shape )
NTH_VALUE ( shape, 4 )
LAST_VALUE ( shape )

Ordered by either number, letter, or letter and number.

SQLDaily @sqldaily.bsky.social · 28d

Oracle Database has added many #SQL features in RUs like

Non-positional INSERT
GROUP BY ALL
TIME_BUCKET function

But how do you find these?

In the New Features Guides!

Some need you to up COMPATIBLE - @mikedietrichde.com the impact this has

buff.ly/5i5aPnu

SQLDaily @sqldaily.bsky.social · 29d

Create blockchain tables in Oracle Database with

CREATE BLOCKCHAIN TABLE ... ( ... )
NO DROP UNTIL n DAYS IDLE
NO DELETE UNTIL n DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1"

These

Allow INSERT
Disallow UPDATE
Disallow DELETE/DROP until you wait N days as defined

Blockchain tables Backported to 19.10
Insert-only tables with hash chain – more secure than immutable tables, but uses more space
CREATE BLOCKCHAIN TABLE trade_ledger (
trade_id NUMBER,
customer_name VARCHAR2(100),
confirmation_date DATE,
value NUMBER(8,2))
NO DROP UNTIL 3 DAYS IDLE
NO DELETE UNTIL 16 DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1";

INSERT INTO trade_ledger VALUES (12341, 'Gerald', '08-SEPT-2025', 1000);

DELETE trade_ledger WHERE trade_id = 1;
ORA-05715: operation not allowed on the blockchain or immutable table

DROP TABLE trade_ledger PURGE;
ORA-05723: drop blockchain or immutable table TRADE_LEDGER not allowed

GraphQL syntax for JSON Relational Duality Views - andersswanson.dev

SQLDaily @sqldaily.bsky.social · Sep 24

Define #JSON Relational Duality Views in Oracle Database with GraphQL syntax

CREATE JSON DUALITY VIEW ...
AS student @insert { -- student table, allowing inserts
_id : id -- student.id
...
}

@anders-swanson.bsky.social gets into the details andersswanson.dev buff.ly/fhqMxZt

Working with JSON Relational Duality Views, you can use the SQL or GraphQL DDL syntax. I like the GraphQL format for its simple, expressive syntax: in this article, we’ll explore using GraphQL to…

SQLDaily @sqldaily.bsky.social · Sep 23

The NVL function in Oracle #SQL returns

The 1st argument if it's non-null
Otherwise the 2nd argument

Use it like so

WHERE col = NVL ( :var, col )

And the plan can use an index if :var is non-null & a full table scan if it is

@monikalewandowska.bsky.social demos