Introduction

APEX_WEB_SERVICE is by far my favorite APEX Pl/SQL API. It dramatically reduces the effort of calling external Web Services from the Oracle Database. In this post, I will first cover the key concepts you need to know and then review several use cases where I have used APEX_WEB_SERVICE to make my life easier.

Key Concepts

HTTP Headers

When dealing with REST APIs, there are two main ways to pass additional information to the REST API. These are through HTTP Headers and Query String Parameters.

You typically use HTTP Headers to convey information to a REST API. In the example below, I am requesting version 2.1 of the API be used. Note: HTTP Headers are also used to pass Authorization tokens, but APEX_WEB_SERVICE takes care of this for you (more on that later).

apex_web_service.set_request_headers 
 (p_name_01   => 'Prefer', 
  p_value_01  => 'apiversion=2.1');

Clearing Existing HTTP Headers

• It is always a good idea to use apex_web_service.clear_request_headers to clear any existing HTTP Headers. This is important if you still have headers set from a previous call to apex_web_service.

• You can also pass TRUE to the boolean p_reset to apex_web_service.set_request_headers and it will clear any existing headers.

-- Option 1 clear headers before calling set_request_headers
apex_web_service.set_request_headers;

-- Option 2, clear existing headers while setting new ones.
apex_web_service.set_request_headers 
 (p_name_01   => 'Prefer', 
  p_value_02  => 'apiversion=2.1',
  p_reset     => TRUE);

• You can pass up to five request headers to apex_web_service.set_request_headers.

Query String Parameters

Query string parameters are typically used to pass parameters to the REST API you call. When viewed in a REST Request URL, they are seen as a '?' mark followed by a '&' delimited list of 'parameter=value' pairs.

?lat=32.866889&lng=-117.257139&date=today&formatted=0

APEX_WEB_SERVICE simplifies passing parameters by allowing you to populate two PL/SQL arrays, one with a list of the parameter names and one with a list of the parameter values. The above query string can be represented as follows.

DECLARE
  lt_parm_names      apex_application_global.VC_ARR2; 
  lt_parm_values     apex_application_global.VC_ARR2; 
BEGIN
  lt_parm_names(1)  := 'lat'; 
  lt_parm_values(1) := '32.866889'; 
  lt_parm_names(2)  := 'lng'; 
  lt_parm_values(2) := '-117.257139'; 
  lt_parm_names(3)  := 'date'; 
  lt_parm_values(3) := 'today'; 
  lt_parm_names(4)  := 'formatted'; 
  lt_parm_values(4) := '0'; 
END;

You can then pass the parameters to APEX_WEB_SERVICE using the parameters p_parm_name and p_parm_value (examples to follow).

Handling Timeouts

When calling remote APIs, we can never be sure how long they will take to respond. Sometimes, we must wait longer for requests to complete or shorten the wait period so that we can fail faster.

APEX_WEB_SERVICE handles timeouts via the p_transfer_timeout parameter. You can change the default timeout (180 seconds) by passing a number of seconds to this parameter.

Access Control Lists (ACLs)

You will not get far until you create an ACL to allow APEX to make outbound requests to the Web Service you are trying to call. ACLs are the Oracle Database's way of limiting which users can make calls to which end-points. When creating the ACL, you need to specify the current APEX schema as the principal_name, e.g., the principal_name would be APEX_230200 for APEX 23.2.

-- Example ACL to allow the schema APEX_230200 
--   connect access to any host '*'
BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_230200',
                           principal_type => xs_acl.ptype_db));
END;

You can learn more in the APEX Installation Guide.

TLS/SSL Certificates

Nearly all web services have HTTPS end-points. This means that APEX_WEB_SERVICE must pass along a public certificate and the request to the Web Service. The Oracle Database handles this by storing these certificates in a database TLS wallet. TLS Wallets are created on the file system of the Database Server.

Creating a Database TLS Wallet

You need to get the certificate before creating the DB TLS Wallet. The easiest way to get the certificate for a web service is to call it from your browser, view it, and then export the Root certificate to a '.cer' file.

You can then copy the '.cer' file to the DB server and create a database wallet as follows:

