Collected from the link:https://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-how-to-select-multiple-records-in-a-block
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.
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.
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 (10.1.2.0.2) 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.
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:
a. NAME: SELECTED
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:
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:
a. NAME: SELECTED
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:
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
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.
18. Click the Add button and add a WHEN-NEW-FORM-INSTANCE trigger with the following code.
|
DECLARE
bIgnore BOOLEAN;
BEGIN
-- 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);
Go_Block('EMPLOYEES');
Execute_Query;
END;
|
19. Add a WHEN-WINDOW-CLOSED trigger with the following code:
|
IF ( Get_Window_Property('SELECTED', VISIBLE) = 'TRUE' ) THEN
Hide_Window('SELECTED');
Go_Block('EMPLOYEES');
ELSE
Exit_Form;
END IF;
|
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:
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:
|
PACKAGE Form_Vars IS
RG1_name VARCHAR2(30);
RG2_name VARCHAR2(30);
END;
|
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;
BEGIN
Go_Block(vName);
:SYSTEM.Message_Level := 25;
vNavigable := Get_Item_Property(vFirstItem,NAVIGABLE);
:SYSTEM.Message_Level := vMsgLvl;
IF ( vNavigable = 'TRUE' ) THEN
Go_Item(vFirstItem);
ELSE
vDummy := NULL||vName||'.'||Get_Item_Property(vFirstItem,NEXTITEM);
IF ( Get_Item_Property(vDummy,NAVIGABLE) = 'TRUE' ) THEN
Go_Item(vDummy);
END IF;
END IF;
vCurrItem := :SYSTEM.CURSOR_ITEM;
<<BLOCK_LIST>>
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');
ELSE
Set_Item_Instance_Property(vCurrItem,CURRENT_RECORD, VISUAL_ATTRIBUTE, 'DEFAULT');
END IF;
END IF;
vCurrItem := vNextNavItem;
Go_Item(vCurrItem);
END LOOP BLOCK_LIST;
END;
|
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
a. Trigger: POST-QUERY
|
DECLARE
bIgnore BOOLEAN := FALSE;
BEGIN
--Add a record to the Record Group.
bIgnore := rec_group.add_value(p_name=>Form_Vars.RG1_name,
p_value=>:Employees.Employee_id,
p_record=>:system.trigger_record);
END;
|
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
|
DECLARE
ignore NUMBER;
BEGIN
-- Remove the Current Record from the Record Group (No DML occurs).
ignore := rec_group.delete_value(Form_Vars.RG1_name,:Employees.Employee_id);
END;
|
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:
a. ROW_NUM – WHEN-NEW-ITEM-INSTANCE
a. ROW_NUM – WHEN-NEW-ITEM-INSTANCE
|
/* 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. :) */
Next_Item;
|
b. SELECTED – WHEN-CHECKBOX-CHANGED
|
DECLARE
bIgnore BOOLEAN;
nIgnore NUMBER;
nRec NUMBER;
BEGIN
IF ( CHECKBOX_CHECKED('EMPLOYEES.SELECTED') ) THEN
--Checked.
bIgnore := Rec_Group.Add_Value(p_name => Form_Vars.RG2_name
,p_value => :EMPLOYEES.EMPLOYEE_ID
,p_inline => FALSE
,p_record => :system.trigger_record);
Set_Selected(TRUE);
ELSE
--Unchecked.
--Check if the value exists first.
nRec := Rec_Group.Delete_Value(p_name => Form_Vars.RG2_name
,p_value => :EMPLOYEES.EMPLOYEE_ID);
Set_Selected(FALSE);
END IF;
END;
|
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.
|
DECLARE
nCount1 NUMBER := 0;
nCount2 NUMBER := 0;
nRec NUMBER := 0;
vDummy VARCHAR2(10);
BEGIN
--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
Go_Item('CONTROL.SELECTED_ITEMS');
Show_Window('SELECTED',136,45);
IF ( :CONTROL.SELECTED_ITEMS IS NOT NULL ) THEN
:CONTROL.SELECTED_ITEMS := NULL;
END IF;
--Get the Rec Num of each selected record.
<<SELECTED>>
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;
ELSE
:CONTROL.Selected_Items := :CONTROL.Selected_Items||CHR(10)||'Rec #: '||nRec||' and Emp ID: '||vDummy;
END IF;
END LOOP SELECTED;
ELSE
GO_Block('EMPLOYEES');
Msg.Show('S','You haven''t selected any records!');
RAISE Form_Trigger_Failure;
END IF;
END;
|
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
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.
Download the Examples:Download the example files
0 comments:
Post a Comment