Home » OrclApex » Oracle Apex Dynamic Action Execute PL/SQL Code Example

Oracle Apex Dynamic Action Execute PL/SQL Code Example

By

This Oracle Apex example shows you, how to create a dynamic action to execute the PL/SQL code. When Oracle Apex executes the PL/SQL code through the dynamic action it does not give any success or error message like the processes in Apex. So before or after executing the PL/SQL code, it is better to notify the user. For example, if you are executing the PL/SQL code to update or delete some information in the Oracle Database, then it is better to ask for the confirmation and when the user confirms, notify the user that it has been executed.

In the following example, I am updating an employee's salary by taking the input for employee id and the salary and before updating it I am asking for the confirmation if the user chooses Yes/OK then it will execute the PL/SQL code to update else not. I have the following page items, P16_EMPLOYEE_ID, P16_SALARY and a button named "setsalary". Below is the screenshot of my page items:

Oracle Apex dynamic action execute PL/SQL Code

And the following is the output of this page:

Oracle Apex page output.

Now we will create the dynamic actions for the button "setsalary".

Create Dynamic Action to Execute PL/SQL Code in Oracle Apex

Do the right-click on the button and select Create Dynamic Action option. Then create the following three TRUE actions:

1. True Action: Confirm

  • Action: Confirm
  • Settings > Text: Are you sure to update the salary for the employee &P16_EMPLOYEEID.?

2. True Action: Execute PL/SQL Code

  • Action: Execute PL/SQL Code
  • Settings > PL/SQL Code: Add the below PL/SQL Code:
Update employees
  set salary = :P16_SALARY
  where employee_id = :P16_EMPLOYEE_ID;
  • Items to Submit: P16_SALARY,P16_EMPLOYEE_ID

3. True Action: Alert

  • Action: Alert
  • Settings > Text: Salary successfully updated.

Note: The true actions number 2 and 3, will execute only when the user chooses Yes or OK for the confirmation defined in first true action (Confirm).

Save the changes and run to test.

Related tutorials: