OraFAQ Forum: Forms » How to create sequence.

Home » Developer & Programmer » Forms » How to create sequence.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to create sequence. [message #662532]

Fri, 05 May 2017 03:58

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.

Report message to a moderator

Re: How to create sequence. [message #662536 is a reply to message #662532]

Fri, 05 May 2017 04:06

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

https://docs.oracle.com/database/121/SQLRF/statements_6017.htm#SQLRF01314

Report message to a moderator

Re: How to create sequence. [message #662546 is a reply to message #662536]

Fri, 05 May 2017 04:48

cookiemonster
Messages: 13963
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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: How to create sequence. [message #662581 is a reply to message #662546]

Sat, 06 May 2017 04:06

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.

Report message to a moderator

Re: How to create sequence. [message #662582 is a reply to message #662569]

Sat, 06 May 2017 04:07

mashhoodnasir
Messages: 26
Registered: May 2017

Junior Member

Em using Oracle 10g

Report message to a moderator

Re: How to create sequence. [message #662607 is a reply to message #662532]

Sat, 06 May 2017 13:25

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.

Report message to a moderator

Re: How to create sequence. [message #662608 is a reply to message #662607]

Sat, 06 May 2017 14:45

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: How to create sequence. [message #662614 is a reply to message #662607]

Mon, 08 May 2017 02:50

cookiemonster
Messages: 13963
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.

Report message to a moderator

Re: How to create sequence. [message #662628 is a reply to message #662614]

Mon, 08 May 2017 05:40

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.

Report message to a moderator

Re: How to create sequence. [message #662630 is a reply to message #662628]

Mon, 08 May 2017 05:44

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why don't you just assign the emp_no when they save - pre-insert?

Report message to a moderator

Re: How to create sequence. [message #662643 is a reply to message #662628]

Mon, 08 May 2017 13:01

joy_division
Messages: 4963
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.

Report message to a moderator

Previous Topic:	FRM-32083
Next Topic:	Update Field if the Checkbox is Checked

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 18:03:54 CST 2025