We're working on them - if there's any you think could be clearer please let me and Toon know.
We're aware of ORA-08697 when you use JOIN syntax
07.03.2026 16:54
👍 2
🔁 0
💬 0
📌 0
We're working on them - if there's any you think could be clearer please let me and Toon know.
We're aware of ORA-08697 when you use JOIN syntax
Assertions for data integrity on a less-than-ideal data model.
www.kibeha.dk/2026/03/asse...
Or "How I could have used assertions 25 years ago when I worked with Concorde XAL"...
#OrclDB #SQL #Assertions #OracleACE
Combining all into one should still be efficient; I'd have to check the details though.
Specific errors is a plus.
Filter in the ALL query, then check exists the one corresponding table in SATISFY
Could you create a separate assertion for each table?
the join columns must be non-null - either via a constraint or where clause
So like a conditional foreign key?
This should be possible with assertions, though it depends on the details
What couldn't?
SQL statement: select use_cases from assertions where creator = 'Oracle ACE' order by real_world_applicability + complexity + novelty desc fetch first 3 rows only
We're still looking for interesting assertions use cases
Just over two weeks left for #OracleACE to get your entries in
Bounty closes 15th March
For full details read blogs.oracle.com/sql/assertio...
I saw, thanks! You're welcome to submit as many as you want :)
SQuizL for 27 Feb 2026. Guess the SQL statement in the top left which ends "pokemon_trainers" using the clues provided below.
Stretch your #SQL skills with #SQuizL
A free guess the SQL statement quiz
Every day there's a new Oracle SQL statement to solve in six tries or less
Pick from the fragments below to complete the statement on the top left
Can you get it in one?
buff.ly/hpGFuBa
SQL statement: select use_cases from assertions where creator = 'Oracle ACE' order by real_world_applicability + complexity + novelty desc fetch first 3 rows only
We're still looking for interesting assertions use cases
Just over two weeks left for #OracleACE to get your entries in
Bounty closes 15th March
For full details read blogs.oracle.com/sql/assertio...
Brand new to Oracle AI Database? Start Here
Many Oracle guides are written for DBAs or long-time developers, and as a newcomer it feels like you’re expected to already know the jargon. I started in the same place, learning the as I went, and I’m still learning each day. If you're considering using…
Aggregation Filters in Oracle AI Database 26ai
oracle-base.com/articles/26/...
Hierarchies should be trees and not loops.
Learn how SQL Assertions in Oracle AI Database 23.26.1 can help prevent cycles in hierarchical data.
www.salvis.com/blog/2026/02...
📚 New Reading List is out!
This week, we got a lot of great ⭕ #Oracle AI #Database and 🤖 #AI content!
📖 Happy reading!
The recording for this session is now avaiable
www.youtube.com/watch?v=ulLy...
Example SQL statement in 23.26.1 with the FILTER clause for aggregates select sum ( salary ) filter ( where job_id = 'IT_PROG' ) total_programmer_salaries, avg ( salary ) filter ( where job_id like '%CLERK' ) mean_clerk_salaries from employees;
You can conditionally filter aggregates in Oracle AI Database 23.26.1 with
fn ( col ) FILTER ( WHERE ... )
In earlier releases use
fn ( CASE WHEN ... THEN col END )
Both these filter the rows as part of the aggregration
Read more in my latest post blogs.oracle.com/sql/conditio...
A SQL statement finding the top three assertions use cases
Calling #OracleACE: we want your assertion use cases!
We’re opening a bounty for creative, real-world solutions using assertions
Submit your blog post, video, script or slide deck the contribution app by the end of 15 March
Full details at buff.ly/fNgDHQv
Great work by the top 3 players in the Weekly DB comp on Oracle Dev Gym for January
1st mentzel.iudith
2st Mahamoutou
3rd Andrey Zaytsev
👏🏆
Thanks to all who took part
See the full rankings at devgym.oracle.com/pls/apex/f?p...
To learn about this, join us Tues 17th Feb at 2pm UK time asktom.oracle.com/ords/r/tech/...
In Feb's Ask TOM Live we're looking at fourth normal form:
"A table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X ↠ Y, X is a superkey"
To help you understand this, would you prefer
A description of these technical terms?
Something more informal?
New in Oracle Database #26ai - Aggregation Filters using the FILTER clause in #SQL.
db-oriented.com/2026/02/06/a...
@oracleace.bsky.social @sym42.bsky.social
I've added a check constraint to the required list to be explicit
Congratulations Chris!
I learned a lot reading Troubleshooting Oracle Performance many years ago; hope pre-retirement suits you well
In particular, all expressions you can use in WHERE are valid in QUALIFY, but you should strongly prefer to put them in WHERE to ensure optimal plans
It's not quite what you would write manually
The QUALIFY clauses get wrapped as a Boolean expression. So if you have
QUALIFY p1 AND p2 AND p3
it becomes
SELECT * FROM (
SELECT ( p1 AND p2 AND p3 ) qexpr ...
) WHERE qexpr IS TRUE
So yes, a manual rewrite may give better optimizations
Yep - use a check constraint
Use Oracle FreeSQL.com as a remote test database
FreeSQL.com lets you use a free, hosted Oracle AI Database instance right from your browser. But, FreeSQL can also be used as a remote database for testing, POCs, and more! In this article, we'll walk though how to connect to a FreeSQL database from…
Examples of valid and invalid time periods when checking for overlapping ranges with respect to a reference period
Preventing overlapping start-end dates in history tables is a common challenge
Oracle AI Database gives you two ways to do this:
Guarantee consecutive ranges with foreign keys
Stop overlapping ranges with assertions
Find out how in my latest post blogs.oracle.com/sql/how-to-s...