Re: Generating unique key numbers

On 29 Nov 97 15:39:10 GMT, "Michal Motalík" <cross_at_zl.inext.cz> wrote:

>I am developing a client/server project which will run on both 
>Interbase and Oracle. I wrote a trigger which sets unique numbers 
>from generator to primary index column on Interbase. I want to 
>write similar trigger on Oracle but i have a problem.
>
>I can not create trigger:
>
>CREATE TRIGGER tabtrig
>BEFORE INSERT ON tab
>FOR EACH ROW
>BEGIN
>  :NEW.id := tabseq.NEXTVAL;
>END;
>
>The sequence "tabseq" was created.
>I get the error PLS-00357.
>(reference to sequence is not permitted in these context)
>
>Does anybody know where the problem is?
>                   
>Michal Motalík
>cross_at_zl.inext.cz

You are not allowed to assign a value to a PL/SQL variable from a sequence. The application developers guide states: CURRVAL and NEXTVAL can be used in the following places:  VALUES clause of INSERT statements
 the SELECT list of a SELECT statement
 the SET clause of an UPDATE statement

To do what you want you have to do something like: CREATE TRIGGER tabtrig
BEFORE INSERT ON tab
FOR EACH ROW
BEGIN
  select tabseq.nextval
  into :new.id
  from sys.dual;
END; Received on Sat Nov 29 1997 - 00:00:00 CST