Re: SQL newbie

From: FC <flavio_at_tin.it>
Date: Sun, 24 Nov 2002 17:58:52 GMT
Message-ID: <wr8E9.67028$Yw.3147701@news2.tin.it>

"Miss. Michelle Heigardt" <michelleheigardt_at_hotmail.com> wrote in message news:87d7e814.0211240926.a7cc2a8_at_posting.google.com...
> Hallo, I have the following SQL for creating a table. If you can help
> me sort out what is wrong I would helpfull.
>
> create sequence testsequence;
> create table testtable(a number primary key, b number
> testsequence.nextval);
> insert into testtable values (1034);
> insert into testtable values (1045);
> insert into testtable values (32);
>
> The error that Oracle says is:
>
> create table testtable(a number primary key, b number
> testsequence.nextval)
> *
> ERROR at line 1:
> ORA-00922: missing or invalid option

Dear Michelle,
what is wrong is the specification of the sequence inside the table. Looks like you've been working with MS Access for a while. :-) To do the same in Oracle you should define a trigger (CREATE TRIGGER), use the BEFORE INSERT clause and then, inside the trigger "body" write a little piece of code like :

   select testsequence.nextval

      into :new.b
     from dual;

Dual is a special table that oracle provides for returning values in expressions that are normally available in SQL, not in PL/SQL, so it's a sort of workaround.

Do not put a COMMIT inside the trigger because you can do that only in very special situations.

So, let's recap everything.

CREATE OR REPLACE TRIGGER my_first_trigger BEFORE INSERT ON testtable
FOR EACH ROW
BEGIN
   SELECT testsequence.NEXTVAL

      INTO :NEW.b
     FROM dual;

END; Looks pretty difficult at first, but Oracle is not a toy after all.

Bye,
Flavio Received on Sun Nov 24 2002 - 11:58:52 CST