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.

 

APEX REST: How do I USE Path Parameters

 

Introduction

When building RESTful web services in Oracle APEX, path parameters offer a clean and efficient way to pass values directly within the URL structure. This technique is especially useful when you want to retrieve specific records or perform operations on targeted resources such as employees, orders, or products. Understanding how to define and use path parameters properly is essential for making your APEX RESTful services more intuitive, dynamic, and REST-compliant.

How to Use Path Parameters in APEX

To implement path parameters in Oracle APEX, follow these steps:

1. Access the RESTful Services Module
   Navigate to SQL Workshop > RESTful Services and either create a new module or edit an existing one.

2. Define a Resource Template with a Path Parameter
   Add a new resource template with a syntax like /employees/{employee_id}. Here, {employee_id} is a path parameter placeholder.

3. Declare the Path Parameter
   After saving the resource template, go to the Parameters section and add a new parameter:

   • Name: employee_id

   • Type: Path Parameter

   • Data Type: Number or Text, depending on your use case

   • Required: Yes

4. Use the Parameter in SQL or PL/SQL Logic
   In the GET method of the resource, reference the path parameter using a bind variable:

   SELECT first_name, last_name, department_id
     FROM employees
    WHERE employee_id = :employee_id
   

5. Test the Endpoint
   You can now call the service using a URL like:

   https://your-apex-domain/ords/hr/api/employees/103
   

   The value 103 will be passed to your SQL via :employee_id.

Best Practices

• Always validate the path parameter value in your SQL or PL/SQL to avoid unexpected results or SQL injection risks.

• Use meaningful and predictable names for your path parameters (e.g., {employee_id}, {order_id}).

• Stick to RESTful conventions—use plural nouns for resource names (/employees, /orders) and include the parameter only when you need to access a specific record.

• Avoid mixing path parameters and query parameters for the same data point; choose one method consistently.

Oracle APEX Documentation
For more details, see the official Oracle APEX documentation:
https://docs.oracle.com/en/database/oracle/apex/

 

You would call the service by embedding the ID number directly in the URL path.

Example URL:

https://your-server/ords/schema/rest/employees/{employee_id}

To dynamically call the service using the ID number provided by the user (e.g., through a page item like P1_EMPLOYEE_ID), you can use the following approach:

1. Create a Button or Process to trigger the call.

2. Use the APEX_WEB_SERVICE package in PL/SQL to make the RESTful call.

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

    l_url := 'https://your-server/ords/schema/rest/employees/' || 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, the l_url is dynamically constructed by appending the employee_id from the page item P1_EMPLOYEE_ID to the base URL.

Conclusion

Path parameters in Oracle APEX are a powerful and RESTful way to pass values within your URL endpoints. They help keep your API design clean and logical while enabling dynamic access to individual resources. By understanding how to define and implement path parameters effectively, you can build more maintainable and intuitive web services within APEX.

Calling a RESTful service in ORACLE APEX

 

Introduction
Calling a RESTful service in Oracle APEX enables your application to interact with external systems, APIs, and web services seamlessly. Whether you want to fetch data from a third-party service or integrate with your own backend, Oracle APEX provides powerful tools to consume RESTful endpoints directly within your application. This blog will guide you through the process of calling RESTful services in APEX, including setup, usage, and best practices to ensure efficient and secure integrations.

How to Call a RESTful Service in Oracle APEX

1. Define a REST Data Source
   In Oracle APEX, start by creating a REST Data Source that points to the external RESTful service you want to consume. Use the RESTful Services interface or the Web Source Modules feature to define the endpoint URL, HTTP method (GET, POST, etc.), headers, and query parameters.

2. Configure Authentication
   Many REST APIs require authentication. Oracle APEX supports several authentication methods such as Basic Authentication, OAuth2, and API keys. Configure the appropriate authentication scheme for your REST Data Source to securely connect with the external service.

3. Test the REST Endpoint
   Use the built-in testing tools in APEX to verify that your REST Data Source returns the expected data. Check the response format, usually JSON or XML, and ensure that you handle any errors or unexpected responses gracefully.

