Building Dynamic Actions in Oracle Application Express 5.0

Last updated: 04/17/15 01:30 pm EDT

 

Before You Begin

Purpose

In this tutorial, you have learn how to:

  • Create Database Application
  • Make Some Changes to Items on Your Page
  • Create an enable/disable standard dynamic action
  • Create a Set Value dynamic action using a SQL Statement
  • Create a Set Value dynamic action using PL/SQL
  • Use a Plugin in a dynamic action
  • Create a dynamic action on a button


Time to Complete

Approximately 60 minutes.

Introduction

What are Dynamic Actions?

In APEX, Dynamic Actions provide developers with a way to define client-side behavior declaratively without the need to know JavaScript. Using a simple wizard, developers can select a page item, a condition, enter a value, and select an action (for example, Show, Hide, Enable, and Disable). Previously, this involved hand crafting JavaScript and AJAX but now many Dynamic Actions require zero coding using the built-in wizards.

Prerequisites

Before starting this tutorial, you should have:

  • Install Oracle Database 11g or later.
  • Install Oracle Application Express Release 5.0.
  • Download and unzip the files.zip file into a working directory to use during this tutorial.
  • Create a workspace and user to perform this tutorial.
 

Creating a Database Application

To create a database application with a few initial pages, log into your application express workspace and perform the following steps:

  1. From the Oracle Application Express home page, click the down arrow next to the Application Builder tab and select Applications > Database Applications.

    alt description here

  2. Click Create >.

    alt description here
  3. For Name, enter Human Resources Application. Keep the default value of Universal Theme(42) for the Theme and click Next >.
    alt description here
  4. Click Next.
    alt description here
  5. Click Next.
    alt description here
  6. Select the DD-MON-YYYY Date Format for your application and click Next.
    alt description here
  7. Click Create Application.
    alt description here
  8. On Application Page, click Create Page button.
    alt description here
  9. You want to create an Interactive Report and a Form on the OEHR_EMPLOYEES table. Click Form for Page Type. Again click Form on a Table with Report.
    alt description here
    alt description here
  10. On Create Form and Report, Enter Employee List for Page Name and Region title . For Breadcrumb select Breadcrumb. For Parent Entry select Home. Enter Employee List for Entry Name. Click Next.
    alt description here
  11. For Table/View Name select EMP.Click Next.
    alt description here
  12. For Navigation Preference select Identify an existing navigation menu entry for this page and for Existing Navigation Menu Entry select Home. Click Next.
    alt description here
  13. Click Next.
    alt description here
  14. Click Next.
    alt description here
  15. Enter Employees Details for Page Name and Region Title click Next > .
    alt description here
  16. For Primary Key Type choose Select Primary key columns and click Next >.
    alt description here
  17. Click Next >.
    alt description here
  18. Move the all the columns and Click Next >.
    alt description here
  19. Click Next >.
    alt description here

  20. Click Create.

    alt description here

  21. Click Save and Run button.

    alt description here
  22. Enter OBE as the Username and Password and click Login.
    alt description here

  23. A report containing the list of employees is automatically created. Select the Edit ( )icon next to one of the employees to view the Form.

    alt description here
  24. The Form is displayed. In the next section, you will make some changes to the items on this page. Click Edit Page 3 in the developer toolbar.
    alt description here
 

Making Some Changes to Items on Your Page

