Calling External APIs Using APEX_WEB_SERVICE in Oracle APEX

Modern applications often need to communicate with external systems such as payment gateways, SMS services, CRM platforms, booking systems, weather services, and third-party business applications. Oracle APEX provides the APEX_WEB_SERVICE package to make REST and SOAP web-service calls directly from PL/SQL. The following 15 points explain how to use it effectively with practical coding examples.

1. What is APEX_WEB_SERVICE?

APEX_WEB_SERVICE is an Oracle APEX API used to communicate with external web services from PL/SQL. It can be used to send HTTP requests, process responses, manage headers and cookies, and work with REST and SOAP services.

Commonly used procedures and functions include:

APEX_WEB_SERVICE.MAKE_REST_REQUEST

APEX_WEB_SERVICE.MAKE_REST_REQUEST_B

APEX_WEB_SERVICE.MAKE_REQUEST

APEX_WEB_SERVICE.SET_REQUEST_HEADERS

It is particularly useful when an APEX application needs to exchange data with an external system.

2. Calling a REST API Using GET

A GET request is generally used to retrieve data from an external API.


The API may return a JSON response such as:


The response can then be parsed and displayed in an APEX page or stored in a database table.

3. Sending Data Using POST

A POST request is commonly used when an application needs to create or submit data to an external service.


The request body contains the information that should be sent to the external application.

4. Sending JSON Data

Most modern REST APIs exchange data using JSON. Oracle Database JSON functions can be used to construct JSON safely.


Using JSON functions becomes especially useful when the request contains multiple attributes or nested JSON structures.

5. Setting HTTP Headers

External APIs often require specific HTTP headers. For example, when sending JSON, the Content-Type header should identify the request body as JSON.


Headers can also be used for API keys, bearer tokens, custom API versions, and other information required by the external service.

6. Using API Authentication

Many APIs require authentication before allowing access. Common authentication methods include API keys, Basic Authentication, Bearer tokens, and OAuth 2.0.

For example, a bearer token may be sent through a request header:


Authentication information should be protected carefully and should not be exposed in client-side JavaScript or publicly accessible application code.

7. Use APEX Web Credentials for Secrets

API passwords, API keys, and OAuth credentials should not normally be hard-coded into PL/SQL.

APEX Web Credentials can store authentication information securely and can be referenced by a credential static ID.


This separates sensitive authentication information from application code and makes credential management easier.

8. Passing Parameters to an API

Many APIs support query parameters for filtering or searching data.

For example:

https://api.example.com/orders?status=ACTIVE

Parameters can be passed using APEX collections of parameter names and values.


This approach is useful when API filters need to be generated dynamically.

9. Parsing JSON Responses

After receiving a response, the application usually needs to extract specific values.

Suppose the API returns:


The response can be stored in a CLOB and processed using Oracle JSON functions.


The result would be:

John

For multiple records, JSON_TABLE can be used to transform JSON data into relational rows.

10. Checking HTTP Status Codes

A successful PL/SQL execution does not necessarily mean that the external API successfully completed the request. The HTTP response status should be checked.


    IF APEX_WEB_SERVICE.G_STATUS_CODE = 200 THEN

 

        DBMS_OUTPUT.PUT_LINE('API call successful');

 

    ELSE

 

        DBMS_OUTPUT.PUT_LINE(

            'HTTP Status: ' ||

            APEX_WEB_SERVICE.G_STATUS_CODE

        );

 

    END IF;

 

END;

Common HTTP statuses include:

200 → Successful request

201 → Resource created

400 → Bad request

401 → Unauthorized

403 → Forbidden

404 → Not found

500 → Server error

11. Implement Proper Error Handling

External services can fail because of network problems, invalid input, authentication failures, timeouts, or server errors. Therefore, API calls should include exception handling.


In production applications, users should receive a meaningful error message rather than internal database details.

12. Calling APIs from APEX Page Processes

APEX_WEB_SERVICE can be called from an APEX Page Process, which is useful when an API needs to be invoked after a user submits a page.

For example:


The response can then be displayed on the page, parsed, or stored in a database table.

For user-triggered operations, an Ajax Callback can also be used when appropriate.

13. Calling SOAP Web Services

Although REST is common today, some enterprise systems still expose SOAP web services.

Oracle APEX provides:

APEX_WEB_SERVICE.MAKE_REQUEST

A simplified SOAP request can look like:


The actual SOAP envelope depends on the service's WSDL and operation requirements.

14. Handling Binary Data and Real-World Integrations

Some APIs return binary content such as PDF files, images, Excel documents, or attachments. APEX_WEB_SERVICE provides BLOB-oriented functionality for these scenarios.

For example:


This is useful when an APEX application needs to retrieve an external document and store it in a database BLOB column.

A hotel PMS, for example, could integrate with:

APEX PMS

   ↓

Payment API

   ↓

Booking API

   ↓

SMS API

   ↓

Email API

15. Security, Performance, and Best Practices

External API integration should be designed with security and performance in mind. Always use HTTPS, protect credentials with Web Credentials, validate input and API responses, check HTTP status codes, and avoid exposing API keys or access tokens in application pages. Avoid unnecessary API calls because external requests depend on network availability and third-party performance. For frequently used information, consider storing or caching appropriate data locally.

A production integration can follow this architecture:

APEX Application

       ↓

Validate Input

       ↓

APEX_WEB_SERVICE

       ↓

External API

       ↓

Check HTTP Status

       ↓

Parse Response

       ↓

Store / Display Data

       ↓

Log Result

For long-running integrations, consider asynchronous processing rather than making users wait for an external service.

Conclusion

APEX_WEB_SERVICE provides a powerful way to connect Oracle APEX applications with external REST and SOAP services. Developers can retrieve data using GET, send information using POST, work with JSON and XML, manage HTTP headers, use authentication credentials, process responses, and handle binary content.

The key principle is:

Secure the credentials, validate the request and response, handle errors properly, and avoid unnecessary external API calls.

With a well-designed integration architecture, Oracle APEX can communicate with payment gateways, CRM systems, booking platforms, SMS services, HR applications, financial systems, and many other external services while keeping the integration logic inside the Oracle environment.

 

Comments

Popular posts from this blog

Implementing Authentication and Authorization in Oracle APEX

Mastering Shared Components in Oracle APEX

Designing a Scalable Database Architecture for Oracle APEX Applications