Oracle APEX Dynamic Action Tutorial with Examples

Introduction
Preparing for example
Dynamic Action example - Change
Dynamic Action example - Enable/Disable
Dynamic Action example - Set Values using SQL Statement
Dynamic Action example - Set Values using PL/SQL
Dynamic Action example - Button

1. Introduction

This document is based on:

Oracle APEX 5

2. Preparing for example

No ADS

You need to add BONUS column to EMP table if it does not already exist:

-- Add/modify columns
alter table EMP add BONUS number;

Create a blank page with the page number is 300.

Create a Region contains the parameters.

Set properties:

Identification:

Name: P300_PARAM_EMPNO
Type: Select List

List of Values:

Type: SQL Query
SQL Query: Select Ename, Empno From Emp

Next, create new Region.

Enter:

Identification:

Title: Emp Form Region
Type: Static Content

Create a few items for this Region:

Set the properties for Items:

No	Item	Label	Source (Type= Database Column)
1	P300_EMPNO	Empno	EMPNO
2	P300_ENAME	Ename	ENAME
3	P300_JOB	Job	JOB
4	P300_MGR	Manager	MGR
5	P300_HIREDATE	Hiredate	HIREDATE
6	P300_SAL	Salary	SAL
7	P300_COMM	Commission	COMM
8	P300_BONUS	Bonus	BONUS
9	P300_DEPTNO	Deptno	DEPTNO

Before the website is renderred, data should be assigned to the item, you need to create a Process in Pre-Rendering/After Header to do this.

Enter:

Identification:

Name: Fetch EMP Row
Type: Automatic Row Fetch

Setting:

Table Name: EMP
Primary Key Column: EMPNO
Primary Key Item: P300_EMPNO

The value of P300_EMPNO is pass from outside, "Fetch EMP Row" Process will query EMP table with EMPNO = :P300_EMPNO at a time before the page is renderred, and assign values to the other items.

When you run this page, it has no data, the causes are :P300_EMPNO null (no value passed to it from outside).

Change properties for items:

P300_JOB

Identification:

Name: P300_JOB
Type: Select List

List of Values:

Type: Static Values
Static values: STATIC:CLERK;CLERK,SALESMAN;SALESMAN,PRESIDENT;PRESIDENT,MANAGER;MANAGER,ANALYST;ANALYST

P300_MGR:

Identification

Name: P300_MGR
Type: Select List

List of Values

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

P300_HIREDATE

Identification

Name: P300_HIREDATE
Type: Date Picker

P300_DEPTNO

Identification

Name: P300_DEPTNO
Type: Select List

List of Values

Type: SQL Query
SQL Query: Select Dname,Deptno From Dept

Running the page:

3. Dynamic Action example - Change

No ADS

Create a Dynamic Action for P300 PARAM EMPNO item, when you change the value of P300_PARAM_EMPNO, Dynamic Action will submit this page.

Dynamic Action is created:

Identification

Name: Change EMPNO Param

When

Even: Change
Selection Type: Item(s)

When the conditions of the "Dynamic Action" is true, clause "TRUE" of it will be executed, whereas clause "FALSE" will be executed.

Creating a Branch, so after the Submit request will be forwarded to a specified page (in this case is the current page).

After Submit, the request will be redirected to the page number 300 (your current page), and set values for :P300_EMPNO by :P300_PARAM_EMPNO

Save and rerun your page:

The operating principle:

4. Dynamic Action example - Enable/Disable

No ADS

With the sale staffs, in addition to the salary, they will receive commission, so when you change job name (JOB) of an employee, if the JOB = 'SALESMAN', the COMM item allows to enter (Enable), otherwise you need to disable COMM item.

Creating a Dynamic Action for JOB item:

Name the newly created Dynamic Action is "Change JOB".

Identification

Name: Change JOB

When

Even: Change
Select Type: Item(s)
Item(s): P300_JOB
Condition: equal to
Value: SALES

When P300_JOB = 'SALESMAN':

Else, P300_JOB does not equal 'SALESMAN', clause in the 'FALSE' will be executed.

Rerun your page:

5. Dynamic Action example - Set Values using SQL Statement

No ADS

Create 2 items:

Identification:

Name: P300_LOCATION
Type: Display Only

Label

Label: Location

Settings:

Save Sesion State: NO

Source

Type: Null

Identification:

Name: P300_NUM_EMPLOYEES
Type: Display Only

Label

Label: Location

Settings:

Save Sesion State: NO

Source

Type: Null

Note: P300_LOCATION and P300_NUM_EMPLOYEES are items not based on columns of a table, its value is calculated at the time of "Page Load" so you need to put "Save Session State = NO".

Create Dynamic Action for P300_DEPTNO when you change department of employee, Dynamic Action will recalculate the location and number of employees of the new department and assign values to the P300_LOCATION and P300_NUM_EMPLOYEES.

This is SQL which queries location and number of employees in the new department:

Select d.Loc Location
      ,Count(e.Empno) Num_Employees
From   Dept d
      ,Emp  e
Where  d.Deptno = e.Deptno(+)
And    d.Deptno = :P300_Deptno
Group  By d.Loc

Identification

Action: Set Value

Settings

Set type: SQL Statement
SQL Statement: .....
Page Items to Submit: P300_DEPTNO

Affected Elements

Selection Type: Item(s)
Item(s): P300_LOCATION, P300_NUM_EMPLOYEES

Rerun your page:

6. Dynamic Action example - Set Values using PL/SQL

No ADS

In this example, simulating to calculate bonus for an employee, bonus is calculated by multiplying the salary to bonus coefficient of employees, when "page load", the value of BONUS will be calculated by the PL/SQL.

Creating a Dynamic Action is running at 'Page load' time.

Calculate Bonus:

Declare
   v_Multiplier Number;
Begin
   -- Determine multiplier based on job.
   Case :P300_Job
      When 'CLERK' Then
         v_Multiplier := 0.1;
      When 'ANALYST' Then
         v_Multiplier := 0.2;
      When 'SALESMAN' Then
         v_Multiplier := 0.3;
      When 'MANAGER' Then
         v_Multiplier := 0.4;
      When 'PRESIDENT' Then
         v_Multiplier := 0.5;
      Else
         v_Multiplier := 0;
   End Case;
   -- Return bonus which is calculated by
   -- Multiplying Salary My Multiplier
   Return :P300_Sal * v_Multiplier;
End;

Rerun your page:

7. Dynamic Action example - Button

No ADS

In this example, 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.