How Do I Use RESTful Web Services in APEX

Introduction
RESTful Web Services in Oracle APEX allow your application to connect and interact with external systems, APIs, and cloud services using HTTP methods. Whether you want to display live data from a third-party API, post data to an external system, or integrate cloud features like AI or payment gateways, RESTful Web Services are the foundation. In Oracle APEX, Web Source Modules simplify this integration by turning REST endpoints into declarative data sources usable throughout your application.

How to Use RESTful Web Services in APEX

1. Create a Web Source Module

   • Navigate to Shared Components > Web Source Modules.

   • Click Create and select From Scratch or From URL.

   • Enter the REST endpoint URL (e.g., https://api.example.com/employees).

   • Define the HTTP method (typically GET for fetching data).

   • Configure authentication (none, basic auth, OAuth2, or API key).

2. Define Parameters

   • Under the Parameters section, add path or query parameters if your service requires them.

   • Use APEX substitutions like :P1_ID to pass page item values.

   • Test the service to verify it returns expected data.

3. Map JSON/XML Response to Columns

   • Once APEX fetches a sample response, it will auto-detect fields.

   • Review and rename columns for usability.

   • Set data types (e.g., VARCHAR2, NUMBER, DATE).

4. Use REST Data in APEX Components

   • Go to a page and create a new Interactive Report, Chart, or Classic Report.

   • Under Data Source Type, select Web Source.

   • Choose the Web Source Module you created.

   • Bind the required parameters from page items.

5. Make Dynamic REST Calls

   • Use Dynamic Actions to set parameter values on the fly.

   • For POST, PUT, or DELETE, create PL/SQL processes or use APEX_EXEC package in PL/SQL.

   • Handle errors gracefully using validations and exception blocks.

Best Practices

• Always secure your Web Source Modules with proper authentication.

• Use bind variables for dynamic parameters and avoid hardcoding sensitive values.

• Leverage response caching where appropriate to minimize API calls.

• Use the Test feature in Web Source Modules to verify your setup before deployment.

• Document each REST call’s purpose, parameters, and expected output for easier maintenance.

Oracle APEX Documentation

• Using Web Source Modules

• Consuming REST Web Services in APEX

1. Step 1: Define a Web Source

• Go to the SQL Workshop in Oracle APEX.

• Select RESTful Services or Web Source to create a new connection.

• Define the REST API endpoint (e.g., https://earthquake.usgs.gov/fdsnws/event/1/query).

• Define the Request Method (GET, POST, etc.) and set any necessary parameters (e.g., starttime, endtime).

2. Step 2: Map Data to APEX

• Once the Web Source is connected to the API, APEX will automatically generate data mappings for the API response.

• You can define how the JSON or XML response should be parsed and how it will be presented in APEX components such as reports or charts.

3. Step 3: Use in APEX Components

• After the Web Source is created and data mappings are set up, you can use the data in APEX components like Reports, Forms, or Charts.

• For instance, an Interactive Report can be connected to this Web Source, and data from the API will be displayed in the report.

4. Step 4: Synchronize Data (Optional)

• To periodically fetch or update the data, you can schedule jobs in APEX or use APEX RESTful Services to send or receive data on a regular basis.

What are the Best Practices for REST Synchronization:

• Error Handling: Always handle potential errors, such as API downtime, incorrect data formats, and failed requests.

• API Rate Limits: Be mindful of API rate limits imposed by the external service to avoid excessive requests.

• Data Caching: Cache API responses when possible to improve performance and reduce the number of requests to the external API.

• Security: Ensure secure communication by using HTTPS and handling authentication (e.g., OAuth, API keys) as needed.

Conclusion
Oracle APEX makes it easy to consume RESTful Web Services and integrate external data into your applications with minimal code. Whether using GET for data retrieval or POST/PUT for updates, APEX's declarative approach via Web Source Modules and dynamic bindings allows for powerful and flexible integrations. Understanding and following best practices ensures secure, performant, and maintainable REST integrations in your APEX projects.

 

How do I Test the APEX REST Call

Introduction
Testing REST calls in Oracle APEX is a critical part of integrating external data and services into your applications. Before deploying or embedding RESTful services into reports or forms, it's important to validate the response, parameters, and structure to ensure your application handles the data properly. Oracle APEX provides built-in tools to test and debug REST calls through the Web Source Module interface and REST Workshop.

How to Test the APEX REST Call

1. Navigate to Web Source Modules

   • Go to Shared Components > Web Source Modules.

   • Click on the REST module you want to test.

   • You will see the Details, Operations, and Parameters tabs.

2. Test with Sample Data

   • In the Web Source Module page, click Test (usually at the top right).

   • Provide required parameter values if any (e.g., path or query parameters like employee_id).

   • Click Test to execute the call.

   • APEX will display the JSON or XML response. You can inspect keys, values, and data structure directly.

3. Use REST Workshop for Additional Testing

   • Navigate to SQL Workshop > RESTful Services > RESTful Services.

   • If your service was defined through ORDS (Oracle REST Data Services), you can locate and test it here.

   • Expand the module and click Test next to the desired resource handler (GET, POST, etc.).

   • Enter headers, parameters, and body as needed, then click Send.

4. Check Response and Debug Issues

   • Confirm that the HTTP status code is 200 OK.

   • Inspect the returned payload for completeness and correctness.

   • Check for missing or incorrect parameters.

   • If you receive 403, 404, or 500 errors, revisit the endpoint URL, authorization headers, and parameter mapping.

5. Test Dynamic Values in a Page

   • On the APEX page using the REST module, enter values in page items that are used as input.

   • Run the page and confirm the REST call reflects the dynamic values and returns expected results.

   • Use Developer Toolbar > Debug mode to track how values are passed into the REST call and inspect any runtime issues.

Best Practices

• Always test REST services in both standalone mode (Web Source Module) and in-page runtime.

• Validate API key or OAuth2 configuration if you receive authentication errors.

• Use test values that represent edge cases (e.g., large inputs, nulls, or invalid entries).

• Log response payloads and status codes using PL/SQL for post-deployment monitoring.

• Use tools like Postman to test external REST APIs independently before integration.

Oracle APEX Documentation

• Using Web Source Modules

• RESTful Services Testing with REST Workshop

To test your RESTful service call:

1. Enter an employee ID in the P1_EMPLOYEE_ID text box.

2. Trigger the process (e.g., by clicking a button).

3. Check the output, either in the page item or via the debug output in Oracle APEX.

Step by Step simplest method “Example”

Continuing from the previous section, create a “Module”

Enter the required fields.

Should look something like this:

Go back to the newly created Module

 

Lets add a handler

This handler will look for the id and return those rows. Add the SQL code below.

Navigating back to the module and slecteting the ID template displays the following

The new “ID” handler should look lik the following two images.

The new handler will display at the bottom of the screen.

Totest the call the Service use the following:

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

That will return the following from a browser:

Or the text equivalent of:

{"items":[{"id":1,"code":"0AK","name":"Pilot Station Airport","city":"Pilot Station","state":"AK"}],"hasMore":false,"limit":25,"offset":0,"count":1,"links":[{"rel":"self","href":"https://adbnphxadcdev01.adb.us-phoenix-1.oraclecloudapps.com/ords/xxdii_paul_test/airportslist/1"},{"rel":"describedby","href":"https://adbnphxadcdev01.adb.us-phoenix-1.oraclecloudapps.com/ords/xxdii_paul_test/metadata-catalog/airportslist/item"},{"rel":"first","href":"https://adbnphxadcdev01.adb.us-phoenix-1.oraclecloudapps.com/ords/xxdii_paul_test/airportslist/1"}]}

Common Scenarios for REST Synchronization:

1. Fetching External Data: 

• You can set up REST synchronization to regularly pull data from an external service (such as a weather API, earthquake data, or social media API) and display it in your APEX application.

2. Sending Data to External Services: 

• You can push data from your APEX forms to external systems (e.g., submitting user data to a CRM or payment gateway).

3. Periodic Synchronization: 

• Schedule periodic synchronization (e.g., every hour or day) to fetch or send data automatically without user intervention.

REST Synchronization in Oracle APEX: How It Works

Oracle APEX provides multiple ways to synchronize data with external RESTful services:

1. Web Source Modules:

• Web Source modules allow you to connect APEX to RESTful APIs and create Read-only data sources. You can use a Web Source to pull data from the external API and display it in your APEX reports or forms.

• Example: If you want to display earthquake data from the USGS API in an APEX report, you would define a Web Source for the API endpoint and map the fields.

Steps to create a Web Source:

• Create a Web Source in your APEX application.

• Specify the REST API endpoint and request method (GET, POST, etc.).

• Define how to parse the JSON or XML response and map it to APEX application columns.

2. RESTful Web Services in APEX:

• APEX allows you to expose RESTful Web Services that can be consumed by external applications or APIs. This is typically used when you want APEX to push data to another system via REST.

• APEX provides RESTful Web Service endpoints, and you can define these services to accept or return data in JSON or XML format.

EXAMPLE:

• APEX exposes a REST endpoint that an external system can send a POST request to, containing data that will be inserted into your APEX application's database.

3. Interactive Grid with REST Integration:

• You can use an Interactive Grid in APEX to integrate with REST APIs for CRUD operations (Create, Read, Update, Delete). The grid allows users to interact with remote data by making REST calls when records are added, edited, or deleted.

4. Scheduled Jobs for Periodic Synchronization:

• You can set up scheduled jobs in APEX (or Oracle DB) to periodically fetch or push data to external APIs. This can be useful for synchronizing data at regular intervals, without requiring manual intervention.

5. Using PL/SQL to Interact with REST APIs:

• For more custom control over the synchronization process, you can use PL/SQL to interact with RESTful APIs. The UTL_HTTP package in PL/SQL allows you to make HTTP requests to external REST services. You can use this to fetch data and then insert/update it in APEX.

 Conclusion
Testing REST calls in Oracle APEX ensures your application integrates external services reliably and securely. By leveraging Web Source Modules, REST Workshop, and runtime debugging, developers can validate each part of the REST call lifecycle. A well-tested REST integration leads to more stable, responsive, and maintainable APEX applications.

 

How do I Display the REST Data in APEX

 

Introduction
Displaying REST data in Oracle APEX is one of the most powerful capabilities for modern application development. Oracle APEX provides multiple ways to consume external RESTful services and integrate them into your pages using low-code components. Whether you're pulling live data from third-party APIs or integrating services across systems, APEX makes it easy to display REST data in reports, charts, forms, and other UI components.

How to Display REST Data in APEX

1. Create a Web Source Module

   • Navigate to Shared Components > Web Source Modules.

   • Click Create and enter the REST endpoint URL. APEX will attempt to fetch sample data from the endpoint.

   • Configure authentication if the endpoint requires it (e.g., API Key, OAuth2).

   • Define the structure of the response. APEX parses the JSON or XML and lets you select data fields.

   • Save the Web Source Module.

2. Use the Web Source in a Component

   • Go to a page (or create a new one).

   • Choose a region type like Interactive Report, Classic Report, or Form.

   • Select Web Source as the data source.

   • Choose the Web Source Module you created earlier.

   • APEX automatically binds the Web Source’s data structure to the region columns.

   • Customize columns, labels, formatting, and filters as needed.

3. Optional: Add Filters or Parameters

   • You can pass page items as bind variables into the Web Source to dynamically filter results.

   • Modify the Web Source Module and use Parameters to map input values (query string, path, or header).

   • Example: Pass :P1_ID as a query parameter to filter by ID.

   • This allows you to create dynamic and interactive REST-powered reports.

4. Preview the Output

   • Run the page to see the live REST data displayed directly in your APEX app.

   • You can combine REST data with client-side interactivity using Dynamic Actions or JavaScript.

Best Practices

• Use pagination options to avoid overloading the report with large datasets.

• If the REST service is slow or rate-limited, consider caching the results in a local table.

• Secure the Web Source Module using authentication schemes to protect credentials and sensitive data.

• Use meaningful column names and labels for better user experience.

• Monitor API response times and failures to catch issues early.

Oracle APEX Documentation

• Using Web Source Modules

• Creating REST Data Sources

Once you have extracted the relevant data (e.g., name, position), you can display it on your APEX page by setting values to page items like P1_EMPLOYEE_NAME, P1_EMPLOYEE_POSITION, etc.

Alternatively, you can use Interactive Reports, Charts, or Forms to display data in a more structured way.

HOW DO I CREATE A REST

Step 1. Navigate to an existing REST Data Service and click on the “Manage Synchronization” link.

Step 2. Select the “New Table” option, provide a name for the table, and press “Save”

Step 3. Click on the “Create Table” in the “Table Status” region.

Step 4. Review the table in SQL workshop

Step 5. Select the type of  “data load” that you want by selecting from the “Append/Merge/Replace” buttons

Step 6. Press the “Save and Run” button.

You should have the following results in your table.

 

Conclusion
Displaying REST data in Oracle APEX is a seamless process that integrates modern APIs into your low-code applications. By leveraging Web Source Modules, you can build interactive and responsive interfaces that connect with external data in real time. Whether you’re displaying data in a report, a form, or a dashboard, APEX offers all the tools needed to harness RESTful services securely and efficiently.

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.