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
Post a Comment