-- Find somewhere to put the wallet on the DB Server
-- A good place is alongside the xdb_wallet
-- Start by going to admin under $ORACLE_HOME
cd $ORACLE_HOME/admin
-- Find the xdb_wallet
find . -type d -name "xdb_wallet"
./devdb/xdb_wallet
-- In this case the xdb_wallet wallet is under $ORACLE_HOME/admin/devdb
cd $ORACLE_HOME/admin/devdb
mkdir tls_wallet
cd tls_wallet
orapki wallet create -wallet . -auto_login
orapki wallet add -wallet . -trusted_cert -cert ABC.crt -auto_login
-- -auto_login indicates the wallet can be used on any server.

Referencing a DB TLS Wallet from APEX

The easiest way to reference a database TLS wallet in APEX is to configure it under Administration Services (the INTERNAL workspace).

APEX_WEB_SERVICE will use the TLS wallet configured here unless you tell it otherwise. You can override the default by passing the parameter p_wallet_path to APEX_WEB_SERVICE. If the wallet has a password (which it need not), then you can pass this with the parameter p_wallet_pwd.

Thanks to Richard Soule for his input on the Wallet section.

Forward Proxy

An alternative to a database wallet is to use a proxy server. With this approach, you call an HTTP end-point, and the forward proxy passes the request on to the final end-point over HTTPS.

APEX_WEB_SERVICE allows you to specify a proxy using the parameter p_proxy_override.

Secured Web Services

Most web services are secured and require you to pass some credentials to authenticate your request. APEX_WEB_SERVICE makes dealing with credentials easy. At the heart of this ease of use are APEX Web Credentials. APEX Web Credentials offer a way to store your credentials securely, making it seamless for APEX services like APEX_WEB_SERVICE to consume them. APEX Web Credentials support the following credential types:

• Basic Authentication - Sends username and password in Base64-encoded form as the Authorization request header.

• OAuth2 Client Credentials - Oracle APEX exchanges the client ID and client secret for an Access Token using a token server URL. The access token is then used to perform the actual request. If the access token is expired, Oracle APEX will transparently request a new one.

• OCI Native Authentication - Oracle APEX signs requests to the Oracle Cloud Infrastructure (OCI) REST API, as described in the OCI Documentation.

• HTTP Header - The credential is added to the REST request as an HTTP Header. The credential's name is used as the header name, and the credential's secret is used as the header value.

• URL Query String - The credential is added to the URL of the REST request as a Query String Parameter (for example: ?name=value).

• Key Pair - A key pair credential consists of a public key, openly shared for encrypting data, and a private key, securely kept secret for decrypting data, ensuring a secure data exchange.

To utilize an APEX Web Credential in your web service call, you need to reference the Web Credential static ID in the p_credential_static_id parameter (see examples below).

Did it Work?

Once you have called a web service using APEX_WEB_SERVICE, you need a way to confirm that it worked. In the world of REST APIs, this is accomplished by verifying the HTTP response status code. While there are guidelines on what each response code means, you need to understand the response codes for each API you call. A response code in itself does not tell you everything. Some APIs may return a 500 response and provide details of the error in the response payload.

To get the response code from a call to APEX_WEB_SERVICE, you must check the PL/SQL package global apex_web_service.g_status_code.

  l_response_clob := 
    apex_web_service.make_rest_request
     (p_url         => l_rest_url,
      p_http_method => 'GET');

  IF apex_web_service.g_status_code != 200 THEN
    -- Something went wrong. Add code here to handle the error.
  END IF;

HTTP Method

When calling web services, you must specify an HTTP method. Possible HTTP methods are HTTP GET, POST, PUT, DELETE, and PATCH. When using APEX_WEB_SERVICE, you select the method using the p_http_method parameter. APEX_WEB_SERVICE defaults to the GET method if you do not explicitly provide it.

Logging

All calls to the APEX_WEB_SERVICE API are logged into the table apex_webservice_log. You can view the logs by clicking Monitor Activity > Web Service Activity Log from APEX Builder.

You can control whether calls to APEX_WEB_SERVICE are logged in the Workspace Settings:

Maximum Requests for a Workspace

After configuring a new APEX Environment and you start making many calls to APEX_WEB_SERVICE, many people get the error message "ORA-20001: You have exceeded the maximum number of web service requests per workspace".

This is caused because, by default, APEX is configured to allow only 1,000 web service requests in a 24 period (rolling window).

You can change this at the Workspace Level or from the INTERNAL (Administration Services) Workspace to change it for your whole instance. Setting the value at the Workspace level overrides the value set at the INTERNAL (Administration Services) level.

Internal / Administration Services Level

Login to the INTERNAL (Administration Services) workspace and click Manage instance> Security > Workspace Isolation > 'Maximum Web Service Requests'.

Workspace Level

Login to the INTERNAL (Administration Services) workspace and Click Manage Workspaces > Existing Workspaces > Edit Workspace Information > Workspace Isolation.

Set Via API

Bonus tip: you can also set Maximum Web Service Requests for a Workspace using the apex_instance_admin PL/SQL API.

BEGIN
  apex_instance_admin.set_workspace_parameter
   (p_workspace => 'ABC',
    p_parameter => 'MAX_WEBSERVICE_REQUESTS' ,
    p_value     => 10000000);
    COMMIT;
END;

Response Headers

The final concept we need to cover is response headers. Web services often include HTTP Headers in the response, providing additional information such as eTag and Content-Type information. After calling APEX_WEB_SERVICE, APEX populates a PL/SQL array called apex_web_service.g_headers containing a list of the Response HTTP Headers.

-- Fetching the HTTP Response Headers after calling apex_web_service
FOR i in 1.. apex_web_service.g_headers.count LOOP
  dbms_output.put_line
   ('Name: ' || apex_web_service.g_headers(i).name ||
    'Value: ' || apex_web_service.g_headers(i).value);
END LOOP;

Use Cases

Now that we have covered the concepts, let's examine the examples. This section will review several examples of calling REST APIs using APEX_WEB_SERVICE. These examples will focus on apex_web_service.make_rest_request and apex_web_service.make_rest_requestb.

Simple REST API Call

In its simplest form, you can call apex_web_service.make_rest_request as follows. This particular REST API requires no authentication.

DECLARE
  l_response      CLOB;
BEGIN
  l_response := apex_web_service.make_rest_request
   (p_url         => 'https://official-joke-api.appspot.com/random_joke',
    p_http_method => 'GET');
  dbms_output.put_line('Response Code   : ' || apex_web_service.g_status_code);
  dbms_output.put_line('Response Payload: ' || l_response);
END;
-- Result:
Response Status : 200
Response Payload: {"type":"general","setup":"Why did Sweden start painting barcodes on the sides of their battleships?","punchline":"So they could Scandinavian.","id":318}

The equivalent CURL statement looks like this:

curl --location 'https://official-joke-api.appspot.com/random_joke'

Basic Authentication

This request calls a web service that has basic authentication (username and password):

DECLARE
  l_response      CLOB;
BEGIN
  -- Set the Request HTTP Headers.
  apex_web_service.set_request_headers 
   (p_name_01  => 'Content-Type', 
    p_value_01 => 'application/json',
    p_reset    => TRUE); 

  -- Call API using Username and Password.
  l_response := apex_web_service.make_rest_request 
    (p_url                  => 'https://dummyjson.com/auth/login', 
     p_http_method          => 'POST', 
     p_transfer_timeout     => 10, 
     p_username             => 'Jon', 
     p_password             => 'ABCDEF',
     p_scheme               => 'Basic'); 

  dbms_output.put_line('Response Status : ' || apex_web_service.g_status_code);
  dbms_output.put_line('Response Payload: ' || l_response);
END;

• APEX_WEB_SERVICE takes care of Base 64 encoding the username and password and sending an HTTP header that looks like Authorization: Basic bmxvaXRlcnRvbjg6SFRReHhYVjlCcTQ=.

The equivalent CURL statement looks like this:

curl --location --request POST 'https://dummyjson.com/auth/login' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic bmxvaXRlcnRvbjg6SFRReHhYVjlCcTQ='

OAuth2 Client Credentials

This request calls a Microsoft Graph API to get a list of SharePoint Sites for the Microsoft Office 365 tenant.

DECLARE
  l_response      CLOB;
  lt_parm_names   apex_application_global.VC_ARR2; 
  lt_parm_values  apex_application_global.VC_ARR2; 
