I updated my SQL Assertion article with some maths and added the ALL ... SATISFY syntax that avoids double NOT EXISTS in Oracle Database SQL Assertions dev.to/franckpachot...
01.03.2026 09:50
👍 46
🔁 3
💬 1
📌 0
I updated my SQL Assertion article with some maths and added the ALL ... SATISFY syntax that avoids double NOT EXISTS in Oracle Database SQL Assertions dev.to/franckpachot...
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
SQL-Assertions in #oracle26ai : They work. They are a Very Powerful feature!).
And you can even see + inspect some the effort that "makes it happen"
/cc
@toonkoppelaars.bsky.social
@oracleace.bsky.social
simpleoracledba.blogspot.com/2026/03/orac...
How do you enforce temporal data integrity?
With SQL Assertions in Oracle AI Database, we can now implement constraints such as:
✅ Non-overlapping validity periods
✅ Temporal referential integrity
Here's a practical example using an extended DEPT-EMP model.
www.salvis.com/blog/2026/03...
There's a 'from dual' missing in your attempt 2
That tool literally made me make the move to the architecture/DBA/performance areas...
And then J2EE happened. No returning to being a dev for me...
CASE*Designer is what drove me away from being a (client/server) developer: suddenly it took me weeks to crank out something useful that could be tested, instead of hours...
And don't start me about, "generating DDL-scripts, or Forms"...
Pencil and paper, for something quick.
Powerpoint for something more permanent.
Fantastic!
![Assertions (23.26.1)
Declare cross-row, cross-table constraints with simple SQL
Use existential ([NOT] EXISTS) expressions to define rule
Example to prevent overlapping job history ranges](https://cdn.bsky.app/img/feed_thumbnail/plain/did:plc:xh6h36ybpgxcl47nmewyotj7/bafkreigggme4v76vj7vul3p4xx47evnmf7ytysi6qy776umjvzxkqvduom)
Assertions (23.26.1) Declare cross-row, cross-table constraints with simple SQL Use existential ([NOT] EXISTS) expressions to define rule Example to prevent overlapping job history ranges
Assertions (23.26.1) Declare cross-row, cross-table constraints with simple SQL New universal expressions (ALL-SATISFY) as a clearer way to express rules that apply to every row Example to prevent overlapping job history ranges
Assertions
Define cross-row, cross-table constraints with simple SQL:
CREATE ASSERTION ... CHECK ( <rule> )
Use this to enforce rules like
* No overlapping start-end dates
* Every parent row must have a child row (invoices must have an invoice line)
This is a fresh www.oracle.com/database/tec...
I'd assume it wouldn't need repairing?
But from your response I gather, I did the things that should've been done?
Keep hitting ORDS-22001, what's the thing I'm missing?
- I've granted my procedure to everyone
- I've used ords_admin.add_plsql_gateway_procedure to add it
- I've added it to wwv_flow_epg_include_mod_local
Any ideas?
@alexnuijten.bsky.social @apexconsulting.nl
Yup, around the same time Lex and I were working on:
www.amazon.com/Applied-Math...
At dbdbd2025.uantwerpen.be
You missed one there... 🧐
Adds used to be fun...
@lukaseder.bsky.social ☝️
Also in SQL assertions, you cannot have nested ALL-SATISFY, only at top-level.
Next interesting topic.
Next: Roel and Alex.
#DOAG2025
For me it was other way around. The clip reminded me of the work we were doing 😀
Some of it is easy to spot...
Come to my session next week at DOAG...
