Home » Developer & Programmer » Forms » Sequences Numbers (Windows XP DB 9i - forms 6i)
Sequences Numbers [message #358211] Mon, 10 November 2008 02:10 Go to next message
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Hi guyz,

i am stuck Mad in this sequences since long time i want to close this issue i tried a lot....but getting problem again n again...

im using all the below on tubular form.

i have a table department_ids Contain below field.
departmentid NUMBER(15)primary key,

i want to create the sequence numbers and its creating fine.the problem is it was not showing...in the field departmentid when the user entering records when he commit reacord it showing...i want the sequence will show when the user int he field while entering the records and for this im using block level triggers (Post Insert and Pre-Insert) as code below

i create a sequence

create sequence dept_seq start with 100000 increment by 1;

below is the code im using in Post Insert.

CURSOR next_deptid IS SELECT dept_seq.NEXTVAL FROM sys.dual;
OPEN next_deptid;
FETCH next_deptid INTO :department_ids.department_id;
CLOSE next_deptid;

IF :department_ids.department_id IS NULL THEN
Message('Error Generating Next Department Ids');
RAISE Form_Trigger_Failure;


and the below is in Pre-Insert
SELECT dept_seq.nextval INTO :department_ids.department_id FROM SYS.dual;

i tried this above triggers on form level also but same problem not showing the seqeunce number and then i tried When-Create-Record instead of Pre-Insert it was showing proerly if i use this. but the problem is it will fire same time when i call the form. whic i dont want, the block should be clear when i call the form.

and suppose user has create two record in the same tubular form and he dont want the second record, and delete the second record, but the sequence has created in the second record.
and when he enter the next record seqeunce will get the next number how can we get the sequence number whic he has deleted?
is there anyway that we can use the deleted seqeunce number instead of the next i want the department_ids should be in sequence.

if anyone know how to see the sequence number while entering the record and whic trigger will be use?

waiting for the good replies plz... Razz


Re: Sequences Numbers [message #358533 is a reply to message #358211] Tue, 11 November 2008 18:05 Go to previous messageGo to next message
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I suppose it is more than a year since we had this question and considering that you have correctly identified all the issues associated with trying to use an Oracle sequence for a number range without holes I will go to the effort of telling you why using an Oracle sequence is the wrong approach.

The pre-insert trigger is the way to go but you should use the 'select max(departmentid)+1 from department_ids' method not the 'nextval' method.

I am assuming that although the user can NOT save a new department during data entry, they can not delete an existing department. This is irrespective of whether the department record has any subordinate records or not.

Use the 'When-Create-Record' with 'nextval' method if you want the user to see a value in the item before doing the commit, but this method will leave holes if the user decides not to proceed. You can NOT use 'When-Create-Record' and 'max+1' as it does not support multiple users.

I have no idea why you are using the 'post-insert' trigger in this example.

Re: Sequences Numbers [message #401533 is a reply to message #358211] Tue, 05 May 2009 07:15 Go to previous message
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Thanks for solve my problem Munna Bhai

Previous Topic: Run Report with parameter
Next Topic: Email Code..But!!!!!
Goto Forum:

Current Time: Thu Oct 27 07:57:23 CDT 2016

Total time taken to generate the page: 0.10833 seconds