Continuation of previous lecture focusing of multiple forms vs multiple canvasses. This is a major concern for distributed development, an area I don’t think it is a strong point for Oracle forms. The source code from tutorial 7:
Custom Forms, Multiple Form Approach
Form 1: Student Form--------------------
1. Goto Tools/Layout Editor2. Create 6 text items (and labels) e.g. text_item5, text_item6, text_item7, text_item8, text_item29, text_item303. Create 2 push buttons e.g. push_button20, push_button21
4. Create a PRE-FORM trigger in the Triggers of the Form :system.message_level :=25; -- :text_item4 := :global.sid;
5. Create an Alert object a. Name: NOT_NULL_ALERT b. Message: Not null error found c. Alert Style: Stop d. Button 1 Label: OK
6. Create a program unit called DISPLAY_ALERT
PROCEDURE Display_NOTNULL_ALERT IS alert_button NUMBER;BEGIN alert_button := SHOW_ALERT('NOT_NULL_ALERT'); if alert_button = ALERT_BUTTON1 then message('Insertion Cancelled'); end if;END;
7. Push Button One: a. Label Add Student b. Create a Trigger (WHEN-BUTTON-PRESSED)
declare e_NotNull Exception; Pragma Exception_Init (e_NotNull, -1400); e_DuplicateKey EXCEPTION; PRAGMA EXCEPTION_INIT (e_DuplicateKey, -1);
begin insert into student (sid, slname, sfname, SGENDER, SDOB, SCategory) values (:text_item4, :text_item5, :text_item6, :text_item7, :text_item29, :text_item30); commit; message ('record saved'); clear_form; -- :global.sid := :text_item4;
exception when e_NotNull then -- message ('Not Null Error'); Display_NOTNULL_Alert; WHEN e_DuplicateKey THEN message ('Duplicate Key'); end;
8. Push Button Two: a. Label Exit b. Create a Trigger (WHEN-BUTTON-PRESSED) EXIT_FORM;
9. Save and name the form STUDENT1.FMB
10. Run the form a. Case 1: Insert an empty record b. Case 2: Insert a new record c. Case 3: Insert an existing record
=========================================================================
Form 2: Enrol Form------------------
1. Create ENROL_CANVAS and ENROL_BLOCK
2. In Pre_Form Trigger: :SYSTEM.MESSAGE_LEVEL:=25;
3. Create the following items and buttons: items = sid and cid buttons = new student, choose offered course, and exit
4. New_Student Button: -- :global.sid := :text_item4; CALL_FORM('C:\David\student.fmx'); -- :text_item4 := :global.sid;
5. Choose Offered Course Button: leave it blank first. --go_block('enrollment_block'); --execute_query; --go_item('text_item49');
6. Exit Button: EXIT_FORM;
7. sid LOV(LOV15): go_item('text_item4'); list_values; SQL: select sid, slname, sfname from student
8. cid LOV(LOV23) go_item('text_item5'); list_values; SQL: select UCode, UTitle from Subject
9. Test the form: a. Case 1: Test the Add_Student Button with empty SID, then in the Student Form, insert a new student. Go back to Enrol Form, and see whether the SID is by default there or not? b. Case 2: Test both LOVs (SID LOV and CID LOV) c. Case 3: Choose an existing SID from the LOV, and click the button to go to the Student Form, and see whether the details of this student appear?
10. Solutions: a. For Case 1: In the Add Student Button of Student Form: begin insert into student (sid, slname, sfname, SGENDER, SDOB, SCategory) values (:text_item4, :text_item5, :text_item6, :text_item7, :text_item29, :text_item30); commit; message ('record saved'); -- pass the value of :text_item4 to global variable :global.sid := :text_item4;
clear_form;
In the PRE-FORM trigger of Student Form: :system.message_level :=25; -- get the value from global variable :text_item4 := :global.sid;
and
In New Student Button of Enrol Form: -- pass :text_item4 to global variable :global.sid := :text_item4; CALL_FORM('C:\David\student.fmx'); -- get the value from global variable :text_item4 := :global.sid;
b. For Case 3: In the PRE-FORM trigger of Student Form: :system.message_level :=25; :text_item4 := :global.sid; if :text_item4 IS NOT NULL then select sid, slname, sfname, sgender, sdob, scategory into :text_item4, :text_item5, :text_item6, :text_item7, :text_item29, :text_item30 from student where sid = :text_item4; end if;
In the Add Student Button of Student Form: Change the label to : 'ADD/EDIT STUDENT' and Change the trigger to the following
declare e_NotNull Exception; Pragma Exception_Init (e_NotNull, -1400); e_DuplicateKey EXCEPTION; PRAGMA EXCEPTION_INIT (e_DuplicateKey, -1);
-- a new alert for update student alert_button NUMBER;
begin insert into student (sid, slname, sfname, SGENDER, SDOB, SCategory) values (:text_item4, :text_item5, :text_item6, :text_item7, :text_item29, :text_item30); commit;
-- pass the value of :text_item4 to global variable :global.sid := :text_item4; message ('record saved'); clear_form;
exception when e_NotNull then -- message ('Not Null Error'); Display_NOTNULL_Alert; WHEN e_DuplicateKey THEN alert_button := SHOW_ALERT ('UPDATE_ALERT'); if alert_button = ALERT_BUTTON1 then update student set slname = :text_item5, sfname = :text_item6, sgender = :text_item7, sdob = :text_item29, scategory = :text_item30 where sid = :text_item4; -- pass the value of :text_item4 to global variable :global.sid := :text_item4;
commit; message ('Student updated'); clear_form; else message ('Update cancelled'); end if; end;
Then create UPDATE_ALERT object, with 2 buttons, and style is caution and the message is : 'Update this student?'
In the Enrol Form, change the label of Add Student to 'Add/EDIT STUDENT'
11. Test the form again: a. Case 1: Test the Add_Student Button with empty SID, then in the Student Form, insert a new student. Go back to Enrol Form, and see whether the SID is by default there or not? c. Case 3: Choose an existing SID from the LOV, and click the button to go to the Student Form, and see whether the details of this student appear?
=========================================================================
Form 2: Enrol Form; Canvas 2: Enrol_Detail_Canvas-------------------------------------------------
1. Create ENROL_DETAIL_CANVAS and ENROL_DETAIL_BLOCK Make sure that the Data Block order: ENROL_BLOCK and then ENROL_DETAIL_BLOCK
2. In the ENROL_DETAIL_CANVAS, call the Layout Editor, and create items and buttons: items = OffID buttons = Add Enrollment and RETURN
3. Add_Enrollment button: insert into enrollment (sid, oid) values (:text_item4, :text_item31); commit; --go_block('enrollment_block'); --execute_query;
4. Return button: clear_form; go_item('text_item4');
5. LOV for OffID: select UCode, OID from OFFERING where UCode = :enrol_block.text_item5
trigger: go_item('text_item49'); list_values;
6. Make sure in the Choose Offered Course Button of Enrol Block: --go_block('enrollment_block'); --execute_query; -- go to item text_item31 which is the CSECID in the Enrol_Detail_Block go_item('text_item31');7. Test the form: a. Case 1: Run the Enrol_Block and choose a CID from the LOV and then go to Enrol_Detail_Block and check the CSECID LOV b. Case 2: Run the Enrol_Block again, but leave the CID empty. Then go to Enrol_Detail_Block and check CSECID LOV.
=============================================================================Form 2: Enrol Form; Canvas 2: Enrol_Detail_Canvas; BUT WITH A NEW DATA BLOCK----------------------------------------------------------------------------
1. Use Data Block Wizard to create a new Data Block. In the same canvas.
2. Table = ENROLLMENT Property (WHERE clause) = sid = :enrol_block.text_item4
3. Make sure that the Add_Enrollment Button in the Enrol_Detail_Canvas is like this: insert into enrollment (sid, oid) values (:text_item4, :text_item31); commit;
:text_item31 := NULL;
-- go to this new data block go_block('enrollment_block'); execute_query;
4. Test the form: a. Case 1: Run the CSECID LOV and Enrol this CSECID. See whether it is added to the Enrol_Detail_Block or not. b. Case 2: Go back to Enrol Form, and choose another student that has got some enrollment details. Then go to the Enrollment Block and see whether the list of CSECID in Enrol_Detail_Block is there or not.
5. Solution for Case 2: In the Choose_Csecid Button of Enrol Block: -- refresh the Enrollment_Block go_block('enrollment_block'); execute_query; -- go to item text_item31 which is the CSECID in the Enrol_Detail_Block go_item('text_item31');=============================================================================