Oracle APEX Dynamic Action Tutorial

1- Introduction

This document is based on:
  • Oracle APEX 5

2- Preparing for example

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

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 was 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

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, 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

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

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

Trong ví dụ này, bạn sẽ tạo một Dynamic Action cho Button. Khi button này bị nhấn, các thay đổi trên form sẽ được lưu lại. Đồng thời button này sẽ bị vô hiệu hóa (disabled) trong khi trang đang được refresh.
Identification:
  • Button Name: APPLY_CHANGE
  • Type: Apply change
Behavior:
  • Action: Submit Page
  • Database Action: SQL UPDATE action
Create Proccess to update data.
Identification:
  • Name: Process ROW of EMP
  • Type: Automatic ROW Processing (DML)
Settings
  • Table Name: EMP
  • Primary Key Column: EMPNO
  • Primary Key Item: P300_EMPNO
  • Supported Operation: Update
  • TODO