Building Dynamic Actions in Oracle Application Express 5.1

Purpose

This tutorial shows you how to create different types of Dynamic Actions on a page in your application using Oracle Application Express 5.1.

Time to Complete

Approximately 60 minutes.

Overview

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express is available with the Oracle Database, whether it's on-premises or in an Oracle Database Cloud Service.

Oracle Application Express 5.1 allows developers to create Dynamic Actions to define client-side behavior declaratively without the need to know JavaScript. Developers can create Dynamic Actions on an Item, Button, or Region of a page, just by defining the Event, Condition, Affected Elements, Action, and other properties of the Dynamic Action in the Property Editor on the Page Designer. Previously, this involved hand crafting JavaScript and AJAX but now many Dynamic Actions require almost zero coding using the Page Designer in Oracle Application Express 5.1.

In this tutorial, you create and test different types of Dynamic Actions on a Form created in your application.

Please keep in mind the following while running this tutorial:

• Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
  • Logging into Oracle Application Express in a Oracle Database Cloud Service: To do this, go to the Oracle Help Center for Cloud, and select Platform and Infrastructure. From here, select your Database Cloud Service and the Get Started page will appear.
  • Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.
• Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
• Schema: If you are accessing an Oracle Application Express workspace in Database Schema Service,  you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premises Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

What Do You Need?

Before starting this tutorial, you should:

• Have access to an Oracle Database 11g or later release, either on-premises or in a Database Cloud Service.
• Install Oracle Application Express Release 5.1 into your Oracle Database (for on-premises only).
• Download and unzip the files.zip into your working directory.
• Execute Create_Tables.sql from the extracted files, to create the required database objects.
• Provision at least one Oracle Application Express user. See Creating New User Accounts in Oracle Application Express Administration Guide.

In this section, you create a Database Application with few initial pages, which will be used for creating different Dynamic Actions in the further sections. Perform the following steps:

1. In the Application Express login page, enter the following login credentials and click Sign In:

   • Workspace: obe

   • Username: obe

   • Password: oracle

   View Image

   

2. From the Oracle Application Express home page, click the down arrow next to App Builder, and select Database Applications.

   View Image

   

3. Click Create.

   View Image

   

4. Accept the default, and click Next >.

   View Image

   

5. Enter Human Resources Application for Name, and click Next >.
   

   View Image

   

6. Click Next >.

   View Image

   

7. Accept the default, and click Next >.

   View Image

   

8. Select Application Express Accounts for Authentication Scheme, DD-MON-YYYY for Date Format, and click Next >.

   View Image

   

9. Click Create Application.

   View Image

   

10. The Human Resources Application is created with two default pages. You want to create a Form and a Report page on the employees table. Click Create Page >.

    View Image

    

11. Click the Form icon.

    View Image

    

12. Click the Report with Form on Table icon.

    View Image

    

13. Enter Employee List for Report Page Name and Employee Details for Form Page Name. Select Breadcrumb for Breadcrumb, Home (Page 1) for Parent Entry, and click Next >.

    View Image

    

14. Select Identify an existing navigation menu entry for this page for Navigation Preference, Home for Existing Navigation Menu Entry, and click Next >.

    View Image

    

15. Select EMPLOYEES (table) for Table/View Name, ensure all the columns are selected,and click Next >.

    View Image

    

16. Perform the following actions:

    • Choose Select Primary Key Column(s) for Primary Key Type
    • Select all columns to be included in the form
    • Select EMPLOYEE_ID (Number) for Primary Key Column 1
    • Choose Existing Sequence for Source for Primary Key Column1 and select EMPLOYEES_SEQ for Sequence
    • Click Create.

    View Image

    

17. Click Save and Run Page.

    View Image

    

18. If the Log In screen appears, enter your Oracle Application Express credentials, and click Log In.

    View Image

    

19. Click the Edit icon before the first row.

    View Image

    

20. In the next section, you want to make few changes to the items on this form. Click Edit Page 3 in the developer toolbar.

    View Image

    

