Building Dynamic Actions in Oracle Application Express 4.1

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

Click Create >.

Select Database and click Next >.

Select From Scratch and click Next >.

For Name, enter Human Resources Application. Keep the default value of From Scratch for the Create Application and click Next >.

Enter Home for Page Name and click Add Page.

You want to create an Interactive Report and a Form on the EMP table. Select Report and Form for Page Type. Select Home(1) for Subordinate to Page, EMP for Table Name, and click Add Page.

The Report and Form pages were created. You want to change the Name of the Report page. Select the Emp link for the Report.

Change the Page Name to Employee List and click Apply Changes.

You also want to change the Name of the Form page. Select the Emp link for the Form.

Change the Page Name to Employee Details and click Apply Changes.

You are now ready to continue to the next wizard page, click Next > .

Accept the default and click Next >.

Accept the default and click Next >.

Select a Date Format for your Application and click Next >.

Select Theme 1 and click Next >.

Click Create.

Your application was created successfully. Click Run Application.

Because you chose the default of Application Express Authentication, you need to login to the new application using the same login you use to develop. Enter your credentials and click Login.

Select Employee List.

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

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.

In the Page Rendering area under Regions, locate the P3_JOB item under the Employee Details region. Right click P3_JOB and select Edit.

Click the Select List quick link for Display As.

Click the List of Values tab.

Select No for Display Extra Values and enter the following SQL statement in the List of values definition area and click Apply Changes.

select distinct job d, job r from emp order by 1

To test the change you just made, click Run.

Notice that Job is now a select list. You want to make some additional changes. Click Edit Page 3 from the developer toolbar.

You want to change many of the item labels. Right click Items and select Edit All.

Change the following Prompts for each of the items below and click Apply Changes.

Click the Edit Page 3 icon.

You also want to change the Manager (currently a number) to a list of Manager Names. Right click P3_MGR and select Edit.

Select Select List for Display As and click the List of Values tab.

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 Apply Changes.

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

You want to change the Deptno to a list of Department Names. Right-click P3_DEPTNO and select Edit.

Select Select List for Display As and click the List of Values tab.

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 Apply Changes.

Select dname d, deptno r from dept

To see all the changes you made, click Run.

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.

Right click P3_JOB and select Create Dynamic Action.

Select Standard and click Next >. Note that a Standard dynamic action allows you to create a dynamic action that will fire when a field's value changes, and will subsequently 'Show', 'Hide', 'Enable', or 'Disable' other page items.

Enter JOB EQUALS SALESMAN for Name and click Next >.

You want the Dynamic Action to be fired when the Job item is equal to SALESMAN. Because you created a dynamic action on the P3_JOB item, the values for Selection Type, Item(s) and Condition should already be specified. Enter SALESMAN for Value. Then click Next >.

Select Enable for Specify the True Action. This action will be invoked when the value of P3_JOB is equal to 'SALESMAN'. Note that the Create Opposite False Action is checked, which will create a Disable action that will be invoked when the value of P3_JOB is not equal to SALESMAN. Click Next >.

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 the Move icon to move it to the right and click Create.

To run the page, click Run.

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

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

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

Select the Display Only Item Type and click Next >.

Enter P3_LOCATION for Item Name and click Next >.

Accept the defaults and click Next >.

Select No for Show Line Breaks and click Next >.

Accept the defaults and click Create Item.

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

Ensure that Display Only item type is selected and click Next >.

Enter P3_NUM_EMPLOYEES for Item Name and click Next >.

Enter Number of Employees for Label and click Next >.

Select No for Show Line Breaks and click Next >.

Click Create Item.

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.

Select the Advanced dynamic action type and click Next >. Note that the Advanced option allows you to create a more complex native dynamic action such as 'Add Class' or 'Set Value', where you can specify different event types such as 'Click' or 'After Refresh' and also utilize plug-in dynamic actions, that are installed in your application.

Enter GET DEPARTMENT INFORMATION for Name and click Next > .

The Event, Selection Type and Item(s) fields are prepopulated. Click Next >. Note that you don't want to specify a condition because you want the dynamic action to always be invoked.

Select Set Value for Action, select SQL Statement for Set Type, enter the following SQL Statement in the area provided, and enter P3_DEPTNO for Page Items to Submit. Click Next >.

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) and click Create.

To run the page, click Run.

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.

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

Select Number Field and click Next >.

Enter P3_BONUS for Item Name and click Next > .

Accept the defaults and click Next >.

Select the up arrow next to the Format Mask field.

Select the $5,234.10 link.

Click Next >.

Accept the defaults and click Create Item.

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

Select Advanced and click Next >.

Enter CALCULATE BONUS for Name and click Next > .

The Event and the Selection Type are prepopulated. Enter P3_JOB, P3_SAL for Item(s) and click Next >.

Select Set Value for Action, deselect Fire On Page Load, and select PL/SQL Function Body for Set Type. 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 and click Next >.

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;

Select Items for Select Type and enter P3_BONUS for Item(s). Click Create.

You can now run the page to test the dynamic action. Click Run.

The bonus is calculated automatically.

Change the Job to CLERK. Alternatively, you could change the value of the Salary which would also invoke the dynamic action. Notice that the bonus was recalculated automatically. Select the Edit Page 3 link in the developer toolbar.

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

Under User Interface, select Plug-ins.

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

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

Click Next >.

The file was successfully imported. Click Next >.

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

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

You want to change the P3_SAL item to use the slider plugin you just imported. Right-click the P3_SAL item and select Edit.

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 Apply Changes.

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

Select Advanced and click Next >.

Enter SALARY HIGH for Name and click Next >.

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. Then click Next >.

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. Click Next >.

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. Click Next >.

Select DOM Object 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.

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.

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

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.

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.

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.

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.

Click Edit Page 3 from the developer toolbar.

Right-click the SAVE button and select Create Dynamic Action.

Enter SUBMIT PAGE for Name and click Next >.

The Event, Selection Type and Item(s) fields are prepopulated. Click Next >.

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

Click Create. When a dynamic action is created on a button, the button's Action property is automatically changed to "Defined by Dynamic Action."

Click Run to see how the dynamic action works.

Make some changes to the form, for example change the salary value.

Click Apply Changes.

Notice that the page is grayed out. A processing bar is also displayed, which you can turn off if you do not want that to display.

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