Implementing a Dynamic SQL Button

 In Oracle APEX, implementing a "Dynamic SQL" button allows you to run SQL statements dynamically at runtime based on input or logic. This is especially useful when building flexible interfaces such as report builders, advanced filters, or admin tools. Below is a detailed explanation on how to implement a button that executes dynamic SQL.

Objective:

Create a button in Oracle APEX that executes Dynamic SQL when clicked, based on user input or page item values.

Steps to Implement a "Dynamic SQL" Button

1. Create the Button

• Go to the page in APEX.

• In the Page Designer, under Buttons, click + and create a new button, name it: BTN_DYNAMIC_SQL.

• Set Action to Defined by Dynamic Action (not Submit).

2. Create a Textarea Item for SQL Input (Optional)

If you want to let users input SQL statements:

• Add a Textarea item, e.g. P1_SQL_INPUT.

3. Create a Dynamic Action on Button Click

• In the Page Designer, right-click on the BTN_DYNAMIC_SQL and select Create Dynamic Action.

• Event: Click

• Action: Execute PL/SQL Code

4. PL/SQL Code to Execute Dynamic SQL

Here’s a sample code block:

DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    v_sql := :P1_SQL_INPUT; -- Or hardcode: 'UPDATE emp SET sal = sal * 1.1 WHERE deptno = 10';
    EXECUTE IMMEDIATE v_sql;
EXCEPTION
    WHEN OTHERS THEN
        APEX_ERROR.ADD_ERROR (
            p_message => 'Error executing SQL: ' || SQLERRM,
            p_display_location => apex_error.c_inline_with_field_and_notif
        );
END;

5. Settings in Dynamic Action

• Items to Submit: Add P1_SQL_INPUT (or any item you're using for the SQL).

• Page Items to Return: None (unless your SQL uses RETURNING INTO).

6. (Optional) Show Feedback

To show confirmation, add a True Action → Show Notification after the PL/SQL block.

The Dynamic SQL button runs an SQL statement dynamically using EXECUTE IMMEDIATE.

Implementation Steps

1. Set the Button Action to Defined by Dynamic Action.

2. Create a Dynamic Action with the following attributes: 

• Event: Click

• Action: Execute PL/SQL Code

• PL/SQL Code:

DECLARE

    v_full_name VARCHAR2(100);

BEGIN

    EXECUTE IMMEDIATE 'SELECT ' || :P1_FNAME || ' || '' '' || ' || :P1_LNAME || ' FROM DUAL' INTO v_full_name;

    apex_debug.info('Full Name: ' || v_full_name);

END;

• Affected Elements:

• Selection Type: Items

• Items: P1_FNAME, P1_LNAME

This code dynamically concatenates FName and LName and logs the result using apex_debug.info.

Example Use Cases

• Admin pages that allow direct updates.

• Executing dynamic reports.

• Batch SQL execution tools.

Security Warning

Running raw user input as dynamic SQL can be dangerous and may lead to SQL injection. Ensure this is only available to trusted users and sanitize input when possible. Use bind variables where applicable.

Let me know if you want to use this button to dynamically build and execute a SELECT statement, or to refresh a region based on user input—I can help with those patterns too.