In this section, you make few changes to the items on your Form page so that they appear meaningful and user-friendly when the Form loads. Perform the following steps:

1. In the Rendering tab, under Items, click P3_JOB_ID.

   View Image

   

2. In the property editor, under Identification, select Select List for Type. Under List of Values, select SQL Query for Type, and click the Code Editor: SQL Query icon.

   View Image

   

3. Enter the following SQL query in the Code Editor - SQL Query, and click the Validate icon.
   
   select distinct job_id d, job_id r
from employees
order by 1

   View Image

   

4. Click OK.
   

   View Image

   

5. Under List of Values, select No for Display Extra Values, and click Save and Run Page.

   View Image

   

6. Notice that the Job changed to a select list on the form. To make changes to other items on the page, click Edit Page 3 in the developer toolbar.

   View Image

   

7. In the Rendering tab, under Items, select P3_COMMISSION_PCT. In its property editor, under Label, change the label to Commission.

   View Image

   

8. In the Rendering tab, under Items, select P3_MANAGER_ID. In its property editor, under Identification, select Select List for Type. Under List of Values, select SQL Query for Type, and click the Code Editor: SQL Query icon.

   View Image

   

9. Enter the following SQL query in the Code Editor - SQL Query, and click the Validate icon.
   
   select first_name d, employee_id r
from employees
where job_id in ('MANAGER','PRESIDENT')


   View Image

   

10. Click OK.

    View Image

    

11. Under List of Values, select No for Display Extra Values, Yes for Display Null Values, enter --Select Manager-- for Null Display Value, and click Save.

    View Image

    

12. In the Rendering tab, under Items, select P3_DEPARTMENT_ID. In its property editor, under Identification, select Select List for Type. Under List of Values, select SQL Query for Type, and click the Code Editor: SQL Query icon.

    View Image

    

13. Enter the following SQL query in the Code Editor - SQL Query, and click the Validate icon.
    
    select department_name d, department_id r
from departments


    View Image

    

14. Click OK.

    View Image

    

15. Under List of Values, select No for Display Extra Values, Yes for Display Null Values, enter --Select Department-- for Null Display Value, and click Save and Run Page.

    View Image

    

16. Notice that the Commission_pct field is labelled as Commission, and the Manager and Department fields are changed to Select Lists. In the next section, you want to create a Dynamic Action on this page, click Edit Page 3 in the developer toolbar.

    View Image

    

In this section, you create a Dynamic Action that enables the Commission field on the form, only when the Job value equals SALESMAN. For all the other values of Job, the Commission field is disabled. Perform the following steps:

1. In the Rendering tab, right-click P3_JOB_ID, and select Create Dynamic Action.
   

   View Image

   

2. In the property editor of the new Dynamic Action, set the following properties as defined in the table below:

   Property Group	Property Name	Property Value	Comments
   Identification	Name	JOB EQUALS SALESMAN	-
   When	Event	Change	Whenever P3_JOB value changes, this Dynamic Action executes.
   When	Selection Type	Item(s)	Value is set by default, because the Dynamic Action is created from P3_JOB_ID under Items.
   When	Item(s)	P3_JOB_ID	Value is set by default, because the Dynamic Action is created from P3_JOB_ID under Items.
   When	Client-side Condition	Item = Value	To ensure that this Dynamic Action executes only when the value of P3_JOB changes to SALESMAN.
   When	Value	SA_MAN	To ensure that this Dynamic Action executes only when the value of P3_JOB_ID changes to SA_MAN (salesman)

   View Image

   

3. After setting the properties of the Dynamic Action, you want to define the action to be executed when the Event (Change) takes place and the Client-side Condition (Equals to SALESMAN) is True. Under JOB EQUALS SALESMAN, under True, click Show.

   View Image

   

4. In the property editor, under Identification, select Enable for Action.
   

   View Image

   

5. Under Affected Elements, ensure that Selection Type is set to Item(s), and click the List of Values: Item(s) icon for Item(s).

   View Image

   

6. You want to enable the Commission field on the form, when P3_JOB_ID equals to SALESMAN. Therefore, click P3_COMMISSION_PCT in the Pick Item(s) dialog.

   View Image

   

