| Oracle® Database Express Edition 2 Day Plus Application Express Developer Guide Release 2.1 Part Number B25310-01 |
|
|
View PDF |
| Oracle® Database Express Edition 2 Day Plus Application Express Developer Guide Release 2.1 Part Number B25310-01 |
|
|
View PDF |
In Oracle Application Express, you can create check boxes as form elements, or you can create check boxes in reports. Check boxes on a form work similarly to lists of values. When you define an item to be a check box, you need to provide the check box values in the List of Values section of the Item Attributes page. You define check boxes on a report using the supplied function HTMLDB_ITEM.CHECKBOX.
This tutorial illustrates the different ways you can create check boxes within the demonstration application, Sample Application and explains how to reference and process the values of checked boxes.
This section contains the following topics:
Application Builder installs with a number of demonstration applications. In this exercise you create check boxes within the demonstration application, Sample Application.
To see if Sample Application is installed:
Log in to the Database Home Page using your database username and password.
Click the down arrow on the right side of the Application Builder icon.
From the menu, select Demonstrations.
The Demonstration Applications page appears, displaying links to demonstration applications.
Locate Sample Application and check the Status column:
If the Status column displays Installed, return to the Database Home Page.
If the Status column displays Not Installed, select Install in the Action column. Follow the on-screen instructions.
As shown in Figure 5-1, the Add/Modify Products form enables users to add new products to the database.
Figure 5-1 Add/Modify Products Form in Sample Application
In this exercise you change the Product Available radio group to a check box and then change the position of the label.
Topics in this section include:
To change the Product Available radio group to a check box:
On the Database Home Page, click the Application Builder icon.
Click Sample Application.
The Application home page appears.
Navigate to the Page Definition for page 6:
In the Page field, enter 6 and click Go.
Click 6 - Add/Modify.
The Page Definition for page 6 appears. The Product Available radio group is an item named P6_PRODUCT_AVAIL.
Under Items, select P6_PRODUCT_AVAIL.
From Display As, select Checkbox.
Scroll down to List of Values.
Under List of Values:
From Named LOV, select Select Named LOV.
In List of values definition, enter:
STATIC:;Y
This list of values is a static list. In this example, the display value is null and the return value is Y. If a display value were provided, it would appear to the left of the check box and could be used in place of the label.
Scroll back to the top of the page and click Apply Changes.
The Page Definition appears.
Click the Run Page icon in the upper right corner. (See Figure 5-2).
If prompted to enter a username and password:
For User Name, enter either demo or admin.
For Password, enter your database user name in lowercase letters.
Click Login.
Sample Application appears.
Navigate to page 6. Select Edit Page 1 from the Developer Toolbar at the bottom of the page.
The Page Definition appears.
In the Page field, enter 6 and click Go.
Click the Run Page icon in the upper right corner.
As shown in Figure 5-3, note that the Product Available item now displays as a check box.
Figure 5-3 Product Available Item as a Check Box
Next, you move the check box label to the right side of the check box.
To change the position of the check box label:
Navigate to the Page Definition for page 6. Select Edit Page 6 from the Developer Toolbar.
Under Items, select P6_PRODUCT_AVAIL.
Scroll down to Label.
For Label, delete the text Product Available.
Scroll down to Lists of Values.
Under List of Values, change the List of values definition to:
STATIC:Product Available;Y
Scroll back to the top of the page and click Apply Changes.
Run the page by clicking the Run Page icon.
As shown in Figure 5-4, note that the label Product Available now displays to the right of the check box.
Figure 5-4 Product Available Label Moved to the Right
Removing the label and adding the display value to the LOV causes the Application Express engine to render the check box first and then the display value, Product Available.
In certain circumstances, you may want a check box to be enabled by default. You can accomplish this by setting the default value attribute of the check box item. One disadvantage of this approach is that you need to perform some extra steps to disable it. Because of the way you defined the Product Available check box, it is virtually impossible to disable it.
Consider the following example:
Within Sample Application, navigate to the Home page by selecting the Home tab.
From the Tasks list, click Add a New Product.
On the Add/Modify Products page:
Fill in the required fields (fields marked with an asterisk).
Disable the Product Available check box.
Click Create.
Disable the Product Available check box again and click Apply Changes.
The Products page appears.
Select the Edit icon for product you just added.
Notice that the Product Available check box is enabled even though you disabled it twice when you added the product. This behavior results from the fact:
The Product Available check box has a default value of Y.
When Product Available is NULL, it defaults to the default value Y which enables the check box.
You can alter this behavior by adding a computation that remembers the state of the check box.
To add a computation that tracks the state of the check box:
Navigate to the Page Definition for page 6. Select Edit Page 6 from the Developer Toolbar.
Under Page Processing, Computations, click the Create icon.
For Location, select Item on This Page and click Next.
For Item:
For Compute Item, select P6_PRODUCT_AVAIL.
For Computation Point, select After Submit.
For Computation Type, select Static Assignment.
Click Next.
For Computation, enter the following and click Next.
N
Note:
Since this item is a check box, set the value of the item to something other than
Y or NULL.Next, create a condition that controls when the computation executes.
On Condition:
For Condition Type, select Value of Item in Expression 1 is NULL.
In Expression 1, enter:
P6_PRODUCT_AVAIL
Because of these settings, this computation will only execute when the value of the check box item, P6_PRODUCT_AVAIL is NULL.
Click Create.
To test the new computation:
Click the Run Page icon.
Sample Application appears.
Navigate to the Home page by selecting the Home tab.
From the Tasks list, select Add a New Product.
On the Add/Modify Product page:
Fill in the required fields (fields marked with an asterisk).
Disable the Product Available check box.
Click Create.
Disable the Product Available check box again and click Apply Changes.
The Products page appears.
Select the Edit icon for product you just added.
Note that the Product Available check box is not selected.
In the next exercise, you create a multi value check box on the Product page. This check box enables users to filter the report by selecting a category.
Topics in this section include:
To a create muli value check box:
Navigate to the Page Definition for page 3:
Click the Edit Page link on the Developer Toolbar.
Enter 3 in the Page field and click Go.
The Page Definition for page 3 appears.
Under Items, click the Create icon.
For Item Type, select Check Box and click Next.
On Display Position and Name:
In Item Name, enter P3_SHOW.
For Sequence, accept the default.
For Region, select Products (1) 10.
Click Next.
For List of Values:
For Named LOV, accept the default.
For Display Null Option, select No.
In List of Values Query, enter:
SELECT distinct category a, category b FROM demo_product_info ORDER BY 1
Note that to create a multi value check box, the List of Values query needs to return more than one row.
Click Next.
On Item Attributes:
For Label, remove the default text by clicking Clear.
Accept the remaining defaults and click Next.
For Source:
For Item Source, select Static Assignment.
For Item Source Value, enter:
Audio:Computer:Phones:Video
When a multi value check box is submitted, the value of the item is a colon delimited string of values. Using this string as the source ensures all boxes are checked when Application Express engine renders the page.
Click Create Item.
To edit check box display values (or labels) to appear in bold:
Navigate to the Page Definition for page 3.
Under Items, select P3_SHOW.
Scroll down to Element.
In Form Element Option Attributes, enter:
class="fielddatabold"
Form Element Option Attributes are used exclusively for check boxes and radio buttons and control the way the Application Express engine renders individual options.
Click Apply Changes.
Next, you edit attributes so that the category check boxes display above the report. To accomplish this, you need to change the Display Point attribute of the region associated with P3_SHOW.
To change the display point of the Products region:
Navigate to the Page Definition for page 3.
Under Region, select Products.
From Display Point, select Page Template Body (3. items above region content).
Click Apply Changes.
If you ran page 3 now, you would notice the category check boxes display vertically. Next, you need to change the display so that the category check boxes display horizontally.
To alter where the category check boxes display:
Navigate to the Page Definition for page 3.
Under Items, select P3_SHOW.
Scroll down to List of Values.
In Columns, enter 4.
Scroll back to the top of the page and click Apply Changes.
The Page Definition for page 3 appears.
Click the Run Page icon in the upper right corner.
Note the check boxes display horizontally at the top of the page as shown in Figure 5-5.
Figure 5-5 Category Check Boxes Moved to Top of Page
In order for the report to be driven by the product category check boxes, you need to submit the page.
To create a button to submit the page:
Navigate to the Page Definition for page 3.
Under Buttons, click the Create icon.
For Button Region, select Products (1) 10 and click Next.
For Position, select Create a button displayed among this region's items and click Next.
For Button Attributes:
For Button Name, enter P3_GO.
In Sequence, enter 40.
Accept the remaining defaults and click Create Button.
Next, you need to create a branch to tell the Application Express engine where to go after the user clicks the Go button.
To create a branch to page 3:
Navigate to the Page Definition for page 3.
Under Branches, click the Create icon.
For Branch Point and Branch Type, accept the defaults and click Next.
For Page, select 3, accept the remaining defaults, and click Next.
Click Create Branch.
In order to generate a report based on the category check box values, you need to change the report query.
To edit the report query:
Under Regions, select Products.
Scroll down to Source.
In Region Source, locate the WHERE clause.
Change the WHERE clause to read:
WHERE p.image_id = i.image_id (+)AND instr(':'||:P3_SHOW||':',category) > 0
Scroll back to the top of the page and click Apply Changes.
Run the page by clicking the Run Page icon.
As shown in Figure 5-6, notice you can filter the report by selecting a category check box and clicking Go at the top of the page.
Figure 5-6 Product Page with Category Check Boxes and Go Button
Although the category check boxes correctly filter the content on page 3, if you deselect all the check boxes, notice the report returns all products. This behavior results from the fact that if a check box has a NULL value (that is, it is deselected), it defaults to the default value Y. The default value of Y in turn enables the check box.
You can alter this behavior by adding a computation that remembers the state of the check box.
To add a computation that tracks the state of the check box:
Navigate to the Page Definition for page 3.
Under Page Processing, Computations, click the Create icon.
For Item Location, select Item on This Page and click Next.
For Item:
For Compute Item, select P3_SHOW
For Computation Point, select After Submit.
For Computation Type, select Static Assignment.
Click Next.
For Computation:
In Computation, enter the following:
none(bogus_value)
Click Next.
Note:
A static assignment of an item needs to be something other thanY or NULL.Next, create a condition that controls when the computation executes.
On Condition:
For Condition Type, select Value of Item in Expression 1 is NULL.
In Expression 1, enter:
P3_SHOW
As a result of these settings, this computation will only execute when the value of the check box item, P3_SHOW is NULL.
Click Create.
Run the page again and deselect all the category check boxes and click Go. Notice that this time the report contains the expected result (no returned records).
In the next exercise, you add a delete check box to each row in the Products report. To accomplish this you edit the report query and make a call to HTMLDB_ITEM package.
HTMLDB_ITEM is a supplied package for generating certain form elements dynamically. In this instance, you use HTMLDB_ITEM.CHECKBOX to generate check boxes in the Products report. When the page is submitted, the values of the check boxes are stored in global package arrays. You can reference these values using the PL/SQL variables HTMLDB_APPLICATION.G_F01 to HTMLDB_APPLICATION.G_F50 based on the p_idx parameter value that was passed in.
Topics in this section include:
See Also:
"HTMLDB_ITEM" in Oracle Database Express Edition Application Express User's Guide.To edit the query to call HTMLDB_ITEM.CHECKBOX:
Navigate to the Page Definition for page 3.
Under Regions, click Products.
Scroll down to Source.
In Region Source, add the new line appearing in bold face to the query.
SELECT p.product_id edit_product, p.product_id view_product_id,
htmldb_item.checkbox(1,p.product_id) del,
p.product_name, p.product_description, p.category, p.product_avail, p.list_price,
'<img src="p?n=' || nvl(i.file_object_id,0) || '" height="50" width="50" />' img
FROM demo_product_info p, demo_images i
WHERE p.image_id = i.image_id (+)
AND instr(':'||:P3_SHOW||':',category) > 0
HTMLDB_ITEM is an Oracle Application Express supplied package you can use to generate certain form elements dynamically. Note the value passed in for p_idx in the above example is 1. You reference the check box values using the global variable HTMLDB_APPLICATION.G_F01 later on.
Scroll to the top of the page and select the Report Attributes tab.
Under Column Attributes, locate the Del column.
Click the Up arrow until the DEL column is directly beneath VIEW_PRODUCT_ID. (See Figure 5-7).
Click Apply Changes.
To add a button to submit the check box array values:
Navigate to the Page Definition for page 3.
Under Buttons, click the Create icon.
For Button Region, select Products 1 (10) and click Next.
For Position, select Create a button in a region position and click Next.
For Button Attributes:
For Button Name, enter DELETE_PRODUCTS.
For Label, enter Delete Products.
Accept the remaining defaults and click Next.
For Button Template, accept the default selection and click Next.
For Display Properties:
For Position, select Top of Region.
Accept the remaining defaults and click Next.
For Position, select Top of Region and click Next.
For Branching, select 3 Products and click Create Button.
To add a process that executes when the user clicks the Delete Products button:
Under Page Processing, Processes, click the Create icon.
For Process Type, select PL/SQL and click Next.
For Process Attributes:
For Name, enter Delete Products.
For Point, select On Submit - After Computations and Validations.
Click Next.
Enter the following PL/SQL process and click Next:
FOR i in 1..HTMLDB_APPLICATION.G_F01.count LOOP DELETE FROM demo_product_info WHERE product_id = HTMLDB_APPLICATION.G_F01(i); END LOOP;
HTMLDB_ITEM is an Oracle Application Express supplied package you can use to generate certain form elements dynamically. When a page is submitted, the values of each column are stored in global package arrays, which you can reference using the PL/SQL variable HTMLDB_APPLICATION.G_F01 to HTMLDB_APPLICATION.G_F50. In this exercise, the value passed in for product_id in EMPNO column is 1, so you reference the EMPNO column values using the global variable HTMLDB_APPLICATION.G_F01.
On Messages:
In Success Message, enter:
Product(s) deleted.
In Failure Message, enter:
Unable to delete product(s).
Click Next.
Click Create Process.
Run the page by clicking the Run Page icon in the upper right corner.
Notice the Delete Products button appears above the report as shown in Figure 5-8. To remove a product from the report, select the Del check box and click Delete Products.
Figure 5-8 Products Report with Delete Products Check Box
