How Do I Refer Form Fields in SQL Queries

How Do I Refer Form Fields in SQL Queries in Oracle APEX

In Oracle APEX, form fields—also known as page items—are commonly used to capture user input. Referencing these fields in SQL queries allows developers to build dynamic, responsive pages where data is filtered or processed based on user-entered values. Knowing how to properly reference form fields in SQL queries is essential for tasks like conditional reporting, custom validations, and dynamic data display.

Referencing Page Items in SQL Queries

Oracle APEX uses bind variables to reference form fields (page items) in SQL or PL/SQL code. The syntax to use is:

:Pn_ITEM_NAME

Where n is the page number and ITEM_NAME is the name of the item.

Example 1: Filter a Report Based on a Text Field

If you have a form item called P10_SEARCH_NAME on page 10, and you want to filter a report by name:

SELECT first_name, last_name
FROM employees
WHERE UPPER(first_name) LIKE UPPER('%' || :P10_SEARCH_NAME || '%')

This query will return results that match the value entered in P10_SEARCH_NAME.

Example 2: Use a Date Picker to Filter by Hire Date

Form item: P5_HIRE_DATE_FROM

SELECT employee_id, last_name, hire_date
FROM employees
WHERE hire_date >= :P5_HIRE_DATE_FROM

You can combine multiple form fields for complex filtering:

WHERE hire_date BETWEEN :P5_HIRE_DATE_FROM AND :P5_HIRE_DATE_TO
  AND department_id = :P5_DEPARTMENT_ID

Using Bind Variables in PL/SQL

Page items can also be referenced in PL/SQL code:

IF :P20_STATUS = 'ACTIVE' THEN
   -- do something
END IF;

Using Form Items in Dynamic Actions

Dynamic Actions can use the values of form fields in client-side JavaScript or server-side PL/SQL. For SQL-based Dynamic Actions or computations, you reference the item with the same :Pn_ITEM syntax.

Best Practices

• Use proper validation: Validate user input before using it in queries to avoid unexpected results.

• Use NVL or defaulting: Handle cases where the item may be null to avoid query failure.
  Example:

  WHERE department_id = NVL(:P3_DEPT_ID, department_id)
  

• Avoid SQL injection: Bind variables automatically protect against SQL injection—never concatenate raw input into queries.

• Clear session state if needed: Ensure session state reflects the latest input, especially when navigating between pages or using modal dialogs.

Example: Interactive Report Filter

You might want an Interactive Report to filter dynamically based on a form item like P1_REGION:

1. Add a hidden item P1_REGION

2. Create a dynamic action or computation that sets this item based on user interaction.

3. Use the item in the report query:

SELECT *
FROM customers
WHERE region = :P1_REGION

 In APEX, form fields (page items) can be referenced in SQL using bind variables. Bind variables prevent SQL injection and improve query performance.

Syntax for Using a Form Field in SQL

SELECT column1, column2  

FROM table_name  

WHERE column3 = :P1_FORM_ITEM;

• :P1_FORM_ITEM is a bind variable that represents the value entered by the user in the P1_FORM_ITEM field.

• The query dynamically filters results based on the user input.

Oracle APEX Documentation

To learn more about referencing items and using bind variables, refer to the official documentation:
https://docs.oracle.com/en/database/oracle/apex/
Search for: Using Bind Variables and Referencing Page Items

Conclusion

Referring form fields in SQL queries in Oracle APEX is a fundamental technique for creating dynamic, user-driven applications. By using bind variables with proper syntax and structure, you can filter data, control logic, and adapt content to the user’s input. With careful validation and best practices, you ensure both performance and security in your APEX applications.