4. Use REST Data in APEX Components
   Once the REST Data Source is defined, you can consume its data in various APEX components like Interactive Reports, Interactive Grids, Classic Reports, and Forms. Use SQL or RESTful Web Source references to bind REST data dynamically within your application.

5. Use PL/SQL for REST Calls (Optional)
   For advanced use cases, you can call RESTful services programmatically using APEX_WEB_SERVICE or UTL_HTTP packages within PL/SQL processes. This approach provides greater control over HTTP headers, request bodies, and response handling.

Best Practices

• Cache REST responses when appropriate to reduce latency and API call costs

• Handle exceptions and timeouts to avoid disrupting user experience

• Secure sensitive API credentials using APEX environment variables or encrypted storage

• Use pagination for large datasets to improve performance

• Log REST call activity and errors for monitoring and troubleshooting

• Keep your REST Data Sources up to date with any changes in the external API

Oracle APEX Documentation
For comprehensive details and examples on calling RESTful services in Oracle APEX, visit the official documentation here:
https://docs.oracle.com/en/database/oracle/application-express/

Calling a RESTful Service by ID number in Oracle APEX , you need to pass the ID as part of the URL or query parameters. This can be done in a variety of ways, depending on the structure of the RESTful service you're calling.

We have 3 options, with the last option being the simplest (It also has step by step descriptions)

Key Concepts:

1. RESTful Web Services: REST (Representational State Transfer) is an architectural style used for designing networked applications. RESTful web services allow you to perform operations like GET, POST, PUT, and DELETE over HTTP, typically using JSON or XML as the data format.

2. RESTful Web Service in APEX: Oracle APEX provides built-in functionality to consume and interact with external REST APIs directly through APEX components, such as Web Source or RESTful Services.

3. Data Synchronization: The goal of synchronization is to keep the data in APEX consistent with the data on an external system. This can involve:

• Pulling data from an external REST API to populate your APEX application (e.g., fetching remote data and displaying it in APEX forms or reports).

• Pushing data to an external system by sending data from APEX to the REST API (e.g., submitting a form and sending the data to a remote service).

• Updating or deleting data from APEX to keep both systems in sync.

Scenario: Calling a RESTful Service by ID

Let’s assume you have an existing RESTful Service for a resource, such as employees, and you want to retrieve details for a specific employee based on an ID number.

Steps to Call a RESTful Service by ID Number in Oracle APEX 23:

Step 1: Define the RESTful Web Service

Let’s say the resource employees is exposed as a RESTful Web Service and you have a query parameter for the id.

1. Define the RESTful Resource in APEX: 

• The Resource Path can be something like /employees/{employee_id}.

• The employee_id would be a parameter in the RESTful Web Service URL.

Example of defining the resource path:

/employees/{employee_id}

Where {employee_id} is a placeholder for the actual ID passed by the user or application.

Step 2: Prepare the Call to the RESTful Service in APEX

To call this service by employee_id, you can either pass the ID in the URL as a path parameter or use a query parameter.

 

Step 1: Define the RESTful Web Service in Oracle APEX

Suppose you have a RESTful service that exposes employee data and supports filtering by an employee_id. You want to define the resource path as /employees/{employee_id}, where {employee_id} is a placeholder for the actual employee ID.

How to define this in Oracle APEX:

1. Navigate to the RESTful Services Interface

• In Oracle APEX, go to SQL Workshop → RESTful Services.

• Click on Create to define a new RESTful Module or select an existing module to add a resource.

2. Create a New RESTful Module (if needed)

• Give it a meaningful Name and Base Path (for example, /api).

• The Base Path is the root URL under which your resources live.

3. Define the RESTful Resource

• Click Add RESTful Resource to define the resource.

• For Resource Template, enter:

 

Prepare the Call to the RESTful Service in APEX — Detailed Examples

In Oracle APEX, when you want to call a RESTful service by employee_id, you typically have two main ways to pass the ID:

1. As a Path Parameter — The ID is part of the URL path

2. As a Query Parameter — The ID is passed in the URL after a question mark (?)

Example 1: Passing employee_id as a Path Parameter

Suppose the REST endpoint URL looks like this:
https://api.example.com/employees/{employee_id}

You replace {employee_id} with the actual employee ID value.

Steps in Oracle APEX:

1. Create a REST Data Source or Web Source Module

   • Go to SQL Workshop → RESTful Services (or Shared Components → Web Source Modules)

   • Create a new REST Data Source

   • Enter the base URL: https://api.example.com/employees/

2. Define the Path Parameter

   • In the REST Data Source settings, define a path parameter, for example:
     Name: employee_id
     Type: Path Parameter

   • When calling the service, you will substitute this parameter with a page item value or bind variable.

3. Use Substitution to Pass the Parameter
   For example, in a report SQL query or process, use:

   SELECT * FROM APEX_WEB_SERVICE.make_rest_request(
     p_url => 'https://api.example.com/employees/' || :P1_EMPLOYEE_ID
   );
   

   or if using Web Source Modules, specify the path parameter as :P1_EMPLOYEE_ID

4. Example URL Called
   If P1_EMPLOYEE_ID = 101, the URL called will be:
   https://api.example.com/employees/101

Example 2: Passing employee_id as a Query Parameter

Suppose the REST endpoint URL looks like this:
https://api.example.com/employees?employee_id=101

Steps in Oracle APEX:

1. Create REST Data Source or Web Source Module

   • Base URL: https://api.example.com/employees

2. Add Query Parameter

   • Define a query parameter employee_id whose value will come from an APEX page item or bind variable, e.g., :P1_EMPLOYEE_ID

3. Use Substitution to Pass the Parameter
   For example, in a PL/SQL block or REST Data Source, build the URL with query parameter:

   DECLARE
     l_url VARCHAR2(4000);
   BEGIN
     l_url := 'https://api.example.com/employees?employee_id=' || :P1_EMPLOYEE_ID;
     -- Make the REST call using APEX_WEB_SERVICE or Web Source Module
   END;
   

4. Example URL Called
   If P1_EMPLOYEE_ID = 101, the URL called will be:
   https://api.example.com/employees?employee_id=101

Additional Tips:

• When creating Web Source Modules in APEX, you can define path and query parameters interactively. For path parameters, use curly braces {} syntax like /employees/{employee_id} and APEX will prompt you to map that to a page item or variable.

• For query parameters, use the “Add Query Parameter” button in the Web Source Module setup and set its value dynamically to an APEX page item or bind variable.

• Always validate and sanitize the input to prevent injection or malformed URLs.

• You can use Page Items (like :P1_EMPLOYEE_ID) to dynamically pass parameters from the user interface.

Summary Code Snippet Example — Calling with Path Parameter Using APEX_WEB_SERVICE

DECLARE
  l_response CLOB;
BEGIN
  l_response := APEX_WEB_SERVICE.make_rest_request(
    p_url => 'https://api.example.com/employees/' || :P1_EMPLOYEE_ID,
    p_http_method => 'GET'
  );
  -- Process l_response JSON as needed
END;

Summary Code Snippet Example — Calling with Query Parameter Using APEX_WEB_SERVICE

DECLARE
  l_response CLOB;
BEGIN
  l_response := APEX_WEB_SERVICE.make_rest_request(
    p_url => 'https://api.example.com/employees?employee_id=' || :P1_EMPLOYEE_ID,
    p_http_method => 'GET'
  );
  -- Process l_response JSON as needed
END;

These methods let you prepare and call RESTful services efficiently in Oracle APEX by dynamically passing employee_id either as a path or query parameter.

 

Conclusion
Integrating RESTful services within Oracle APEX empowers your applications to leverage external data and services, extending functionality and improving user experience. By following best practices in defining REST Data Sources, managing authentication, and handling responses, you can build reliable and maintainable integrations. Proper planning and testing will help ensure your RESTful service calls enhance your APEX applications effectively and securely.

