Oracle Apex page output.

Oracle Apex Dynamic Action Execute PL/SQL Code Example

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

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:

This Post Has 4 Comments

  1. atef abuarida September 23, 2020

    Dear,

    iam new in oracle apex , i try dynamic action exactly as you explain by the result did not displayed , is there setting for screen mode to display dynamic action result ?

    thank you very much in advance

    1. Vinish Kapoor September 24, 2020

      In the PL/SQL code, you can set an item value to display the result. Check the following example:

      apex_util.set_session_state('P2_MYITEM', 'a new value');

      Now specify the item P2_MYITEM in the Returned Item property.

  2. sowmya May 14, 2021

    Hi, I need your help on a dynamic action.
    I have 4 button and 4 check boxes created.
    onclick of first button first check box should be checked and others are unchceked and on click of scond button it should referesh and 2 second chcek box should be clicked and rest all unchceked and so on..

    how can i write dynamic action on this?

    1. Vinish Kapoor May 16, 2021

      Create a dynamic action for each button and create 4 set value action for each dynamic action.

      For example, for button 1, create a set value for checkbox with value Y and create other 3 set value for checkbox to N.

      Similiar way create other 3 dynamic actions.

      Also, make sure, to set the value for checkbox according to your configuration. For example, if you have set Y for checked and N for unchecked then the above method will work.

Comments are closed.