| Assertion error ora-8697 [message #690468] |
Tue, 31 March 2026 08:55  |
John Watson
Messages: 9000
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Has anyone tried to use assertions? They should be a huge enhancement. Long overdue, they were originally specified in the SQL-92 standard.
I'm trying a simple example: to check that the scott.emp table has exactly one PRESIDENT. Creating the assertion returns a silly error:127.0.0.1:1521/orclpdb1> sho user
USER is "SCOTT"
127.0.0.1:1521/orclpdb1>
127.0.0.1:1521/orclpdb1> create assertion check_pres check (not exists (select nullif((select count(*) from emp where job='PRESIDENT'),1)));
create assertion check_pres check (not exists (select nullif((select count(*) from emp where job='PRESIDENT'),1)))
*
ERROR at line 1:
ORA-08689: CREATE ASSERTION failed
ORA-08697: SYS owned tables are not supported.
Help: https://docs.oracle.com/error-help/db/ora-08689/
127.0.0.1:1521/orclpdb1>
The examples in the docs,
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-assertion.html
don't work either, for a different reason:127.0.0.1:1521/orclpdb1>
127.0.0.1:1521/orclpdb1> CREATE ASSERTION no_empty_departments CHECK
(NOT EXISTS
(SELECT 'an empty department'
FROM dept d
WHERE NOT EXISTS
(SELECT 'an employee in the department'
2 FROM emp e
3 WHERE e.deptno = d.deptno))); 4 5 6 7 8
FROM emp e
*
ERROR at line 7:
ORA-08689: CREATE ASSERTION failed
ORA-08673: Equijoin "E"."DEPTNO"="D"."DEPTNO" found does not meet the criteria to do a FAST validation.
Help: https://docs.oracle.com/error-help/db/ora-08689/
127.0.0.1:1521/orclpdb1> CREATE ASSERTION no_empty_departments CHECK
(ALL (SELECT d.deptno
FROM dept d) da
SATISFY
(EXISTS
(SELECT ''
FROM emp e
WHERE e.deptno = da.deptno))); 2 3 4 5 6 7 8
WHERE e.deptno = da.deptno)))
*
ERROR at line 8:
ORA-08689: CREATE ASSERTION failed
ORA-08673: Equijoin "E"."DEPTNO"="D"."DEPTNO" found does not meet the criteria to do a FAST validation.
Help: https://docs.oracle.com/error-help/db/ora-08689/
127.0.0.1:1521/orclpdb1>
Thank you for any insight.
|
|
|
|
| Re: Assertion error ora-8697 [message #690469 is a reply to message #690468] |
Tue, 31 March 2026 09:02  |
John Watson
Messages: 9000
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Oh, I see my error. And yes, it is obvious. My query is a SELECT that isn't FROM anything. That works in 26, but under the covers it is a select from dual:
127.0.0.1:1521/orclpdb1> set autot on exp
127.0.0.1:1521/orclpdb1> select sysdate;
SYSDATE
---------
31-MAR-26
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
127.0.0.1:1521/orclpdb1>
|
|
|
|
| Re: Assertion error ora-8697 [message #690470 is a reply to message #690468] |
Wed, 01 April 2026 01:16 |
John Watson
Messages: 9000
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Just for completeness, the problem with the examples in the docs is fixed if you modify emp.deptno to NOT NULL, which enables the optimization that the feature seems (not unreasonably) to require.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
