APEX REST: How do I Use Query Parameters

Introduction

In Oracle APEX, RESTful Web Services enable developers to create flexible and secure APIs that can be consumed by other applications. One of the essential techniques for interacting with these services is using query parameters to filter or retrieve specific data. Query parameters are appended to the endpoint URL and are particularly useful when you want to pass optional or multiple values without altering the resource path. Understanding how to define and use query parameters effectively allows your APEX-based APIs to be more dynamic, user-friendly, and scalable.

How to Use Query Parameters in APEX RESTful Services

1. Create or Edit a RESTful Module

   • Go to SQL Workshop > RESTful Services in Oracle APEX.

   • Create a new RESTful module or open an existing one.

   • Define a new resource template or use an existing template, e.g., /employees.

2. Define the Query Parameter

   • Within the resource template, go to the Parameters section.

   • Click Create to define a new parameter:

     • Name: department_id (or any name relevant to your use case)

     • Type: Query Parameter

     • Data Type: Number or String

     • Required: No (if optional)

3. Use the Parameter in SQL

   • In the GET handler’s SQL statement, use the bind variable for the query parameter:

     SELECT employee_id, first_name, last_name, department_id
       FROM employees
      WHERE (:department_id IS NULL OR department_id = :department_id)
     

   • This allows your service to return:

     • All employees if no parameter is passed.

     • Only employees in a specific department if department_id is provided.

4. Call the Service

   • You can now call the endpoint with a query string:

     https://your-apex-domain/ords/hr/api/employees?department_id=90
     

   • The department_id is passed and used in the SQL query.

Best Practices

• Always validate and sanitize input parameters in SQL or PL/SQL to prevent injection and ensure data integrity.

• Provide default values where applicable, using NVL or IS NULL checks in your SQL.

• Keep query parameter names descriptive and consistent with your data model.

• Use conditional logic in SQL for optional parameters to avoid hard failures or unintended results.

• Document each supported query parameter clearly in your API specification.

Oracle APEX Documentation

For official reference, visit the APEX RESTful Services guide:
https://docs.oracle.com/en/database/oracle/apex/

 Alternatively, if the RESTful service expects the employee_id as a query parameter, you can pass it like this:

Example URL:

https://your-server/ords/schema/rest/employees?id=123

In this case, modify the code to append the ID as a query parameter:

DECLARE

    l_url        VARCHAR2(32767);

    l_response   CLOB;

    l_employee_id VARCHAR2(100);

BEGIN

    -- Get the employee ID from the page item

    l_employee_id := :P1_EMPLOYEE_ID;

    -- Construct the RESTful URL with the employee ID as a query parameter

    l_url := 'https://your-server/ords/schema/rest/employees?id=' || l_employee_id;

    -- Make the REST call to the service

    l_response := APEX_WEB_SERVICE.make_rest_request(

                      p_url          => l_url,

                      p_http_method  => 'GET'

                  );

    -- Process the response

    -- (Handle response as JSON or XML depending on your API)

    DBMS_OUTPUT.put_line(l_response);

    

    -- You can also populate the page items with data from the response

    :P1_EMPLOYEE_DATA := l_response; -- Example: Store response in a page item

END;

Here, id is passed as a query parameter, and the API can handle this as such.

Conclusion

Query parameters are an essential component of building efficient, reusable RESTful Web Services in Oracle APEX. They give users the flexibility to filter, sort, and retrieve data without the need to create multiple endpoints. By understanding how to define, implement, and secure query parameters in APEX, developers can create more versatile APIs that better support complex business logic and front-end application needs.