home *** CD-ROM | disk | FTP | other *** search
- Newsgroups: comp.databases.oracle
- Path: sparky!uunet!rde!andy
- From: andy@homebase.vistachrome.com (Andy Finkenstadt)
- Subject: Re: Referencing a SEQUENCE from a trigger...
- Message-ID: <1992Jul21.115013.14305@homebase.vistachrome.com>
- Reply-To: andy@homebase.vistachrome.com
- Organization: Vista-Chrome Incorporated
- References: <1992Jul20.195514.8085@unixg.ubc.ca>
- Date: Tue, 21 Jul 1992 11:50:13 GMT
- Lines: 50
-
- george@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@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@GEnie.geis.com
- +1 904 575 0189 work | Tallahassee FL 32310 | andy@vistachrome.com
-