Re: Creating assertions on oracle 10g

From: The Boss <usenet_at_No.Spam.Please.invalid>
Date: Sun, 1 Nov 2009 00:18:26 +0100
Message-ID: <4aecc5c3$0$1525$e4fe514c_at_dreader19.news.xs4all.nl>

Vladimir M. Zakharychev wrote:
> On Oct 31, 1:50 pm, Pedda <hemanth.6..._at_gmail.com> wrote:
>> Hi,
>> I am working on Oracle 10g Database.
>> I am trying to create an assertion in Oracle 10g but it is displaying
>> an error message
>> I use the following command to create an assertion in 10g but it is
>> not working.
>>
>> CREATE ASSERTION AST1(SELECT COUNT(*) FROM EMP + SELECT COUNT(*) FROM
>> DEPT>SELECT COUNT(*) FROM EMP_DEPT);
>>
>> Please let me know any changes to the above command so that the above
>> assertion works in Oracle10g
>>
>> Thanks,
>> Hemanth
>
> Why do you expect it to work? In which Oracle SQL reference manual did
> you find this CREATE ASSERTION command? Even 11g R2 doesn't have it,
> nor any previous release. Indeed, it's part of the SQL-92 standard,
> but to the best of my knowledge, no commercial RDBMS product ever
> implemented it. You can possibly implement an equivalent to assertion
> in Oracle as a statement level trigger or a check constraint on a
> refresh-on-commit materialized view.
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com

Here's a nice thread on AskTom on the subject: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:698031000346429496

I like the comment made by an anonymous reader: <q>
we need a
CREATE ASSERTION no_internal_oracle_error AS CHECK oracle_implements_what_it_promises
</q>

-- 
Jeroen 

Received on Sat Oct 31 2009 - 18:18:26 CDT