Before you create some Dynamic Actions, you want to make some changes to the Items on your page. Perform the following steps:

  1. In the Page Item Region, locate the P3_JOB item under the Employee Details region. Click P3_JOB.

    alt description here

  2. In the Page Item region, click the Select List quick link for Type.

    alt description here

  3. In the List of Values section, select SQL Query for Type. Select No for Display Extra Values and enter the following SQL statement in the List of values definition area.
    select distinct job d, job r from emp order by 1

    alt description here
  4. To test the change you just made, click Save and Run Page.
    alt description here
  5. Notice that Job is now a select list. You want to make some additional changes. Click Edit Page 3 from the developer toolbar.
    alt description here

  6. You want to change many of the item labels. Click each item and edit the name in the Page Item section and click Save.

    Item Prompt
    P3_ENAME Name
    P3_JOB Job
    P3_MGR Manager
    P3_HIREDATE Hire Date
    P3_SAL Salary
    P3_COMM Commission
    P3_DEPTNO Department
    alt description here
  7. Click the Edit Page 3 icon. You also want to change the P3_MGR (currently a number) to a list of Manager Names. Right click P3_MGR and select Edit.
    alt description here
  8. In the Page Item, select Select List for Type.
    alt description here

  9. Select No for Display Extra Values, select Yes for Display Null Value, and enter -- Select Manager-- for Null Display Value. Enter the following SQL statement in the List of values definition area and click Save.

    Select ename d, empno r from emp where job in ('MANAGER', 'PRESIDENT')
    alt description here

  10. You want to change the Deptno to a list of Department Names. Click P3_DEPTNO.

    alt description here

  11. In the Page Item, select Select List for Type.

    alt description here

  12. Select Yes for Display Extra Values, select Yes for Display Null Value, and enter -- Select Department -- for Null Display Value. Enter the following SQL statement in the List of values definition area and click Save.

    Select dname d, deptno r from dept

    alt description here

  13. To see all the changes you made, click Save and Run Page.

    alt description here

  14. Notice the changes to the labels, and the Job, Manager and Department fields are now select lists. In the next section, you will create a Dynamic Action to enable the Commission item only when the Job is equal to SALESMAN. Select the Edit Page 3 link in the developer toolbar.

    alt description here
 

Creating an Enable/Disable Declarative Dynamic Action

You want to create a Dynamic Action that will enable the Commission item when the Job equals SALESMAN. All other Jobs will set the Commission item to Disable. Perform the following steps:

  1. Right click P3_JOB and select Create Dynamic Action.

    alt description here

  2. Enter JOB EQUALS SALESMAN for Name. You want the Dynamic Action to be fired when the P3_Job item is equal to SALESMAN. Because you created a dynamic action on the JOB item, the values for Selection Type, Item(s) and Condition should already be specified. Enter SALESMAN for Value.

    alt description here

  3. Click Show in the Page Rendering section.

    alt description here

  4. Select Enable for Specify the True Action. This action will be invoked when the value of JOB is equal to 'SALESMAN'.

    alt description here

  5. The item that will be enabled or disabled is Commission. Select Item(s) for Selection Type and select P3_COMM from the list of items and click Save.

    alt description here

  6. Right-click False Action and select Create FALSE Action.

    alt description here

  7. Select Disable for Specify the False Action. Select Item(s) for Selection Type and select P3_COMM from the list of items and click Save.

    alt description here

  8. To run the page, click Run and Save Page.

    alt description here

  9. Notice that the Commission item is disabled because the Job is not equal to SALESMAN.

    alt description here

  10. Change the Job to SALESMAN and notice that the Commission field gets enabled. Select the Edit Page 3 link in the developer toolbar.

    alt description here
 

Creating a Set Value Dynamic Action With a SQL Statement

In this section, you will create a set value Dynamic Action that will show the location of a Department and the number of employees when it is changed. Before you proceed, add LOCATION and NUM_EMPLOYEES columns to EMP table. Later perform the following steps:

  1. You first need to create a new item for Location. Under Employee Details, right click Items and select Create Page Item.

    alt description here

  2. In the  Page Item section, enter P3_LOCATION for the Item Name and select the Display Only Item Type. Select No for Show Line Breaks and click Save.

    alt description here

  3. You need to create another new item for Number of Employees. Under Employee Details, right click Items and select Create Page Item.

    alt description here

  4. In the Page Item section, enter P3_NUM_EMPLOYEES for the Item Name and select the Display Only Item Type. Select No for Show Line Breaks and click Save.

    alt description here

  5. Now you can create the Dynamic Action to set the value of the new P3_LOCATION and P3_NUM_EMPLOYEES items. You want the department's location and the number of employees to be displayed when the value of the Department changes. Right click P3_DEPTNO and select Create Dynamic Action.

    alt description here

  6. Select Set Value for Action, select SQL Statement for Set Type, enter the below SQL Statement in the area provided, and enter P3_DEPTNO for Page Items to Submit.


                                      select d.loc location,
                                      count(e.empno) num_employees from dept d, emp
                                      e where d.deptno = e.deptno(+) and d.deptno =
                                      :P3_DEPTNO group by d.loc

    This page contains the page elements the dynamic action should control. In this case, you want to set the value of the P3_LOCATION, P3_NUM_EMPLOYEES items. Select Item(s) for Selection Type and select P3_LOCATION, P3_NUM_EMPLOYEES for Item(s).


  7. To run the page, click Save and Run Page.

    alt description here

  8. The location and number of employees is displayed based on the Department specified. Change the Department name. Notice that the location and number of employees changes automatically. In the next section, you will set a value using a dynamic action but this time you will use PL/SQL instead of a SQL statement. Select Edit Page 3 in the developer toolbar.


 

