APEX REST: How do I Handle the Response

 

Introduction
In Oracle APEX, working with RESTful Web Services is a common practice when integrating external systems or building modern, data-driven applications. One of the most critical aspects of this integration is handling the response returned by a RESTful service. Whether you are calling a REST API from a Web Source Module, PL/SQL block, or using apex_web_service, it's essential to understand how to capture, parse, and utilize the returned data effectively. This blog will guide you through the techniques to handle REST responses in Oracle APEX and apply them to your applications.

How to Handle REST Responses in Oracle APEX

1. Using Web Source Modules (for Interactive Components)

   • Go to Shared Components > Web Source Modules and create a new module using a REST endpoint.

   • During creation, APEX will fetch the JSON structure and let you define how each JSON field maps to columns.

   • After defining the Web Source, use it directly in Classic Reports, Interactive Reports, or Forms.

   • APEX automatically handles the response behind the scenes, parsing the JSON and populating your report.

2. Using PL/SQL with apex_web_service APIs

   • For more control, use apex_web_service.make_rest_request to perform the call and receive the response.

     DECLARE
       l_response CLOB;
     BEGIN
       l_response := apex_web_service.make_rest_request(
         p_url => 'https://api.example.com/employees',
         p_http_method => 'GET'
       );
     END;
     

   • Once the response is captured in a CLOB, use apex_json.parse to read and extract values:

     apex_json.parse(l_response);
     FOR i IN 1 .. apex_json.get_count(p_path => 'employees') LOOP
       dbms_output.put_line(apex_json.get_varchar2(p_path => 'employees[%d].name', p0 => i));
     END LOOP;
     

3. Storing or Displaying the Response

   • You can assign response values to page items:

     :P1_EMP_NAME := apex_json.get_varchar2(p_path => 'employee.name');
     

   • Or insert parsed values into a table for processing or auditing.

Best Practices

• Always validate the response status code before parsing. Use apex_web_service.g_status_code to check for errors.

• Use apex_json rather than manually parsing JSON for reliability and cleaner code.

• Limit the size of responses with pagination when possible to avoid memory issues.

• Secure any REST calls with proper authentication headers and avoid exposing sensitive endpoints to the client side.

• Log errors for debugging by capturing the full response in a debug table or session state.

Oracle APEX Documentation
Official Reference: https://docs.oracle.com/en/database/oracle/apex
Helpful APIs:

• apex_web_service

• apex_json

Once you make the RESTful API call, you will get a JSON or XML response, depending on how the service is configured.

1. If JSON: Use APEX’s JSON utilities to parse and extract the values. For example:

DECLARE

    l_json       JSON_OBJECT_T;

    l_name       VARCHAR2(100);

    l_position   VARCHAR2(100);

BEGIN

    -- Parse the response

    l_json := JSON_OBJECT_T.parse(l_response);

    -- Extract specific values from JSON

    l_name := l_json.get('name').to_string();

    l_position := l_json.get('position').to_string();

    -- Populate APEX items with the extracted data

    :P1_EMPLOYEE_NAME := l_name;

    :P1_EMPLOYEE_POSITION := l_position;

END;

If XML: Use Oracle’s XML utilities to parse and extract values if the response is XML.

 Conclusion
Handling REST responses in Oracle APEX is a crucial skill that allows your applications to leverage real-time data and connect with external systems. Whether using Web Source Modules for low-code data display or apex_web_service with apex_json for fine-grained control, APEX provides the flexibility to manage and consume REST responses securely and effectively. By following best practices and using APEX’s built-in tools, you can ensure your REST integrations are reliable, maintainable, and powerful.