Oracle Application Express 18: Creating Dynamic Actions

Before You Begin

This 20-minute tutorial shows you how to create different types of Dynamic Actions on a page in your application in Oracle Application Express 18.2 or later.

Background

Oracle Application Express allows you to create Dynamic Actions to define client-side behavior without the need to know JavaScript. You 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.

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

What Do You Need?

To complete this tutorial, you need to:

• Access to Oracle Application Express 18.2 or later

Accessing your Development Environment

How you sign in and access Oracle Application Express depends upon where Oracle Application Express resides. Oracle Application Express may reside in a local on-premises Oracle Database or in a hosted environment, such as the Oracle Cloud. The sign in credentials you use to sign in differ depending upon the installation type.

• Free Workspace: Give Oracle Application Express a test run by signing up for a free workspace. To request an evaluation workspace, go to apex.oracle.com, and click Get Started for Free.
• Oracle Cloud: Develop and deploy applications without worrying about infrastructure, repair, and downtime. Oracle Application Express is available in Exadata Express Cloud Service and Database Cloud Service. However, you need to manually customize your databases to install and enable Oracle Application Express. To learn more, see Oracle Database Cloud Service.
• Oracle Application Express On-premises: Install Oracle Application Express directly within any Oracle Database and then sign in to your workspace using your sign in credentials. For details on your sign in credentials, contact your administrator or see Oracle Application Express Installation Guide.
• Oracle Application Express Pre-Built VM: Install a Pre-Built Virtual Machine (VM) which includes an Oracle Database and Oracle Application Express 18.2. To learn more, see Hands-On Lab.
  Once the VM is installed, start the VM:
• Click the big red circle labeled Start.
• Click the APEX shortcut, or enter the following URL: http://localhost:8080/ords/f?p=4550:1.
• When prompted to sign in, enter the sign in credentials (unless given other credentials to use):
• Workspace: obe
• Username: obe
• Password: oracle

Note your Application ID may be different when compared to the screenshots in this tutorial. Your Application ID is assigned automatically when you create the application.

Install Sample Dataset

To install the sample dataset:

1. In the Oracle Application Express Workspace home page, click SQL Workshop.   
2. Select Utilities and click Sample Datasets.
3. On the Sample Datasets page, find the dataset HR Data and click Install. If the sample dataset is already installed, then click Update.
   
   

   

4. In the Manage Sample Dataset page, choose appropriate values for Dataset, Language, and Schema , or leave them at their default values. Click Next. The Load Sample Dataset page opens displaying the database object that will be installed. Click Install Dataset.

   

5. The Load Sample Dataset - Results page appears. At this point, you can create an application or exit. Click Create Application.
   

   

6. The Create Application Wizard appears. It displays all the pages containing different components built on the dataset.

   

7. Click Create Application to create the HR application.

   

8. On the Demonstation-HR application home page, click Run Application. If prompted for a Sign in page, enter your workspace username and password.
   

   

9. The Demonstration - HR application appears.  From the navigation menu on the left, select Employees.
   

   

10. The Employees page appears. Click the Edit icon on any row to view the Employee form.
    

    

    

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

Update the Employees Form

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, select P8_COMMISSION_PCT. In its property editor, under Label, enter Commission for Label.

   

2. In the Rendering tab, under Items, select P8_DEPARTMENT_ID. In its property editor, under Label, enter Department for Label.



3. Click Save and navigate back to page 7 using the Page Selector at the top of the Page Designer window.
   

   

4. On page 7, click Save and Run Page icon. The Employees page appears.  Click the Edit icon on any row to view the changes in the Employee form. Notice that all the field labels are changed on the form.

   

Create an Enable/Disable Declarative Dynamic Action

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 P8_JOB_ID, and select Create Dynamic Action.

   

2. In the property editor, set the following properties:
   • Under Identification: Name- JOB EQUALS SALESMAN
     
   • Under When:
   • Event: Change
   • Selection Type: Select Item(s)
   • Items: Select P8_JOB_ID
   • Under Client-side Condition:
   • Type: Item=Value
   • Item: P8_JOB_ID
   • Value: SA_MAN

   

3. After setting the properties of the Dynamic Action, you want to define the action to be executed when the Event takes place and the Condition is True. Under JOB EQUALS SALESMAN, under True, click Show.
4. In the property editor, set the following properties:
   • Under Identification: Action- Enable
   • Under Affected Elements: Selection Type- Item(s)
   • Click the List of Values: Item(s) icon for Item(s) and choose P8_COMMISSION_PCT in the Pick Item(s) dialog.

   

5. You also want to disable the Commission field on the form when P8_JOB_ID is not equal to SALESMAN. To disable the filed, right-click Enable, and select Create Opposite Action.

   

6. In the property editor, set the following properties:
   • Under Identification, select Disable for Action.
     
   • Under Affected Elements, ensure that Selection Type is set to Item(s)
   • Click the List of Values: Item(s) icon for Item(s). 
   • Choose P8_COMMISSION_PCT in the Pick Item(s) dialog.
   • Ensure that Fire on Initialization is set to Yes.
   • Click Save.

   

7. Navigate back to page 7 using the Page Selector at the top of the Page Designer window. Click Save and Run Page icon. The Employees page appears.  Open the Employee form and select Human Resource Representative for Job. Notice that the Commission field is automatically disabled.

   

8. Now, select Sales Manager for Job. Notice that the Commission field is automatically enabled now. To create another Dynamic Action on the page, click Edit Page 8 in the developer toolbar.

   