7. You also want to disable the Commission field on the form when P3_JOB is not equal to SALESMAN. To create the False Action, right-click FALSE, and select Create FALSE Action.

   View Image

   

8. In the property editor, under Identification, select Disable for Action.

   View Image

   

9. Under Affected Elements, ensure that Selection Type is set to Item(s), and click the List of Values: Item(s) icon for Item(s).

   View Image

   

10. Click P3_COMMISSION_PCT in the Pick Item(s) dialog.

    View Image

    

11. Notice that the properties under Execution Options are set by default. Ensure that Fire on Initialization is set to Yes, and click Save and Run Page.

    View Image

    

12. Select HR_REP for Job Id.
    

    View Image

    

13. Notice that the Commission field is automatically disabled.

    View Image

    

14. Now, select SA_MAN for Job Id.

    View Image

    

15. Notice that the Commission field is automatically enabled now. To create another Dynamic Action on the page, click Edit Page 3 in the developer toolbar.

    View Image

    

In this section, you create a Dynamic Action that sets the value of Location and Number of Employees fields in the form automatically, based on the value selected for Department field. Perform the following steps.

1. Firstly, you want to create two new items to display Location and Number of Employees values in the form. In the Rendering tab, right-click Items, and click Create Page Item.
   

   View Image

   

2. In the property editor, set the properties of the new item as per the following table, and click Save.

   Property Group	Property Name	Property Value
   Identification	Name	P3_LOCATION
   Identification	Type	Display Only
   Label	Label	Location
   Settings	Save Session State	No
   Settings	Show Line Breaks	No
   Source	Type	Item
   Source	Item	P3_LOCATION

   View Image

   

   View Image

   

3. To create another Display Only item, right-click Items, and click Create Page Item, in the Rendering tab.

   View Image

   

4. In the property editor, set the properties of the new item as per the following table, and click Save.

   Property Group	Property Name	Property Value
   Identification	Name	P3_NUM_EMPLOYEES
   Identification	Type	Display Only
   Label	Label	Total Employees
   Settings	Save Session State	No
   Settings	Show Line Breaks	No
   Source	Type	Item
   Source	Item	P3_NUM_EMPLOYEES

   View Image

   

   View Image

   

5. You can also create a Dynamic Action from the Dynamic Actions tab. Click the Dynamic Actions tab.
   

   View Image

   

6. Right-click Events, and select Create Dynamic Action.

   View Image

   

7. In the property editor of the new Dynamic Action, set the following properties as defined in the table below, and click the List of Values: Item(s) icon for Item(s).

   Property Group	Property Name	Property Value	Comments
   Identification	Name	Get Department Details	-
   When	Event	Change	To ensure that the Dynamic Action executes on a Change Event.
   When	Selection Type	Item(s)	To ensure that the Dynamic Action executes when Change Event occurs on a Item.

   View Image

   
   

8. You want this Dynamic Action to be executed when the value of the Department field changes. Therefore, select P3_DEPARTMENT_ID in the Pick Item(s) dialog.

   View Image

   

9. After setting the properties of the Dynamic Action, you want to define the action to be executed when the Event (Change) takes place. Under Get Department Details, under True, click Show.

   View Image

   

10. In the property editor, under Identification, select Set Value for Action.

    View Image

    

11. Under Settings, select SQL Statement for Set Type, and click the Code Editor: SQL Statement icon.

    View Image

    

12. Enter the following SQL query in the Code Editor - SQL Statement, and click the Validate icon.
    
    select d.location_id location,count(e.employee_id) num_employees
from departments d, employees e
where d.department_id = e.department_id(+)
and d.department_id = :P3_DEPARTMENT_ID
group by d.location_id

    View Image

    

13. Click OK.

    View Image

    

14. To ensure that the SQL statement entered above executes successfully, you have to submit P3_DEPARTMENT_ID item used as a host variable in the above query. Click the Items to Submit icon.

    View Image

    

15. Select P3_DEPARTMENT_ID.

    View Image

    

