Oracle AI Database added the FILTER clause for aggregates in 23.26.1

fn ( ... ) FILTER ( WHERE ... )

Harris Fungwi shows how to use it to solve LeetCode problem 1158:

Count a customer's orders for 2019

Check their blog to see how and other solutions

Oracle 26ai - UUID v4 available, and looking at v7, Interesting! TL;DR: in Oracle 26ai, there is the new function UUID(), which implements the UUID-V4. Someone also mentioned UUID-V7. I started experimenti...

Oracle AI Databaes 23.9 added a UUID() function

These generates v4 UUIDs

@pdevisser.bsky.social checks it out an looks at methods for generating v7 UUIDs

Convert PL/SQL record and arrays to JSON and back (23.4) Passing PL/SQL variables to JSON the constructor converts them to JSON Returning PL/SQL types in JSON_VALUE converts the JSON to the type Example converting JSON to and from a PL/SQL array of records

Convert PL/SQL records and arrays from and to #JSON in Oracle AI Database 26ai

JSON_VALUE ( ..., '$' RETURNING <plsql type> ) => JSON to PL/SQL

JSON ( <plsql var> ) => PL/SQL to JSON

Try it at buff.ly/ewJPfCT

GitHub - krisrice/oracle-db-skills: Oracle DB Skills is a curated library of 100+ practical, documentation-backed guides for working with Oracle Database, organized by domain Oracle DB Skills is a curated library of 100+ practical, documentation-backed guides for working with Oracle Database, organized by domain - krisrice/oracle-db-skills

AI agent skills are a set of markdown instructions explaining how to do a task

@krisrice.io has built a library of 100+ skills for working with Oracle AI Database with actionable examples, best practices, and common pitfalls

Get them at

Assertiveness Training This blog post is not a workshop how to communicate needs, opinions, and feelings clearly. However, the title does refer to a new feature in Oracle 26ai. I will demonstrate this feature using …

Stopping users storing overlapping periods is a challenge

Oracle AI Database 26ai makes it simple with assertions

Rob van den Berg show you how with

CREATE ASSERTION ... CHECK (
ALL ( <query> ) t1
SATISFY ( NOT EXISTS ( <overlaps query> ) )
)

buff.ly/qQeuUH6

Immutable Table Enhancements in Oracle Database 23ai/26ai Immutable tables were introduced to Oracle 21.3 and 19.11 at the same time, so it could be considered a 19c and 21c new feature. This article demonstrates the enhancements to immutable tables in…

Make insert-only tables in Oracle AI Database with

CREATE IMMUTABLE TABLE ... ( ... )
NO DROP UNTIL m DAYS IDLE
NO DELETE UNTIL n DAYS AFTER INSERT

Take care with the NO clauses

They control when you can delete rows or drop the table

@oraclebase.bsky.social demos

Row level audit that is IMMUNE to table changes Here's an audit utility that lets you capture row changes whilst also being immune to structural changes to the base tables. Get the original trigger-based audit utility here…

Triggers are a common way to track DML changes

But it's easy to overlook these when you add new columns!

@connormcd.bsky.social shows a compound trigger Oracle AI Database solution

Store rowids in a private temp table
Join these to the table
Get every column with JSON_OBJECT(*)

Using JSON Schema to validate data submitted to the database via REST Calls The title’s a mouthful (sorry!), but here’s the core idea: your application exposes REST endpoints through Oracle REST Data Services (ORDS). When a client uses POST to insert new records, the input…

JSON Schema in Oracle AI Databse 26ai enables you to validate the structure of JSON objects and arrays using DBMS_JSON_SCHEMA

@martindba.bsky.social demos how you can use this to
check data submitted to the database via REST calls

Enhanced Partitioning Metadata in Oracle 23c Oracle 23c has a lot of small, but useful enhancements that makes life of developers easier. For example two new columns in the data dictionary views for partition metadata.

Sometimes its the small changes that make the big difference

Oracle AI Database 26ai added

HIGH_VALUE_CLOB
HIGH_VALUE_JSON

To the partitioning dictionary views

=> it's easier to query partition boundaries

@danischnider.bsky.social discusses

How to Fetch Multiple Oracle Execution Plans in One Nice Query When looking at execution plans in Oracle, we’ll have to do several steps to be able to call the DBMS_XPLAN package functions. In fact, we have to find out the SQL_ID for a given statement fi…

Get the plan for #SQL in Oracle AI Database with dbms_xplan.display_cursor

But what if you want to get many at once?

@lukaseder.bsky.social demos:

