Creating assertions on oracle 10g...

Main Page | Report this Page

Computers Forum Index » Computer - Databases - Oracle (Server) » Creating assertions on oracle 10g...

Page 1 of 1

Creating assertions on oracle 10g...

Author

Message

Pedda...

Posted: Sat Oct 31, 2009 10:50 am

Guest

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

Posted: Sat Oct 31, 2009 11:58 am

Guest

On Oct 31, 1:50 pm, Pedda <hemanth.6... at (no spam) gmail.com> wrote:

Quote:

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

Posted: Sat Oct 31, 2009 5:00 pm

Guest

On Oct 31, 7:58 am, "Vladimir M. Zakharychev"
<vladimir.zakharyc... at (no spam) gmail.com> wrote:

Quote:

On Oct 31, 1:50 pm, Pedda <hemanth.6... at (no spam) 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 --

The Boss...

Posted: Sun Nov 01, 2009 3:18 am

Guest

Vladimir M. Zakharychev wrote:

Quote:

On Oct 31, 1:50 pm, Pedda <hemanth.6... at (no spam) 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

Vladimir M. Zakharychev...

Posted: Sun Nov 01, 2009 2:54 pm

Guest

On Nov 1, 2:18 am, "The Boss" <use... at (no spam) No.Spam.Please.invalid> wrote:

Quote:

Vladimir M. Zakharychev wrote:
On Oct 31, 1:50 pm, Pedda <hemanth.6... at (no spam) 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: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...

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

ErikYkema...

Posted: Mon Nov 09, 2009 9:14 am

Guest

On 31 okt, 11:50, Pedda <hemanth.6... at (no spam) gmail.com> wrote:

Quote:

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

Posted: Mon Nov 16, 2009 2:05 pm

Guest

On Oct 31, 9:00 am, Mark D Powell <Mark.Powe... at (no spam) hp.com> wrote:

Quote:

On Oct 31, 7:58 am, "Vladimir M. Zakharychev"

vladimir.zakharyc... at (no spam) gmail.com> wrote:
On Oct 31, 1:50 pm, Pedda <hemanth.6... at (no spam) 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 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...

Posted: Mon Nov 16, 2009 2:13 pm

Guest

On Nov 9, 1:14 am, ErikYkema <erik.yk... at (no spam) gmail.com> wrote:

Quote:

On 31 okt, 11:50, Pedda <hemanth.6... at (no spam) 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...

Posted: Mon Nov 16, 2009 4:03 pm

Guest

On Mon, 16 Nov 2009 06:05:31 -0800, Pedda wrote:

Quote:

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

Page 1 of 1

All times are GMT The time now is Thu Dec 10, 2009 8:23 am Computers Forum Index