16. You want to set the value of Location field in the form, when this Dynamic Action executes. Under Affected Elements, ensure that Selection Type is set to Item(s), and click the Item(s) icon for Item(s).

    View Image

    

17. Select P3_LOCATION.

    View Image

    

18. You also want to set the value of Total Employees field in the form, when this Dynamic Action executes. Click the Item(s) icon for Item(s), again.

    View Image

    

19. Select P3_NUM_EMPLOYEES.

    View Image

    

20. Click Save and Run Page.

    View Image

    

21. Click Cancel, to open the Employee List.

    View Image

    

22. Click the Edit icon beside the first row in the Employee List.

    View Image

    

23. The Employee Details form opens for editing. Notice that the value of Location and Total Employees is automatically set based on the value of Department field on the form.

    View Image

    

24. Select IT for Department_id, and notice the values of Location and Total Employees changes automatically. In the next section, you want to create a similar Dynamic Action using PL/SQL. Click Edit Page 3 in the developer toolbar.

    View Image

    

In this section, you create a Dynamic Action that calculates and sets the value of Bonus field in the form automatically, every time the page loads. Perform the following steps.

1. Firstly, you want to create a new item to display Bonus in the form. In the Rendering tab, right-click Items, and click Create Page Item.
   

   View Image

   

2. In the property editor, under Identification, enter P3_BONUS for Name, and select Number Field for Type. Under Appearance, click the Format Mask icon for Format Mask.

   View Image

   

3. Select $5,234.10.

   View Image

   

4. Click Save.
   

   View Image

   

5. Under Source, select Item for Type, P3_BONUS for Item, and Only when current value in session state is null for Used.

   View Image

   

6. To create the Dynamic Action, click the Dynamic Actions tab.

   View Image

   

7. You want to create a Dynamic Action that executes every time the page loads. Therefore, right-click Page Load and select Create Dynamic Action.

   View Image

   

8. In the property editor, under Identification, enter Calculate Bonus for Name. Notice that the Event is already selected as Page Load.

   View Image

   

9. After setting the properties of the Dynamic Action, you want to define the action to be executed when the Event (Page Load) takes place. Under Calculate Bonus, under True, click Show.
   

   View Image

   

10. In the property editor, under Identification, select Set Value for Action. Under Settings, select PL/SQL Function Body for Set Type, and click the Code Editor: PL/SQL Function Body icon.

    View Image

    

11. Enter the following SQL query in the Code Editor - PL/SQL Function Body, and click the Validate icon.
    
    declare
l_multiplier number;
begin
-- determine multiplier based on job
case :P3_JOB_ID
When 'PU_CLERK' then l_multiplier := .1;
When 'MK_REP' then l_multiplier := .2;
When 'SA_REP' then l_multiplier := .3;
When 'AC_MGR' then l_multiplier := .4;
When 'AD_PRES' then l_multiplier := .5;
else l_multiplier := 0;
end case;
-- return bonus which is calculated by multiplying salary by multiplier
return :P3_SALARY * l_multiplier;
END;



    View Image

    

12. Click OK.
    

    View Image

    

13. To ensure that the PL/SQL code entered above executes successfully, you have to submit P3_SALARY and P3_JOB_ID items used as a host variables in the above code. Click the Page Items to Submit icon, and select P3_SALARY. Click the Items to Submit icon again, and select P3_JOB_ID also.

    View Image

    

14. You want to set the value of Bonus field in the form, when this Dynamic Action executes. Under Affected Elements, ensure that Selection Type is set to Item(s), click the List of Values: Item(s) icon for Item(s), and select P3_BONUS.  Under Execution Options, select No for Fire on Initialization, and click Save and Run Page.

    View Image

    

15. Notice that the value of Bonus is calculated and displayed in the Bonus Field as soon as the page loads. In the next section, you want to create a Dynamic Action which uses Plug-in, click Edit Page 3 in the developer toolbar.

    View Image

    

In this section, you use a Plug-in to create a Dynamic Action that sets the Style of a field based on its value. Perform the following steps:

1. To install the Plug-in as a shared component, click the Shared Components icon.
   

   View Image

   

2. Under Other Components, click Plug-ins.

   View Image

   

3. The Plug-in import file is provided with files.zip mentioned in the prerequisites section of this tutorial. Click Import.

   View Image

   

4. Click Choose File. Ensure that the File Type is Plug-in.
   

   View Image

   

5. Browse to your working directory where files.zip is downloaded, and select item_type_plugin_com_oracle_apex_starrating_modern.sql file. Click Next >.

   View Image

   

6. Click Next >.

   View Image

   

7. Click Install Plug-in.

   View Image

   

8. The Slider Plug-in is installed. In the Component Settings window, click Apply Changes. 

   View Image

   

9. Click the Edit Page 3 icon.

   View Image

   

10. In the Rendering tab, under Items, select P3_RATING. In its property editor, under Identification, select Modern Star Rating [Plug-In] for Type.

    View Image

    

11. Under Settings, enter 5 for Number of Stars, select No for Use Components Default, select fa-star for icon, and click Save and Run Page.

    View Image

    

12. In the page navigation, click Employee List to view the report.
    

    View Image

    

13. click the Edit icon next to Employee Id 100.

    View Image

    

14. You see that the employee rating is displayed using the Modern Star Rating plug-in instead of a number. Update the employee rating to 4.

    View Image

    

In this section, you create a Dynamic Action on the Save button such that when the Save button is clicked, the page is submitted and the Save button is disabled until the page refreshes. Perform the following steps:

1. Before creating the Dynamic Action, you want to see the current functionality of the form when the Apply Changes button is clicked. Click Apply Changes.
   

   View Image

   

2. The changes are saved to the database and you are returned to the Employee List. Notice that the salary of the edited row is updated with the new value. You can also notice that the few seconds before you are redirected to the Employee List, the Apply Changes button is remains enabled. Because of this, it is possible that the user can click the same button multiple times.
   
   To avoid this situation, you want to create a dynamic action on the Apply Changes button. Click the edit icon of the employee you just edited.

   View Image

   

3. Click Edit Page 3 in the developer toolbar.

   View Image

   

4. In the Rendering tab, under Region Buttons, right-click Save, and select Create Dynamic Action.
   

   View Image

   

5. In the property editor, under Identification, enter Submit Page for Name. Notice that values of Event, Selection Type and Button are already populated, under Execution Options.

   View Image

   

6. After setting the properties of the Dynamic Action, you want to define the action to be executed when the Save button is clicked. Under Submit Page, under True, click Show.

   View Image

   

7. In the property editor, under Identification, select Submit Page for Action. Under Settings, enter SAVE for Request/Button Name. Under Execution Options, select No for Fire on Initialization, and click Save.
   
   Note: The value entered in the Request/Button Name should be exactly same as the name of the button on which you are creating the Dynamic Action.

   View Image

   

8. Along with page submission, you want to disable the Apply Changes button, once it is clicked. To achieve this, you have to create another True Action for the same event. Under Submit Page, right-click True, and select Create TRUE Action.

   View Image

   

9. In the property editor, under Identification, select Disable for Action. Under Affected Elements, select Button for Selection Type, and SAVE for Button. Under Execution Options, select No for Fire on Initialization, and click Save and Run Page.

   View Image

   

10. The Employee Details form loads. Click Apply Changes.

    View Image

    

11. Notice that page gets submitted and in the mean time, the Apply Changes appears disabled.

    View Image

    

12. After the page is submitted, you are redirected to the Employee List report. Click Application<n> in the developer toolbar.

    View Image

    

In this tutorial, you have learned how to:

• Create a database application with a form and a report page
• Edit the properties of items on a form page
• Create a Enable/Disable Dynamic Action on a page item
• Create a Set Value Dynamic Action using a SQL statement
• Create a Set Value Dynamic Action using PL/SQL
• Create a Dynamic Action using a Plug-in
• Create a Dynamic Action on a button

• Managing Dynamic Actions

• Oracle Cloud Home Page