BEGIN

  -- Set the Request HTTP Headers.
  --  p_reset clears any HTTP Headers from a previous call.
  apex_web_service.set_request_headers 
   (p_name_01  => 'Prefer', 
    p_value_01 => 'apiversion=2.1',
    p_reset    => TRUE); 

  -- Set the Query String Parameters
  lt_parm_names(1)  := '$select'; 
  lt_parm_values(1) := 'name,displayName,sharepointIds,siteCollection'; 
  lt_parm_names(2)  := '$top'; 
  lt_parm_values(2) := '2'; 

  -- Call the REST API.
  l_response := apex_web_service.make_rest_request 
    (p_url                  => 'https://graph.microsoft.com/v1.0/sites', 
     p_http_method          => 'GET', 
     p_transfer_timeout     => 10, 
     p_parm_name            => lt_parm_names, 
     p_parm_value           => lt_parm_values, 
     p_token_url            => 'https://login.microsoftonline.com/<OFFICE_TENANT_ID>/oauth2/v2.0/token', 
     p_credential_static_id => 'CN_MS_GRAPH_ACCESS'); 
  dbms_output.put_line('Response Status : ' || apex_web_service.g_status_code);
  dbms_output.put_line('Response Payload: ' || l_response);
END;

• p_transfer_timeout causes the request to timeout if it has not been completed within 10 seconds.

• p_token_url tells apex_web_service the URL to use when exchanging the OAuth2 client ID and client secret for a token.

• p_credential_static_id tells APEX which APEX Web Credential to use when performing the OAuth2 Client Credentials token exchange.

The CURL for the OAuth2 token fetch that APEX takes care of for use behind the scenes looks like this:

curl --location 'https://login.microsoftonline.com/<TBDOFFICETENANTID>/oauth2/v2.0/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'grant_type=client_credentials' \
--data-urlencode 'client_id=12345' \
--data-urlencode 'client_secret=ABCDEF' \
--data-urlencode 'scope=https://graph.microsoft.com/.default'

The CURL to call the MS Graph API using the token looks like this:

curl --location 'https://graph.microsoft.com/v1.0/sites?%24top=2&%24select=name%2CdisplayName%2CsharepointIds%2CsiteCollection' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Prefer: apiversion=2.1' \
--header 'Authorization: Bearer eyJ0eXA...'

The Web Credential for the above example looks like this:

Multi-Part Call

APEX_WEB_SERVICE has supported Multi-Part API calls since APEX 20.2. Here is an example of a multi-part request.

DECLARE
  -- Variable used to build the Multipart request.
  l_multipart      apex_web_service.t_multipart_parts; 
  l_multipart_blob BLOB;
  l_file_blob      BLOB;
  l_response       CLOB;
BEGIN

  -- Set the OUath2 Token Manually.
  -- apex_web_service will set an HTTP Header called 'Authorization' to
  --  Bearer {Token Value}
  apex_web_service.oauth_set_token(p_token => '{Token Value}');

  -- Add the SharePoint Drive ID.
  apex_web_service.append_to_multipart 
   (p_multipart    => l_multipart, 
    p_name         => 'sharepoint_drive_id', 
    p_content_type => 'text/plain', 
    p_body         => 'ABC'); 

  -- Add the SharePoint File Item ID.
  apex_web_service.append_to_multipart 
   (p_multipart    => l_multipart, 
    p_name         => 'sharepoint_item_id', 
    p_content_type => 'text/plain', 
    p_body         => 'ABC'); 

  -- Add File BLOB Content.
  apex_web_service.append_to_multipart 
   (p_multipart    => l_multipart, 
    p_name         => 'file', 
    p_filename     => 'Test_File.pdf', 
    p_content_type => 'application/pdf', 
    p_body_blob    => l_file_blob); 

  -- Build the Complete Multipart Request as a BLOB.
  l_multipart_blob := apex_web_service.generate_request_body 
                       (p_multipart => l_multipart); 

  -- Post the Multipart BLOB to the REST API.
  -- Calling apex_web_service.oauth_set_token above, means 
  --   we don't need to pass a Web Credential.
  l_response := apex_web_service.make_rest_request 
   (p_url         => 'https://www.example.com', 
    p_http_method => 'POST', 
    p_body_blob   => l_multipart_blob);  

  dbms_output.put_line('Response Status : ' || apex_web_service.g_status_code);
  dbms_output.put_line('Response Payload: ' || l_response);
END;

• In the above example, I am using an alternative method for setting the OAuth token by calling apex_web_service.oauth_set_token.