Create a Set Value Dynamic Action Using PL/SQL

  1. You want to calculate the bonus that a particular employee gets based on their job and salary. In this section, you will create a new Bonus item and then a dynamic action that will calculate the bonus based on both the job and salary specified. Perform the following steps:

    Note: Add BONUS column to EMP table (incase if it is not available).

    You want to first create the Bonus item. Under the Employee Details region, right click Items and select Create Page Item.

    alt description here

  2. Enter P3_BONUS for Item Name and select Number Field for Type.

    alt description here

  3. Select the up arrow next to the Format Mask field.

    alt description here

  4. Select the $5,234.10 link.

    alt description here

  5. Click Save.

    alt description here

  6. Now you are ready to create your dynamic action. Right click Dynamic Actions and select Create Dynamic Action.

    alt description here

  7. Enter CALCULATE BONUS for Name. Select Page Load for the Event Name and click Save.

    alt description here

  8. In the Dynamic Actions page, click Show under the CALCULATE BONUS Dynamic Action.

    alt description here

  9. Select Set Value for Action, Selct No for Fire On Page Load, and select PL/SQL Function Body for Set Type.

    alt description here

  10. Enter the following PL/SQL code into the PL/SQL Function Body area, enter P3_JOB,P3_SAL in the Page Items to Submit field.

    declare
                                      l_multiplier number;
                                      begin
                                      -- determine multiplier based on job
                                      case :P3_JOB
                                      when 'CLERK' then l_multiplier := .1;
                                      when 'ANALYST' then l_multiplier := .2;
                                      when 'SALESMAN' then l_multiplier := .3;
                                      when 'MANAGER' then l_multiplier := .4;
                                      when 'PRESIDENT' then l_multiplier := .5;
                                      else l_multiplier := 0;
                                      end case;
                                      -- return bonus which is calculated by
                                      multiplying salary my multiplier
                                      return :P3_SAL * l_multiplier;
                                      END;
    alt description here

  11. Select Item(s) for Select Type and enter P3_BONUS for Item(s).

    alt description here

  12. You can now run the page to test the dynamic action. Click Save and Run Page.

    alt description here

  13. The bonus is calculated automatically. Select the Edit Page 3 link in the developer toolbar.

    alt description here
 

Using a Plug-in in a Dynamic Action

