Re: Referencing a SEQUENCE from a trigger...

george_at_unixg.ubc.ca (George chow) writes:
>
>Hello,
>I've got a situation where I need to insert rows into a table in a
>ON-VALIDATE-FIELD trigger. For these rows, I need a serial no which I'm
>getting from a SEQUENCE. However, I can't seem to reference the SEQUENCE.

I've struggled with this problem as well. I tried to use the same method as the default value method and get the same error. The way to do it is to do

   SELECT V_REF_NO_SEQ.NEXTVAL into :new_ref FROM DUAL;

If you have any other 'select' statements guaranteed to return one and only one row you can "piggyback" it onto that select statement instead of generating a seperate one.

I've given this some thought and have decided that there is a rationale for this "extra" select statement: Sequence values are stored in the RDBMS, not in the form. You need to be talking to the RDBMS to get the nextval . Forms 3.0, when presented with :sequence.sequence_name.nextval converts that to a SELECT statement ON-NEW-RECORD-INSTANCE (in effect).

>I'm doing something like:
>
> new_ref := :SEQUENCE.V_REF_NO_SEQ.NEXTVAL;
>
>which gets me the error messages:
>
> PL/SQL error 49 at line 10, column 16:
> bad bind variable 'sequence.v_ref_no_seq'
>
>I've gone through the _SQL Language Reference_ and _SQL*Forms Designer
>Reference_ and I can't find anything useful there. Is the ":SEQUENCE."
>syntax only valid for use with default value?
>
>George
>george_at_unixg.ubc.ca
>

Hope that helps.

-Andy

PS - I don't work for Oracle, but I wouldn't mind. <GRIN>

-- 
Andrew Finkenstadt       | Vista-Chrome, Inc.   |    NIC Handle: AF136
GEnie Unix Sysop/Manager | The Printing House   |   ...!uunet!rde!andy
+1 904 222 2639 home     | 1600 Capital Cir SW  |  andy_at_GEnie.geis.com
+1 904 575 0189 work     | Tallahassee FL 32310 | andy_at_vistachrome.com