Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Creating assertions on oracle 10g

823 views
Skip to first unread message

Pedda

unread,
Oct 31, 2009, 6:50:41 AM10/31/09
to hemant...@gmail.com
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

Vladimir M. Zakharychev

unread,
Oct 31, 2009, 7:58:57 AM10/31/09
to

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

Mark D Powell

unread,
Oct 31, 2009, 1:00:39 PM10/31/09
to
On Oct 31, 7:58 am, "Vladimir M. Zakharychev"

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 --


The Boss

unread,
Oct 31, 2009, 7:18:26 PM10/31/09
to

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


Vladimir M. Zakharychev

unread,
Nov 1, 2009, 9:54:14 AM11/1/09
to
> Here's a nice thread on AskTom on the subject:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6...

>
> 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

Nah, would be violated way too often... Besides, they do implement
what they promise, but with loads of unexpected side effects aka
bugs...

ErikYkema

unread,
Nov 9, 2009, 4:14:34 AM11/9/09
to

You might want to look for Toon Koppelaar's book 'Applied Mathematics
for Database Professionals' (APress) and his framework/website
http://www.rulegen.com. Here he proposes/offers a framework for the
Oracle database that comes somewhat closer to assertions.
Erik

Pedda

unread,
Nov 16, 2009, 9:05:31 AM11/16/09
to
> I did not even knowassertionswere 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 --- Hide quoted text -
>
> - Show quoted text -

Thank U
Mr. Mark D Powell
Yes I certainly agree with you but i has read the text book "Database
Management Systems" by Raghuramakrishnan.
In which, he had explained it interms of DBMS . I just trying to
implement that in the RDBMS which results in an error.

Pedda

unread,
Nov 16, 2009, 9:13:32 AM11/16/09
to
On Nov 9, 1:14 am, ErikYkema <erik.yk...@gmail.com> wrote:
> On 31 okt, 11:50, Pedda <hemanth.6...@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
>
> You might want to look for Toon Koppelaar's book 'Applied Mathematics
> for Database Professionals' (APress) and his framework/websitehttp://www.rulegen.com. Here he proposes/offers a framework for the

> Oracle database that comes somewhat closer toassertions.
> Erik

Thanks Erik
Sure here is the change
CREATE ASSERTION ASS_NAME CHECK
((SELECT COUNT(S.SID) FROM STUDENTS S) +
(SELECT COUNT(F.FID)FROM FACULTY F)<100);

Mladen Gogala

unread,
Nov 16, 2009, 11:03:14 AM11/16/09
to
On Mon, 16 Nov 2009 06:05:31 -0800, Pedda wrote:

> Thank U
> Mr. Mark D Powell
> Yes I certainly agree with you but i has read the text book "Database
> Management Systems" by Raghuramakrishnan. In which, he had explained it
> interms of DBMS . I just trying to implement that in the RDBMS which
> results in an error.

Wow! A gentleman and a scholar, too. Have you ever considered reading a
book or two on Oracle RDBMS? I would wholeheartedly recommend the ones
written by Mr. Tom Kyte and, at a later stage, by Christian Antognini.

--
http://mgogala.byethost5.com

0 new messages