In this section, you will use a plug-in in a dynamic action. Perform the following steps:

  1. You want to import the slider plugin. Click the Shared Components icon.

    alt description here

  2. Under Other Components, select Plug-ins.

    alt description here

  3. The slider plugin has been provided for you in the files directory. Click Import.

    alt description here

  4. Click the Choose File button to locate the file. Select the item_type_plugin_com_oracle_slider.sql file in the files directory and click Next > Next.

    alt description here

  5. Make sure that your Application is selected from the drop-down list and click Install Plug-in.

    alt description here

  6. You want to use this Slider Plugin for the P3_SAL item. Click the Edit Page icon.

    alt description here

  7. You want to change the P3_SAL item to use the slider plugin you just imported. Click the P3_SAL item.

    alt description here

  8. In the Property Editor - Page Item section, select Slider [Plug-in] for Display As and enter 50000 for Maximum Value and 100 for Step. These settings configure the slider for this item. Then click Save.

    alt description here

  9. Now you are ready to create the dynamic action. Right-click the P3_SAL item and select Create Dynamic Action.

    alt description here

  10. Enter SALARY HIGH for Name and select Change [Slider] for Event, make sure P3_SAL is selected for Item(s), select greater than for Condition and enter 25000 for Value. These settings will determine when the dynamic action's true and false action will fire.

    alt description here

  11. Click Show.

    alt description here

  12. Select Set Style for Action and enter color for Style Name and red for Value. These 'True' action settings will be invoked when the salary value is greater than 25000. Select JavaScript Expression for Selection Type and enter P3_SAL_display for DOM Object and click Save. The DOM Object is case sensitive so make sure you enter it exactly as specified in the screenshot below.

    Note: If you want to change the appearance of the displayed value of the Slider you have to access it using P3_SAL_display which is the ID for that object in the DOM object tree generated by the Slider plug-in. This naming convention is specific to the Slider plug-in and could be completely different for another plug-in.
    alt description here

  13. You also need to select the action that will take place when the action is false. Select Set Style for False Action and enter color for Style Name and black for Value. These 'False' action settings will be invoked when the salary value is not greater than 25000. Select JavaScript Expression for Selection Type and enter P3_SAL_display for DOM Object and click Create. The DOM Object is case sensitive so make sure you enter it exactly as specified in the screenshot below.

    alt description here

  14. You are now ready to test the slider plug-in and dynamic action. Click Save and Run Page.

    alt description here

  15. Notice that the Salary field is a slider type. Drag the bar to the right so that it is greater than 25000 and release your mouse.

    alt description here

  16. Notice that the display value is now in red because the dynamic action was fired. You can also drag it to less than 25000 and release and you will see that it changes back to black.

    alt description here
 

Creating a Dynamic Action on a Button

In this section, you will create a dynamic action on the Apply Changes button. When this button is clicked, any changes to the form are saved. Also Save button is disabled while page refreshes. Perform the following steps:

  1. Before creating the dynamic action on the button, you will view the current functionality of the Apply Changes button. To save the changes you made to the Salary value, click the Apply Changes button.

    alt description here

  2. The changes are saved to the database and you are returned to the reports page. Notice that during the few seconds the SAVE action is being processed, it is possible to click the button again or make some other change to the form. To avoid this situation, you will create a dynamic action on the Apply Changes button. Click the edit icon of the employee you just edited.

    alt description here

  3. Right-click the Save button and select Create Dynamic Action.

    alt description here

  4. Enter SUBMIT PAGE for Name. The Event, Selection Type and Item(s) fields are prepopulated.

    alt description here

  5. Select Submit Page for Action and enter SAVE for Request / Button Name. Setting this value to be the same as the button name will ensure that the button's 'Execute Validations' attribute is respected during page processing and also that any page processing components defined to fire when this button is pressed will also still fire when expected. Click Save.

    alt description here

  6. In Dynamic actions region, select True action under Submit Page and Click Create TRUE action.

    alt description here

  7. Select Disable for Action. For Selection Type select Button. Enter SAVE for Button. Select NO for Fire on Page Load Click Save.

    alt description here

  8. Click Save and Run Page to see how the dynamic action works.

    alt description here

  9. Make some changes to the form, for example change the salary value. Click Apply Changes button

    alt description here

  10. Notice that the Apply Changes button is disabled and a process icon is also displayed, which you can turn off if you do not want that to display.

    alt description here

  11. After the changes are saved to the database, you are returned to the reports page.

    alt description here
 

Want to Learn More?

In this tutorial, you have learned how to:

  • Create Database Application
  • Make Some Changes to Items on Your Page
  • Create an enable/disable standard dynamic action
  • Create a Set Value dynamic action using a SQL Statement
  • Create a Set Value dynamic action using PL/SQL
  • Use a Plugin in a dynamic action
  • Create a dynamic action on a button

Resources

 

Credits

  • Lead Curriculum Developer: Dimpi Sarmah