SELECT ...
FROM v$sql s
,TABLE (dbms_xplan.display_cursor ( s.sql_id, s.child_number )) p
WHERE s.sql_text LIKE ...

Graph traversal queries with SQL/PGQ (23.4) SQL/PGQ added in SQL:2023 ISO standard Create a property graph to define relationship between rows Use GRAPH_TABLE operator to traverse the graph Example CREATE PROPERTY GRAPH statement

Graph traversal queries with SQL/PGQ (23.4) Find everyone friends with Mary example GRAPH_TABLE query

With SQL/PGQ in Oracle AI Database, you can

Create property graphs
Query them with GRAPH_TABLE

This maps tables and rows to vertices and edges in a graph

This enables you to traverse networks of relationships with #SQL

Try it at buff.ly/89FnPaf

15 SQL Interview Questions TO GET YOU HIRED in 2025 | SQL Interview Questions & Answers |Intellipaat 📚 Explore the Top 100 SQL Interview Questions and ace your next interview! Click here to dive in: https://intellipaat.com/blog/interview-question/sql-interview-questions/ 🔥Checkout Intellipaat's…

Preparing for an interview with #SQL questions?

Intellipaat covers 15 questions & answers, including

Constraints
Normalisation
DDL and DML commands
GROUP BY vs ORDER BY
WHERE vs HAVING
UNION and UNION ALL
Find 2nd highest salary

Loading and Processing JSON with PL/SQL This blog is part of a  series  about aspects and features of Oracle 26 and Autonomous Transaction Processing Database. "JSON (JavaScript Ob...

Want to analyze your Strava data with #SQL?

@go-faster.co.uk shows 3 ways you can load it into Oracle AI Database to do this

Directly through a JSON Duality View
Convert each name-value pair in explicit code
Extract Values from JSON in Virtual Columns

Hands-on CRUD with JDBC and JSON Relational Duality Views - andersswanson.dev JSON Relational Duality Views are one of my favorite Oracle AI Database features (I’ll call them “duality views” from here on out). Duality views let you model data once, then serve it cleanly to…

Duality views in Oracle AI Databse allow you to design a single, canonical model that satisfies both document and relational APIs

@anders-swanson.bsky.social shows you how to use these in a Java app to do CRUD operations

andersswanson.dev buff.ly/KHR6LES

The Hitchhiker’s Guide to connecting to Oracle from Python Aliens are among us.If you want proof, just try getting a minor software upgrade applied to a server in a large organisation and you will be sure to run into a level of bureaucracy that can only ha…

Want to connect Python to Oracle AI Database?

@antikyte.bsky.social has built a guide of installing & using

cx-Oracle with Python 3.6.8
python-oracledb with Python 3.12

Showing how to

select from a table
execute a packaged procedure
execute a packaged function

Update default on null (23.4) Set the default value when updating a column to NULL SQL code CREATE TABLE default_values ( id integer, c1 number DEFAULT ON NULL FOR INSERT ONLY 1, c2 number DEFAULT ON NULL FOR INSERT AND UPDATE 2 ); INSERT INTO default_values VALUES ( 1, NULL, NULL ), ( 2, -99, -99 ); UPDATE default_values SET c2 = NULL; SELECT * FROM default_values;

Override NULL on insert and updates in Oracle AI Database 26ai

DEFAULT ON NULL FOR INSERT AND UPDATE

The database will replace NULL with the default value in these statements

This extends DEFAULT ON NULL added in 12c

Try it at buff.ly/G5xNLm7

Assertions used in arc-relationship TL;DR: A simple use-case for SQL-Assertions: Adding a data-related constraint to an arc-relation. Demo using the Emp-Dept schema. Summary u...

An arc relationship is when a row can reference exactly one of many parent tables

The challenge is when a column in a yet another table defines which is the parent

@pdevisser.bsky.social shows how to use assertions in Oracle AI Database to do this

Enforcing Acyclic Hierarchies With SQL Assertions - Philipp Salvisberg's Blog Explore SQL Assertions in Oracle AI Database and learn how to enforce hierarchy integrity by detecting cycles with declarative constraints.

Loops in hierarchical data are a pain

Easy to add
Hard to prevent

@salvis.com shows Oracle AI Database 23.26.1 gives a simple solution

Create an assertion limiting the maximum depth to N

Do this by joining the hierarchy N+1 times in the assertion

Order-Equivalence Do identical OVER clauses always produce identical row orders?

Should these functions return the same value?

SELECT
ROW_NUMBER() OVER (ORDER BY x),
ROW_NUMBER() OVER (ORDER BY x)
FROM ...