How do I Apply Best Practices for Creating REST Services in APEX

 

Introduction
Creating REST services in Oracle APEX allows you to expose your application data and logic through modern APIs that can be consumed by various clients, including web, mobile, and other backend systems. However, to ensure these services are reliable, secure, and maintainable, applying best practices throughout the development process is essential. This blog will guide you through the key best practices for creating REST services in Oracle APEX, helping you design efficient and robust APIs.

How to Apply Best Practices for Creating REST Services in APEX

1. Plan Your REST API Design
   Design your REST endpoints with clarity and consistency. Use meaningful resource names and HTTP methods that follow REST conventions: GET for reading, POST for creating, PUT/PATCH for updating, and DELETE for removing data.

2. Use Modular RESTful Services
   Organize your REST services into modules and templates in Oracle APEX. This structure helps manage related endpoints logically and simplifies maintenance.

3. Validate Input and Output
   Always validate incoming data to prevent SQL injection and ensure data integrity. Use JSON schema validation or PL/SQL checks. Also, format your responses consistently, usually in JSON, to provide clear data structures to clients.

4. Implement Pagination and Filtering
   For endpoints that return large datasets, implement pagination and filtering to optimize performance and reduce load times. Use query parameters to allow clients to specify page size, offsets, and filter criteria.

5. Secure Your REST Services
   Use Oracle APEX authentication schemes like OAuth2, Basic Authentication, or API keys to protect your endpoints. Restrict access based on user roles and privileges. Ensure sensitive data is never exposed unnecessarily.

6. Use Meaningful HTTP Status Codes
   Return appropriate HTTP status codes for each response, such as 200 for success, 201 for created resources, 400 for bad requests, 401 for unauthorized access, and 500 for server errors. This practice helps clients handle responses correctly.

7. Log and Monitor REST Service Usage
   Enable logging for REST service calls and errors to monitor usage patterns and detect issues early. Use Oracle APEX monitoring tools and database audit features.

8. Document Your REST APIs
   Maintain clear and up-to-date documentation of your REST endpoints, including URL paths, request and response formats, parameters, and authentication requirements. This aids developers who consume your APIs.

Best Practices Summary

• Follow RESTful design conventions

• Organize services with modules and templates

• Validate all input and sanitize outputs

• Implement pagination and filtering

• Secure APIs with robust authentication

• Use correct HTTP status codes

• Enable logging and monitoring

• Maintain comprehensive documentation

Oracle APEX Documentation
For more detailed guidance on RESTful services in Oracle APEX, visit the official documentation here:
https://docs.oracle.com/en/database/oracle/application-express/

• Use parameterized queries to prevent SQL injection.

• Implement authentication to restrict access to sensitive data.

• Optimize queries to improve performance for large datasets.

• Enable pagination for GET requests using OFFSET and FETCH NEXT.

• Use CORS policies to allow secure cross-origin API calls.

Creating a REST service from a table in Oracle APEX allows applications to expose database data securely over HTTP. By defining GET, POST, PUT, and DELETE handlers, developers can build robust APIs that integrate with other systems, mobile apps, and web applications. This approach simplifies data exchange while maintaining security and performance.

EXAMPLE:

Navigate to: SQL Workshop > RESTful Services

Select: RESTful Data Services > Enable Object

Click on the green Button

Select the table that you want to create a RESTful Service from.

Copy the service URL from the screen.

https://adbnphxadcdev01.adb.us-phoenix-1.oraclecloudapps.com/ords/xxdii_paul_test/airportslist/

Result when the url is entered into the browser.

Conclusion
Adhering to best practices when creating REST services in Oracle APEX ensures your APIs are efficient, secure, and easy to maintain. By following these guidelines, you can deliver scalable RESTful services that integrate seamlessly with various clients and provide a solid foundation for your applications’ data access needs. Taking the time to design and implement REST services properly will save you effort in the long run and enhance the overall quality of your Oracle APEX applications.