Forms: How to Select Multiple Records in a block using Record Groups

   I decided to create this demo because the question of how to select multiple records in a multi-record data block is a fairly common topic in the Oracle Technology Network (OTN) Forms discussion forum.  There are other ways of accomplishing this task, but I like to use Oracle Forms Record Groups because I ran into a situation where I needed to use a Record Group (RG) to perform a task in a form so I created a wrapper package to simplify the process of using a RG.  Over the years, the package has grown and I’ve come across situations that were easily solved by using the RG package I had created.
Disclaimer:  This demo is built using Oracle Forms 10g R2 ( but it should work with older (as well as newer) versions of Oracle Forms.  This demo also uses the new sample HR schema.  If you don’t have access to this schema, you will have to modify the code to suit your needs.
There are two ways you can use this demo form.  First, you can just dig into the sample form provided and figure out what I did or second, you can follow the steps listed to recreate the sample form.
What you’ll need to build this demo:
a. Oracle Forms 10g (Oracle Developer Suite 10g R2)
b. A valid Oracle database account
     1. The demo was built using the HR schema
c. The Craig.pll library.  This library contains the Record Group package needed by this demo form.
Note:  as I indicated above, you can use Oracle Forms 6i or any version of Oracle Forms newer than Forms 6i.  I have not tested the Record Group package with any version of Oracle Forms older than Forms 6i so you are on your own if you try to make this demo work with a version older than Forms 6i.

Let’s get started…

1. Open Oracle Forms Builder and create a new Forms module.
2. Connect to your database with a user that has SELECT privilege to the HR schema tables.
3. Create a new data block using the Data Block Wizard and select the HR.EMPLOYEES table and all columns.
     a. Let the Data Block Wizard call the Layout Wizard to display all columns in a Tabular Layout.
4. Add a new “Non-database” item as the last navigable item in the EMPLOYEES block and set the following properties of the item:
     b. Item Type:  Check Box
     c. Value when Checked:  1
     d. Value when Unchecked:  0
     e. Check Box Mapping of Other Values:  Not Allowed
     f. Initial Value:  0
     g. Database Item:  No
     h. Width:  14
     i. Prompt:  Selected
     j. Prompt Attachment Edge:  Top
     k. Prompt Alignment:  Center
5. Open the Property Pallet for the following item: JOB_ID, MANAGER_ID and DEPARTMENT_ID and scroll to the Physical set of properties and set the following properties:
     a. Visible:  No
     b. Canvas:  <NULL>
6. Select the EMPLOYEES block and set the following properties:
     a. Show Scroll Bar:  Yes
     b. Scroll Bar Canvas:  CANVAS4 (use the name of the canvas created by the Layout Wizard).
7. Now open the Layout Editor for you primary canvas and arrange the items so they appear similar to the following:
 Note:  Because the Layout Wizard added most everything to your canvas, the FRAME will have the Update Layout property set to Automatically.  The quickest way to rearrange your layout is to size the enclosing Frame to a smaller size and let Forms automatically resize the frame to fit your displayed items and the scrollbar.  At this point, I typically change the Update Layout property to Manually because I don’t always like the way Forms adjusts the layout.  This is a preference issue and not something you need to change.
8. Open the property pallet for the canvas created by the Layout Wizard and set the following properties:
     a. Name: EMPLOYEES
     b. Window: WINDOW1 (should already be set)
     c. Width:  717
     d. Height:  260
9. Now it’s time to attach the CRAIG.PLL Forms library.  Go to the Object Navigator (ON) and click on the Attached Libraries node then click on the add button (+) in the ON  toolbar. This will open the Attach Library dialog.
     a. Click the Browse… button and navigate to the directory when you stored the CRAIG.pll and select this library file.
     b. Now click the Attach button.  Forms Builder will display an alert informing you that the library contains a non-portable directory specification and will ask if you want to remove the path.  Select “Yes” to this Alert.
10. Now we need to create a new window object in the Windows node of the Object Navigator.  Open the property pallet for the new window and set the following properties:
     a. Name:  SELECTED
     b. Title:  Selected Rows
     c. Width:  313
     d. Height:  187
11. Next, we need to create the Canvas that will be used to display the selected records output.  Click on the Canvases node of the Object Navigator and click the add button.
12. Open the property pallet for the new canvas and set the following properties:
     a. Name:  SELECTED
     b. Window: SELECTED
     c. Width:  313
     d. Height:  187
13. Now, let’s create a CONTROL block to store the Selected? button and the SELECTED_ITEMS Text-Item (which will be used to display which items were selected).
     a. Select the Data Blocks node in the Object Navigator (ON) and then click the add button in the ON toolbar.
     b. The New Data Block alert will display; select the Build a new data block manually option and click the OK button.
     c. Make sure the new block is placed after the EMPLOYEES block in the Data Blocks node.
14. Set the following properties of the control block:
     a. Name:  CONTROL
     b. Database Data Block:  No
15. Add a new item (Selected Items) to the Control block and set the following properties:
     a. Name:  SELECTED_ITEMS
     b. Item Type:  Text Item
     c. Multi-line: Yes
     d. Maximum Length:  2000
     e. Database Item:  No
     f. Insert Allowed:  No
     g. Update Allowed:  No
     h. Canvas:  SELECTED
     i. X Position:  12
     j. Y Position:  15
     k. Width:  288
     l. Bevel:  Plain
     m. Prompt:  Selected Rows
     n. Prompt Attachment Edge:  Top
16. Add another new item (Selected Button) to the Control block and set the following properties:
     a. Name:  BTN_SELECTED
     b. Item Type:  Push Button
     c. Label:  Selected?
     d. Number of Items Displayed:  0
     e. Canvas:  EMPLOYEES
     f. X Position:  257
     g. Y Position:  232
     h. Width:  82
     i. Height:  16
At this point, the SELECTED canvas should look similar to the following:
Now it is time to start writing the code to make the form work.  Let’s start at the top and work our way down. 
17. Select the Triggers node in Object Navigator.  This node is at the top of the object navigator and is commonly referred to as Form or Module level triggers.
18. Click the Add button and add a WHEN-NEW-FORM-INSTANCE trigger with the following code.


            bIgnore                        BOOLEAN;


            -- RG - Name the Record Groups (RG) used to track the

            -- rows in the Employees table as well as which records

            -- are selected.

            Form_Vars.RG1_name := 'EMPLOYEES';

            Form_Vars.RG2_name := 'MULTISELECT';


            --RG - Now Initialize the RGs

            bIgnore := Rec_Group.Initialize(Form_Vars.RG1_name);

            bIgnore := Rec_Group.Initialize(Form_Vars.RG2_name);





19. Add a WHEN-WINDOW-CLOSED trigger with the following code:

IF ( Get_Window_Property('SELECTED', VISIBLE) = 'TRUE' ) THEN






20. Now we need to create three Alert objects.  These are used by the MSG package in the CRAIG.pll.  Click on the Alerts node and add the following alerts and set their properties accordingly.
     a. Stop
          i. Name:  STOP
          ii. Title:  Error
          iii. Alert Style:  Stop
          iv. Button 1 Label:  OK
          v. Button 2 Label:  null
     b. Caution
          i. Name:  CAUTION
          ii. Title:  Warning
          iii. Alert Style:  Caution
          iv. Button 1 Label:  OK
          v. Button 2 Label:  null
     c. Note
          i. Name:  Note
          ii. Title:  Information
          iii. Alert Style:  Note
          iv. Button 1 Label:  OK
          v. Button 2 Label:  null
21. Now it is time to add the remaining code needed to make the process work.  We’ll start with the Program Units. 
     a. Open the Program Units node and add a Package Specification named: FORM_VARS with the following code:


    RG1_name  VARCHAR2(30);

    RG2_name  VARCHAR2(30);


 Note:  The purpose of the FORM_VARS package specification is simply to create the equivalent of GLOBAL variables.  The benefit of using package specification variables instead of GLOBAL variables is that package specification variables use fewer resources and have greater flexibility in the DATA Types available.  I could have used Items in the CONTROL block as well, but I prefer to use a Package Specification because it is cleaner and less confusing than using a CONTROL block.
     b. Add a second Program Unit Procedure named SET_SELETED with the following code:

PROCEDURE set_selected (b_selected BOOLEAN DEFAULT TRUE) IS

   vName          VARCHAR2(9) := 'EMPLOYEES';

   vFirstItem    VARCHAR2(65) := vName||'.'||Get_Block_Property(vName,FIRST_ITEM);

   vCurrItem    VARCHAR2(65);

   vNextNavItem         VARCHAR2(65);

   vLastItem     VARCHAR2(65) := vName||'.'||Get_Block_Property(vName,LAST_ITEM);

   vNavigable   VARCHAR2(5) := 'TRUE';

   vDummy      VARCHAR2(65) := 'TRUE';

   vMsgLvl       VARCHAR2(3) := :SYSTEM.Message_Level;



            :SYSTEM.Message_Level := 25;

            vNavigable := Get_Item_Property(vFirstItem,NAVIGABLE);

            :SYSTEM.Message_Level := vMsgLvl;


            IF ( vNavigable = 'TRUE' ) THEN 



                        vDummy := NULL||vName||'.'||Get_Item_Property(vFirstItem,NEXTITEM);


                        IF ( Get_Item_Property(vDummy,NAVIGABLE) = 'TRUE' ) THEN


                        END IF;

            END IF;


            vCurrItem := :SYSTEM.CURSOR_ITEM;



            WHILE ( vCurrItem != vLastItem ) LOOP

                        vNextNavItem := vName||'.'|| Get_Item_Property(vCurrItem,NEXT_NAVIGATION_ITEM);


                        vDummy := NULL||Get_Item_Property(vCurrItem,VISIBLE);


                        IF ( Get_Item_Property(vCurrItem,VISIBLE) = 'TRUE' ) THEN

                                    IF ( b_selected ) THEN

                                                Set_Item_Instance_Property(vCurrItem,CURRENT_RECORD, VISUAL_ATTRIBUTE, 'SELECTED');


                                                Set_Item_Instance_Property(vCurrItem,CURRENT_RECORD, VISUAL_ATTRIBUTE, 'DEFAULT');

                                    END IF;

                        END IF;

                        vCurrItem := vNextNavItem;


            END LOOP BLOCK_LIST;


22. Now, we need to add the Item trigger code.  Open the EMPLOYEES data block and add the following Block level triggers and code:
     a. Trigger:  POST-QUERY


            bIgnore                        BOOLEAN := FALSE;


            --Add a record to the Record Group.

            bIgnore := rec_group.add_value(p_name=>Form_Vars.RG1_name,




Note: The Post-Query trigger ensures a record is added to the EMPLOYEES record group; which contains an entry for every record displayed in the multi-record block.
     b. Trigger:  WHEN-REMOVE-RECORD


            ignore              NUMBER;     


            -- Remove the Current Record from the Record Group (No DML occurs).

            ignore := rec_group.delete_value(Form_Vars.RG1_name,:Employees.Employee_id);


Note:  The When-Remove-Record trigger ensures when a row is cleared or deleted from the multi-record block the corresponding record is removed from the EMPLOYEES record group.
23. Now, we need to add the Item level triggers for the EMPLOYEES data block.  Open the EMPLOYEES block and add the following Item Triggers:

/* This instruction is needed just to prevent the user from navigating to the ROW_NUM column.

    I could have set the NAVIGATION property of the item, but this presented problems in the

    SET_SELECTED program unit and as this is just a demo form, I didn’t want to spend too

    much time on the development. :) */





            bIgnore                        BOOLEAN;

            nIgnore                        NUMBER;

            nRec                            NUMBER;




                        bIgnore := Rec_Group.Add_Value(p_name => Form_Vars.RG2_name

                                                                        ,p_value => :EMPLOYEES.EMPLOYEE_ID

                                                                        ,p_inline => FALSE

                                                                        ,p_record => :system.trigger_record);




                        --Check if the value exists first.

                        nRec := Rec_Group.Delete_Value(p_name => Form_Vars.RG2_name

                                                                          ,p_value => :EMPLOYEES.EMPLOYEE_ID);


            END IF;


Note:  This trigger takes care of adding or removing the selected/unselected records from the MULTISELECT record group.
24. The final Item level trigger is the CONTROL block, BTN_SELECTED When-Button-Pressed trigger.  Open the CONTROL block and add the WHEN-BUTTON-PRESSED trigger and following code to the BTN_SELECTED item.


            nCount1          NUMBER := 0;

            nCount2          NUMBER := 0;

            nRec                NUMBER := 0;

            vDummy         VARCHAR2(10);



            --Get total number of selected records.

            nCount1 := Rec_Group.Get_Count(Form_Vars.RG2_name);

            nCount2 := Rec_Group.Get_Count(Form_Vars.RG1_name);


            IF ( nCount1 > 0 ) THEN



                        IF ( :CONTROL.SELECTED_ITEMS IS NOT NULL ) THEN

                                    :CONTROL.SELECTED_ITEMS := NULL;

                        END IF;


                        --Get the Rec Num of each selected record. 


                        FOR i IN 1..nCount1 LOOP

                                    vDummy := Rec_Group.Get_Value(Form_Vars.RG2_name,i);

                                    nRec := Rec_Group.Get_Number(Form_Vars.RG1_name, Rec_Group.Get_Value(Form_Vars.RG2_name,i) );


                                    --Display value of each selected record.

                                    --Msg.Show('N','Rec#: '||nRec||' and Emp ID: '||vDummy||' selected.');                                

                                    IF ( :CONTROL.SELECTED_ITEMS IS NULL ) THEN

                                                :CONTROL.SELECTED_ITEMS := 'You selected the following records:'||CHR(10)||'Rec #: '||nRec||' and Emp ID: '||vDummy;


                                                :CONTROL.Selected_Items := :CONTROL.Selected_Items||CHR(10)||'Rec #: '||nRec||' and Emp ID: '||vDummy;

                                    END IF;


                        END LOOP SELECTED;



                        Msg.Show('S','You haven''t selected any records!');

                        RAISE Form_Trigger_Failure;                      

            END IF;


Note:  This is where the main work is performed and we loop through the selected record and match them with the record in the multi-record block.
25. Lastly, we need to create the Visual Attributes that are used by the SET_SELECTED program unit.  Open the Visual Attributes node and add the following attribute with the listed properties.
     a. DEFAULT
          i. All properties are left at their default values
          ii. In other words, we don’t set any of the values as we want the default values.
     b. SELECTED
          i. Background Color:  r75g100b75
          ii. Font Weight:  Bold
That’s it!  Just compile and run the form.  The run product should look similar to the following screen shots.
 Description of File Attachments
1.  CRAIG.pll - this is Forms PL/SQL Library that contains the Rec_Group pacakge used by this demo.
2.  How to_Select Multiple Records in a Block using an RG.doc - This demo in Microsoft Word 2003 format.
Craig Butts,
Dec 13, 2011, 9:12 AM
Craig Butts,
Dec 13, 2011, 9:12 AM