What about when many rows have the same value for X?

According to the #SQL standard: yes

@winand.at investigates

Oracle AI Database 26ai has a huge number of #SQL enhancements

Harris Fungwi covers his favourites:

No more FROM dual
Shrinking Tablespaces
Priority transactions
GROUP BY ALL & alias
DB_DEVELOPER_ROLE
IF [NOT] EXISTS for DDL

The code should read:

SELECT JSON_TRANSFORM (
JSON ('{ "arr": [3,2,1] }'),
SET '$.origArr' = PATH '$.arr',
PREPEND '$.arr' = 10,
SORT '$.arr',
SET '$.sum' = PATH '@.arr[*].sum()',
SET '$.count' = PATH '@.arr[*].count()'
) jarr;

HT @thatjeffsmith.com

Try it at freesql.com?compressed_c...

Edit #JSON with #SQL in Oracle AI Database with

JSON_TRANSFORM ( <json>, <changes> )

Release 26ai added operations for

Arithmetic
Aggregations
PREPEND & SORT arrays
COPY
MINUS / INTERSECT / UNION set ops
MERGE
NESTED PATH
CASE expressions

How to Answer REAL Deloitte SQL INTERVIEW question. SQL Interview Question and Answer | External Table in SQL In SQL interviews, you’ll often face tricky questions that test your problem-solving skills. One such question asked in a Data Analyst…

Want to query CSV files using Oracle AI Database?

Create an external table to access them

CREATE TABLE csv ( ... )
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY ...
LOCATION ( 'file.csv' )
)

Then access the file with #SQL

Manish Sharma demos

Annotations in Oracle Database 23ai/26ai This article describes the use of annotations to document our database objects in Oracle database 23ai/26ai.

Document objects in Oracle AI Database with schema annotations

Define these name-value pairs with

ANNOTATIONS ( name { 'value' } {,} )

You can add these to

Tables
View
Materialized View
Domains

Added in 26ai, backported to 19.28

@oraclebase.bsky.social demos

💢 A complete overview of performance tuning capabilities and features in Oracle Database💢 Oracle, Oracle Database, Performance tuning, RAC, Standby database, Oracle features

Oracle AI Database has many tools to help you diagnose performance issues, including

Automatic Workload Repository (AWR)
Time Model Statistics
Performance Tuning Advisors
SQL Plan Management

@ar-kamrani.bsky.social gives an overview of these

Staging Tables in Oracle 23ai Browsing through the Oracle 23ai New Features Guide, I once again discovered a small, inconspicuous feature that I had previously overlooked: There is a new option to create stage tables. What is t…

In Oracle AI Database 26ai you can create staging tables:

CREATE TABLE ... FOR STAGING ( ... )

These simple tables

- Can't be compressed
- Have statistics locked
- Don't go into the recyclebin when dropped

@danischnider.bsky.social investigates

JSON-Relational Duality Views (23.4) Create a JSON interface for relational tables Define using SQL or GraphQL style syntax The database converts DML using JSON objects on the view to the relational tables Example duality view created using GraphQL syntax

JSON-Relational Duality Views (23.4) Example inserting JSON into a duality view and showing it writes to the underlying database tables

With JSON Relational duality views in Oracle AI Database 26ai you can

Define a #JSON API over tables using #SQL or GraphQL
Read & write JSON data; the database translates this to relational tables

This gives you the benefit of

Normalized data storage
Simple JSON data access

Oracle AI Database features and licensing app screenshot

What to know which #SQL features were added to Oracle AI Database and when?

Check the Features and Licensing app

apex.oracle.com/database-fea...

Use this to:

Choose a release and see what's new
Find which release a feature first appeared

Oracle 26ai CALENDAR Functions – First Look A technical first look at Oracle 26ai CALENDAR functions, exploring syntax, formatting rules, fiscal logic, and practical caveats in analytical SQL.

Oracle AI Database 23.26.1 introduces a suite of date functions to

Add X periods
Get the date unit
Get X period of Y unit
Get the date unit start and end

For calendar, fiscal, and retail dates

e.g.

RETAIL_ADD_MONTHS
FISCAL_DAY_OF_YEAR

@andrejsql.bsky.social demos

There are many #JSON enhancements in Oracle 26ai

Join #OracleACE @robbie.databee.org at 2pm UTC 15 April 2026 to learn about

The JSON datatype
JSON constructors
JSON_mergepatch and JSON_transform functions
Using flexible domains for JSON Schema validation