How Do I Use Form Fields in Dynamic SQL Execution

 Introduction

In Oracle APEX, Dynamic SQL allows you to build and execute SQL statements at runtime, offering flexibility in how queries and operations are formed based on user input. By incorporating form fields (page items) into your dynamic SQL, you can create highly adaptable logic that responds directly to what the user enters or selects on the page. This approach is useful for advanced filtering, conditional execution, or building SQL statements where parts of the query change dynamically.

How Do I Use Form Fields in Dynamic SQL Execution
Font: Arial, font size: 14px, plain text

In Oracle APEX, form fields (page items) can be used inside dynamic SQL to build flexible, conditional logic based on user input. Dynamic SQL is useful when you need to construct a query or DML statement at runtime, especially when the structure or filters of the statement depend on what the user enters.

Step 1: Create Page Items
Create the necessary page items that will collect user input. For example:

• P1_TABLE_NAME – where the user enters a table name

• P1_FILTER_VALUE – where the user inputs a value to filter on

Step 2: Use PL/SQL Dynamic SQL Block
You can use EXECUTE IMMEDIATE to run dynamic SQL. Reference form fields using bind variables where possible. Example:

DECLARE
  v_sql   VARCHAR2(4000);
BEGIN
  v_sql := 'DELETE FROM ' || DBMS_ASSERT.simple_sql_name(:P1_TABLE_NAME) ||
           ' WHERE status = :1';

  EXECUTE IMMEDIATE v_sql USING :P1_FILTER_VALUE;
END;

Step 3: Use DBMS_ASSERT to Prevent Injection
Always validate dynamic parts like table or column names using DBMS_ASSERT. Never concatenate raw user input directly into SQL. DBMS_ASSERT.simple_sql_name ensures only valid SQL identifiers are used.

Step 4: Example: Building a Dynamic SELECT
This block returns a count from a table chosen by the user:

DECLARE
  v_sql   VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.simple_sql_name(:P1_TABLE_NAME);

  EXECUTE IMMEDIATE v_sql INTO v_count;

  :P1_RESULT := v_count;
END;

Step 5: Set Page Process or Dynamic Action
Add this PL/SQL block to a Page Process (e.g., when a button is clicked), or use a Dynamic Action of type "Execute PL/SQL Code" and set the page items as "Items to Submit" and "Items to Return" appropriately.

Step 6: Handle Exceptions
Wrap your dynamic SQL with exception handling to catch and respond to errors.

EXCEPTION
  WHEN OTHERS THEN
    APEX_DEBUG.MESSAGE('Error: ' || SQLERRM);
    raise_application_error(-20001, 'Invalid action requested.');

Best Practices

• Use bind variables (USING) instead of concatenating user values into the SQL.

• Sanitize object names using DBMS_ASSERT.

• Avoid exposing table or column names directly to users unless strictly necessary.

• Use APEX session state correctly by submitting items before executing dynamic SQL.

• Limit the scope and complexity of dynamic SQL to what is absolutely needed.

Using form fields with dynamic SQL gives you flexibility in Oracle APEX, allowing you to build responsive and user-driven actions securely and efficiently.

Sometimes, you need to construct SQL queries dynamically using PL/SQL.

Example: Executing a Dynamic Query with a Form Field

DECLARE  

    v_sql VARCHAR2(4000);  

BEGIN  

    v_sql := 'SELECT emp_name, department FROM employees WHERE emp_id = ' || :P1_EMP_ID;  

    EXECUTE IMMEDIATE v_sql;  

END;

• This approach is useful for dynamic filtering but should be used cautiously to prevent SQL injection.

Conclusion

Using form fields in dynamic SQL execution within Oracle APEX lets you build smart, data-driven applications that react in real time to user input. While powerful, this technique should be handled carefully to avoid SQL injection risks. By validating inputs and using bind variables wherever possible, you can safely harness the full power of dynamic SQL with page item values in your APEX applications.