Home » Developer & Programmer » Forms » How to generate numbers automatically in multirecord table using Oracle Form 6i (Oracle Form 6i, XP)
How to generate numbers automatically in multirecord table using Oracle Form 6i [message #596972] Sat, 28 September 2013 04:31 Go to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
I have two control block i.e. class_register and student_info. In which student_info is multi-record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table.
Now I want to generate stud_roll_no automatically until the last record is present.
Can any one tell me how to do this....
Re: How to generate numbers automatically in multirecord table using Oracle Form 6i [message #596986 is a reply to message #596972] Sat, 28 September 2013 10:13 Go to previous messageGo to next message
Littlefoot
Messages: 19464
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use :SYSTEM.TRIGGER_RECORD or :SYSTEM.CURSOR_RECORD variables within the WHEN-NEW-RECORD-INSTANCE trigger. For example:
:your_block.stud_roll_no := :system.trigger_record;
Re: How to generate numbers automatically in multirecord table using Oracle Form 6i [message #596989 is a reply to message #596986] Sat, 28 September 2013 11:27 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Thanks little foot. Its working fine as I moved to the next record. But as I want to generate roll no for all students. So I created procedure to generate roll no and it also working but additional record get created. Here is the procedure.
PROCEDURE generate_roll_no IS
	last_rec	NUMBER;
	first_rec	NUMBER;
	v_check		NUMBER;
BEGIN
	GO_BLOCK('STUDENT_INFO');
	LAST_RECORD;
	last_rec	:=	:SYSTEM.TRIGGER_RECORD;
	FIRST_RECORD;
	first_rec	:=	:SYSTEM.TRIGGER_RECORD;

	FOR i IN first_rec..last_rec
	LOOP
		:STUDENT_INFO.stud_roll_no := :SYSTEM.TRIGGER_RECORD;
		NEXT_RECORD;
	END LOOP;
END;


and here is the output screen..
./fa/11128/0/
Can you please tell me how to remove additional record..
Re: How to generate numbers automatically in multirecord table using Oracle Form 6i [message #596990 is a reply to message #596989] Sat, 28 September 2013 11:42 Go to previous messageGo to next message
Littlefoot
Messages: 19464
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When do you call that procedure (I mean, in which trigger)? I don't understand what "I want to generate roll no for all students" means. When do you want to generate these numbers? After querying existing records from a table? If so, wouldn't
:STUDENT_INFO.stud_roll_no := :SYSTEM.TRIGGER_RECORD;
put into the POST-QUERY block-level trigger do the job? Try it!

As of your question: the simplest way to fix what's happening is to add DELETE_RECORD after the END LOOP:
    NEXT_RECORD;
  END LOOP;
  delete_record;     --> here
end;
because you have one superfluous NEXT_RECORD (i.e. when you are in the last record, you move to yet another next record).

Or, include IF, such as
FOR i IN first_rec..last_rec
LOOP
  :STUDENT_INFO.stud_roll_no := :SYSTEM.TRIGGER_RECORD;

  if :system.last_record is false then
     NEXT_RECORD;
  end if;
END LOOP;
Re: How to generate numbers automatically in multirecord table using Oracle Form 6i [message #596991 is a reply to message #596990] Sat, 28 September 2013 12:22 Go to previous message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Thanks little foot. It now working 100% correctly. I tried all and its working. Thank you..
Previous Topic: Oracle D2kForms and reports to run on kiosk
Next Topic: need_horizontal_and_vertical_toolbars_for_a_block_on_different_canvases
Goto Forum:
  


Current Time: Tue Aug 19 23:54:21 CDT 2014

Total time taken to generate the page: 0.12083 seconds