Re: Creating assertions on oracle 10g
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 31 Oct 2009 10:00:39 -0700 (PDT)
Message-ID: <3b8fd243-186d-4795-99c5-c6804691961c_at_m16g2000yqc.googlegroups.com>
On Oct 31, 7:58 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> 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
Date: Sat, 31 Oct 2009 10:00:39 -0700 (PDT)
Message-ID: <3b8fd243-186d-4795-99c5-c6804691961c_at_m16g2000yqc.googlegroups.com>
On Oct 31, 7:58 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> 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
I did not even know assertions were in the ANSI-92 SQL standard. There is a dbms_assert package, introduced in 10gR2 and back ported on some platforms, but it is for use in discovering SQL injection flaws so it is likely not of interest to this thread but I will mention it anyway just in case.
Table triggers, column constraints, and Function based indexes seem to be possible tools for use in placing assertion type logic in code.
- Mark D Powell --