OraFAQ Forum: Forms » Sequences Numbers

Home » Developer & Programmer » Forms » Sequences Numbers (Windows XP DB 9i - forms 6i)

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

Sequences Numbers [message #358211]

Mon, 10 November 2008 02:10

*munnabhai*
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.

DECLARE
CURSOR next_deptid IS SELECT dept_seq.NEXTVAL FROM sys.dual;
BEGIN
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;
END IF;

end;

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

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

Regards

Report message to a moderator

Re: Sequences Numbers [message #358533 is a reply to message #358211]

Tue, 11 November 2008 18:05

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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.

David

Report message to a moderator

Re: Sequences Numbers [message #401533 is a reply to message #358211]

Tue, 05 May 2009 07:15

shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi

Senior Member

Thanks for solve my problem Munna Bhai

Shahzaib

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 10:41:38 CDT 2024