Oracle Apex page output.

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:

4 thoughts on “Oracle Apex Dynamic Action Execute PL/SQL Code Example”

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

    Reply

    • 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. 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?

    Reply

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

Leave a Comment