Home » Developer & Programmer » Forms » How to create sequence.
How to create sequence. [message #662532] Fri, 05 May 2017 03:58 Go to next message
mashhoodnasir
Messages: 26
Registered: May 2017
Junior Member
I have a block named:EMPLOYEE_DATA defined in oracle form.
i want to auto-generate emp_id. i tried following method but it didnt work:

STEP 1:
-------
CREATE SEQUENCE EMP_iD
START WITH 1001
INCREMENTED BY 1
NOMAXVALUE
NOCYCLE
NOCACHE;

STEP-2:
--------

CREATE TRIGGER AT FORM: WHEN-NEW-FORM-INSTANCE

SELECT EMP_NO.NEXTVAL INTO :EMP_NO FROM EMPLOYEE_DATA;


Kindly help me regarding this issue.
Re: How to create sequence. [message #662536 is a reply to message #662532] Fri, 05 May 2017 04:06 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
https://docs.oracle.com/database/121/SQLRF/statements_6017.htm#SQLRF01314
Re: How to create sequence. [message #662546 is a reply to message #662536] Fri, 05 May 2017 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well WNFI is the wrong trigger to use - I assume you want users to be able to create more than one record, so putting it in a trigger that runs once per form makes no sense.
Also, as in your other thread - that'll only work if employee_data contains exactly one row.
If you need to run a function through a SQL statement use dual, that's what it's there for:
SELECT EMP_NO.NEXTVAL INTO :EMP_NO FROM DUAL;

Dual is a special oracle table that always contains exactly one row. It exists to do things like this.

As for the trigger - when-new-record-instance or pre-insert would make more sense.

[Updated on: Fri, 05 May 2017 04:48]

Report message to a moderator

Re: How to create sequence. [message #662569 is a reply to message #662546] Fri, 05 May 2017 12:14 Go to previous messageGo to next message
Littlefoot
Messages: 21546
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which Oracle database version do you use? If you're on 12c (most probably not, though), you could use identity column, such as
CREATE TABLE emp
  (emp_no     NUMBER GENERATED ALWAYS AS IDENTITY,
   ename      VARCHAR2(30)
);
and stop worrying about it.
Re: How to create sequence. [message #662581 is a reply to message #662546] Sat, 06 May 2017 04:06 Go to previous messageGo to next message
mashhoodnasir
Messages: 26
Registered: May 2017
Junior Member
yes you are right i already tried with dual table and its working fine but i want to try it with EMP table which is initially blank.
Re: How to create sequence. [message #662582 is a reply to message #662569] Sat, 06 May 2017 04:07 Go to previous messageGo to next message
mashhoodnasir
Messages: 26
Registered: May 2017
Junior Member
Em using Oracle 10g
Re: How to create sequence. [message #662607 is a reply to message #662532] Sat, 06 May 2017 13:25 Go to previous messageGo to next message
mashhoodnasir
Messages: 26
Registered: May 2017
Junior Member
i used following statement with PRE-FORM trigger and its working fine.
SELECT EMP_NO.NEXTVAL INTO :EMP_NO FROM DUAL;

but i want to restrict auto generation until the new record is committed in the database. i means to say whenever i open form it shows current value and once i insert data into it it will generate new next after reopening the form.
Re: How to create sequence. [message #662608 is a reply to message #662607] Sat, 06 May 2017 14:45 Go to previous messageGo to next message
Littlefoot
Messages: 21546
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
PRE-FORM is as bad choice as WHEN-NEW-FORM-INSTANCE trigger, just as Cookiemonster already told you; besides, he specified which triggers you should use instead. How come you didn't follow his instructions?
Re: How to create sequence. [message #662614 is a reply to message #662607] Mon, 08 May 2017 02:50 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
mashhoodnasir wrote on Sat, 06 May 2017 10:06
yes you are right i already tried with dual table and its working fine but i want to try it with EMP table which is initially blank.
Don't know why you think you want to try that with emp, but you don't. If you want run a function once from a SQL (and sequence.nextval is a function) then you use dual, always.

mashhoodnasir wrote on Sat, 06 May 2017 19:25

but i want to restrict auto generation until the new record is committed in the database. i means to say whenever i open form it shows current value and once i insert data into it it will generate new next after reopening the form.
Why do you want to do that? It's not a standard way of working and it just makes your life, and the users life, harder than it needs to be.
Just set the form to allow the users to insert as many rows as they like.
Re: How to create sequence. [message #662628 is a reply to message #662614] Mon, 08 May 2017 05:40 Go to previous messageGo to next message
mashhoodnasir
Messages: 26
Registered: May 2017
Junior Member
@cookiemonster
when_new_record_instance working fine for me. but still there is a problem. whenever i run the form it shows new value. my requirement is that it will generate next number only when other fields save in database.

Re: How to create sequence. [message #662630 is a reply to message #662628] Mon, 08 May 2017 05:44 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you just assign the emp_no when they save - pre-insert?
Re: How to create sequence. [message #662643 is a reply to message #662628] Mon, 08 May 2017 13:01 Go to previous message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
mashhoodnasir wrote on Mon, 08 May 2017 06:40
@cookiemonster
when_new_record_instance working fine for me. but still there is a problem. whenever i run the form it shows new value. my requirement is that it will generate next number only when other fields save in database.

Then you are in fantasy land. A sequence is meant to provide a unique value, most likely a primary key. In no way is it to be used to have be a gapless consecutive number for multiple reasons such a transaction failure, you are running RAC, etc.
Previous Topic: FRM-32083
Next Topic: Update Field if the Checkbox is Checked
Goto Forum:
  


Current Time: Sun Mar 29 00:50:21 CDT 2020