How Do I Use Triggering PL/SQL via APEX Dynamic Actions

 Introduction

Oracle APEX Dynamic Actions provide a flexible way to respond to user interactions without requiring full page reloads. One powerful use of Dynamic Actions is triggering PL/SQL code directly from user events like button clicks, field changes, or selections. This enables you to execute business logic, update data, or validate inputs on the server instantly, creating a smooth and interactive user experience in your application.

How Do I Use Triggering PL/SQL via APEX Dynamic Actions
Font: Arial, 14px, Plain Text

In Oracle APEX, Dynamic Actions let you create interactive behavior without writing JavaScript. One of the most powerful uses of Dynamic Actions is triggering PL/SQL code when a user interacts with the page. You can use this feature to run validations, update the database, or fetch values based on user input, all without submitting the page.

Step 1: Add a Dynamic Action
Go to Page Designer, and select the item or button you want to respond to. Under the "Dynamic Actions" section, click the Create button.

Choose:

• Event: Select the triggering event, like Click, Change, or Blur.

• Selection Type: Choose how you’re identifying the item (usually Item(s) or Button).

• Affected Elements: Choose the specific page item or button that should trigger the action.

Step 2: Add a True Action to Execute PL/SQL
Once the Dynamic Action is created, click True (which means: what happens when the condition is true), then click Add Action and choose Execute PL/SQL Code.

Step 3: Write the PL/SQL Code
Enter the PL/SQL block that should execute when the event is triggered. For example:

BEGIN
  :P1_RESULT := :P1_VALUE * 10;
END;

This example takes the value of :P1_VALUE, multiplies it by 10, and stores the result in :P1_RESULT.

Step 4: Specify Items to Submit and Return
To make PL/SQL work correctly, APEX needs to know which items to send to the server and which items will be updated.

• Page Items to Submit: Add any items whose values are used in the PL/SQL block, such as P1_VALUE.

• Items to Return: Add any items whose values are changed by the PL/SQL block, such as P1_RESULT.

Step 5: Optional – Add a Condition
You can also apply a condition to determine when the Dynamic Action should run. For example, only when the value of a field is greater than 100.

Step 6: Test Your Application
Run the page. Change the input or click the button as configured. Check if the PL/SQL block runs and updates the specified item without requiring a page submit.

Best Practices

• Always declare and use :P1_ITEM_NAME syntax for referencing APEX items.

• Make sure items used in your logic are submitted to the server.

• Use APEX_DEBUG.MESSAGE to log or troubleshoot issues in PL/SQL.

• Avoid long-running PL/SQL operations in Dynamic Actions for better performance.

Using Dynamic Actions to trigger PL/SQL in Oracle APEX allows you to create seamless, interactive experiences that combine real-time user interaction with powerful backend logic.

Conclusion

Triggering PL/SQL through APEX Dynamic Actions allows you to combine responsive front-end behavior with robust back-end processing. It helps reduce page submissions, improve performance, and deliver a more seamless experience to users. By mastering this technique, you can build applications that are dynamic, efficient, and capable of handling complex business logic with ease.

Additional Example

How Do I Use Triggering PL/SQL via APEX Dynamic Actions APEX Dynamic Actions can call PL/SQL processes for real-time interactions.

Example: Enabling a Button When a Condition is Met Create a PL/SQL Dynamic Action with the following code: DECLARE v_salary NUMBER; BEGIN SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = :P1_EMP_ID; IF v_salary > 50000 THEN APEX_UTIL.SET_SESSION_STATE('P1_ENABLE_BUTTON', 'Y'); ELSE APEX_UTIL.SET_SESSION_STATE('P1_ENABLE_BUTTON', 'N'); END IF; END;

Use this session state to enable/disable a button dynamically.