Fetching a BLOB

You often need to fetch media, as opposed to JSON or XML. In this example, I am fetching a file from SharePoint.

-- Fetch a BLOB from SharePoint
DECLARE
  l_file_blob     BLOB;
  l_sp_drive_id   VARCHAR2(100) := '<SHAREPOINT_DRIVE_ID>';
  l_sp_file_id    VARCHAR2(100) := '<SHAREPOINT_ITEM_ID>';
  l_url           VARCHAR2(32000);
BEGIN

  -- Set the Request HTTP Headers.
  apex_web_service.set_request_headers 
   (p_name_01  => 'Prefer', 
    p_value_01 => 'apiversion=2.1',
    p_reset    => TRUE); 

  -- Build the URL to the file using the SharePoint Drive ID and the File ID.
  l_url := 'https://graph.microsoft.com/v1.0/drives/' || 
             l_sp_drive_id || '/items/' || l_sp_file_id || '/content';

  -- Call the SharePoint MS Graph API to fetch the file into a BLOB variable.
  l_file_blob := apex_web_service.make_rest_request_b 
    (p_url                  => l_url, 
     p_http_method          => 'GET', 
     p_transfer_timeout     => 25,
     p_token_url            => 'https://login.microsoftonline.com/<OFFICE_TENANT_ID>/oauth2/v2.0/token',
     p_credential_static_id => 'CN_MS_GRAPH_ACCESS'); 

  dbms_output.put_line('Response Status : ' || apex_web_service.g_status_code);
  dbms_output.put_line('File Size       : ' || 
    apex_string_util.to_display_filesize(p_size_in_bytes => dbms_lob.getlength(l_file_blob)));
END;

-- Result:
-- Response Status : 200
-- File Size       : 11.4KB

Fetch an XML Atom Blog Feed

We can achieve great things if we combine APEX_WEB_SERVICE with other capabilities of the Oracle database. Let's say we want to query the last five posts from Jeff Smith's blog feed. Jeff's blog posts are available via an XML-based RSS feed accessible using the URL https://www.thatjeffsmith.com/feed/atom. There is no security; you can copy and paste the above URL into your browser and see the feed.

We can turn this feed into rows and columns that we can consume in APEX using the following SQL:

SELECT TO_UTC_TIMESTAMP_TZ(bp.date_published)  date_published
,      TO_UTC_TIMESTAMP_TZ(bp.date_updated)    date_updated
,      bp.guid
,      bp.title
,      bp.author
,      NVL(bp.link, bp.link_alt) link
,      bp.description
,      bp.content
FROM   XMLTABLE(XMLNAMESPACES('http://www.w3.org/2005/Atom' AS "ns0"), 'ns0:feed/ns0:entry'
        PASSING XMLTYPE(apex_web_service.make_rest_request
                         (p_url => 'https://www.thatjeffsmith.com/feed/atom', 
                          p_http_method => 'GET'))
        COLUMNS
          guid           VARCHAR2(255)  PATH 'ns0:id',
          date_published VARCHAR2(50)   PATH 'ns0:published',
          date_updated   VARCHAR2(50)   PATH 'ns0:updated',
          title          VARCHAR2(500)  path 'ns0:title',
          author         VARCHAR2(255)  path 'ns0:author/ns0:name',
          link           VARCHAR2(500)  path 'ns0:link[@rel eq "alternate"]/@href',
          link_alt       VARCHAR2(500)  path 'ns0:link[1]/@href', 
          description    VARCHAR2(500)  path 'ns0:summary',
          content        CLOB           path 'ns0:content'
    ) bp;

• Looking deep within the FROM clause, you can see we are calling apex_web_service.make_rest_request. This returns the XML from the feed into the XMLTYPE function, which converts the XML text into an XML object.

• Once we have an XMLTYPE object, we can pass that into XMLTABLE, which maps the XML into rows and columns.

• At this point, you need to reference the fields from the feed you want to expose in the SQL query.

Conclusion

Even though I billed this post as the 'definitive guide', I am sure there are many use cases for APEX_WEB_SERVICE that I have missed. This speaks to the power and flexibility of this API. I strongly encourage you to get familiar with this, my favorite Oracle APEX PL/SQL API!

🔗 Read More

• 🩳 APEX Shorts

• 📝 APEX Posts