Create a Set Value Dynamic Action with a SQL Statement

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. To create two new items to display Location and Number of Employees values in the form, right-click Items, and click Create Page Item in the Rendering tab.
   

   

2. In the Page Items property editor, set the following properties:
   • Under Identification:
   • Name: Enter P8_NUM_EMPLOYEES
   • Type: Select Display Only
   • Under Settings:
     
   • Save Session State: Select No
   • Show Line Break: Select No
     
   • Under Source:
   • Type: Select Item(s)
   • Item(s): Select P8_NUM_EMPLOYEES
     
   • Click Save

   

3. To create another Display Only item, right-click Items, and click Create Page Item in the Rendering tab.
4. In the Page Items property editor, set the following properties of the new item:
   • Under Identification:
   • Name: Enter P8_LOCATION
   • Type: Select Display Only
   • Under Settings:
   • Save Session State: Select No
   • Show Break Line: Select No
   • Under Source:
   • Type: Select Item
   • Item: Select P8_LOCATION
   •  Click Save.

   

5. Click the Dynamic Actions tab.

   

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

   

7. In the property editor of the new Dynamic Action, set the following properties as defined:
   • Under Identification, Name: Get Department Details
   • Under When:
   • Events: Select Change
   • Selection Type: Select Item(s)

   

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

   

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.
10. In the property editor, set the following properties:
    • Under Identification, Action: Set Value
    • Under Settings, Set Type: SQL Statement and click the Code Editor: SQL Statement icon. 
    • In the Code Editor - SQL Statement, enter the following SQL query and click the Validate icon.
      

        select l.city location, count(e.employee_id) num_employees
      from oehr_departments d, oehr_employees e, oehr_locations l
      where d.department_id = e.department_id
      and l.location_id = d.location_id
      and d.department_id = :P8_DEPARTMENT_ID
      group by l.city

    • Click OK.

    

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

    

12. Set the value of the Location and Total Employees field in the form, when this Dynamic Action executes. Therefore, under Affected Elements, set the following: 
• Selection Type: Select Item(s)
• Item(s): Select P8_LOCATION, P8_NUM_EMPLOYEES
  
13. Click Save. Navigate to page 7 and click Save and Run Page.
14. The Employees page opens. Click the Edit icon beside the first row.
15. The Employee form opens for editing. Notice that the value of Location and Total Employees is automatically set based on the value of Department (shipping) field on the form.
    

    

16. Select IT for Department, 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 8 in the developer toolbar.

    

Create a Set Value Dynamic Action with PL/SQL

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. To create the Dynamic Action, perform the following steps:

1. To create a new item to display bonus in the form, right-click Items and click Create Page Item in the Rendering tab.
   

   

2. In the property editor, set the following properties:
   • Under Identification:
     
   • Name: Enter P8_BONUS
   • Type: Enter Number Field
   • Under Appearance:
     
   • Format Mask: Click the Format Mask icon and select $5,234.10.

   

   • Under Source:
     • Type:  Item
     • Item: P8_BONUS
     • Used: Only when current value in session state is null
   • Click Save.

   

3. To create a Dynamic Action that executes every time the page loads,  click the Dynamic Actions tab then right-click Page Load and select Create Dynamic Action.
4. In the property editor, set the following properties:
   • Under Identification, Name: Enter Calculate Bonus
   • Under When, Event: Select Page Load
     

   

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

   

6. In the property editor, set the following properties:
   • Under Identification, Action: Select Set Value
   • Under Settings, Set Type: Select PL/SQL Function Body
   • Click the the Code Editor: PL/SQL Function Body icon
7. 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 :P8_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 :P8_SALARY * l_multiplier;
    END;

8. After the PL/SQL query is validated, click OK.

   

9. To ensure that the PL/SQL code entered above executes successfully, you have to submit P8_SALARY and P8_JOB_ID items used as a host variables in the above code. In the Property Editor, under Settings, enter the following:  
   
   • Click the Items to Submit icon, and select P8_SALARY.
   • Click the Items to Submit icon again, and select P8_JOB_ID 
     

   

10. To set the value of Bonus field in the form, when this Dynamic Action executes, set the following properties:
    • Under Affected Elements
    • Selection Type: Select Item(s)
    • Item(s): Select P_BONUS
    • Under Execution Options, set Fire on Initialization to No
    • Click Save and navigate to page 7 and run the page.
      

    

11. The Employees Report is displayed. Click the Edit icon beside the first row in the Employees report. 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 will learn how to create a Dynamic Action on a button. Click Edit Page 8 in the developer toolbar.

    

Create a Dynamic Action on a Button

In this section, you will create a Dynamic Action on the Save button so that when you click Save, the page is submitted and the Save button is disabled until the page refreshes. To create the dynamic action, 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.

   

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, that is the employee with Employee First Name=Adam. In the Employee form, click Edit Page 8 in the developer toolbar.

   

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

   

4. 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.

   

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

   

6. In the property editor, set the following properties:
   
   • Action: Submit Page
   • Request/Button Name: 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.
   • Fire on Initialization: No
   • Click Save.

   

7. Along with page submission, you want to disable the Apply Changes button, once you click it. To implement this, you have to create another True Action for the same event. So, under Submit Page, right-click True, and select Create TRUE Action.

   

8. In the property editor, set the following properties:
   • Action: Disable
   • Selection Type:  Button
   • Button: SAVE
     
   • Fire on Initialization: No
   • Click Save. Navigate to page 7 and click Save and Run Page icon.
     



9. The Employee form loads. Click Apply Changes. Notice that page gets submitted and in the mean time, the Apply Changes appears disabled.

   

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

    

Want to Learn More?

• Managing Dynamic Actions