Re: create assertion?

Hi Eugene,

I remember finishing the first day of the Oracle DBA course taught by my bearded buddy Pete Sharman many moons ago thinking, "Shit, there is no way I will ever get on top of all this, it's all too much, aaaarrrrrrrggggghhhhhh !!!"

So I do sympathise with what you must be going through ...

I have to admit mate, I have absolutely no idea what you are trying to do here. Part of me suspects you're trying to create some sort of constraint, but then I think nah, you're trying to create something that you want to grant privileges to, but then I think nah, you're trying to create an assertion (pause for a moment while I check the manuals ......., double checking .........., oh thank goodness), but then I'm thinking nah ....

So I'm left scratching my head somewhat.

Based on the evidence before me, I would recommend the following:

1. Forget about CREATE ASSERTION, it just ain't going to happen (check the SQL Reference manual, she ain't there)
2. You can't create, alter, grant, select, drop, colour in, something that doesn't exist in Oracle
3. If the intention is to create a constraint, you need to do this as part of creating the table or altering the table (check the SQL Reference manual for the correct syntax)
4. If the intention is to create a constraint based on the (so-called) logic you provided, then perhaps a trigger is what you're after (check the Application Developers Guide for further details)
5. Before proceeding much further, spend a few nights reading the Oracle Concepts and brushing up on the SQL Reference manuals. It would be time well spent.

In answer to your other question, you should only grant to users privileges they require. No more, no less. The problem with the default roles (connect, resource, dba, etc) is that you're quite likely to provide more privileges than required. That's the three Ds, Dumb, Dangerous and .... ummmmm ...., actually, that's just the two Ds.

Sorry I can't be of any more assistance.

Cheers

Richard

"eugene kim" <eugene1977_at_hotmail.com> wrote in message news:alc2u8$1dn$1_at_newsreader.mailgate.org...
> hi..
>
> create assertion price_constraint check
> (not exists
> (select * from titles
> where max(price) < price));
>
>
> i want to see if this one will work,
> max(price) is selected before new price comes in?
> by the way oracle is giving me error,
> -----------------------------------------
> create assertion price_constraint check
> *
> ERROR at line 1:
> ORA-00901: invalid CREATE command
> -----------------------------------------
>
> my first guess was 'create assertion' has to be granted.
> grant create assertion to user1;
> didn't work
>
> i used this..
>
> grant CREATE SESSION,
> CREATE TABLE,
> CREATE VIEW,
> CREATE TRIGGER,
> CREATE PROCEDURE,
> CREATE SEQUENCE to my_group_role;
>
> one more question..
> grant connect, resource to user; is better than specifying in detail?
>
> thanks a lot
>
Received on Sat Sep 07 2002 - 08:47:07 CDT