# APEX_WEB_SERVICE The Definitive Guide # Introduction [APEX\_WEB\_SERVICE](https://docs.oracle.com/en/database/oracle/apex/23.2/aeapi/APEX_WEB_SERVICE.html) 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). ```sql 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. ```sql -- 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. ```plaintext ?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. ```sql 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. ```sql -- 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](https://docs.oracle.com/en/database/oracle/apex/23.2/htmig/enabling-network-services-in-oracle-db11g-or-later.html). ## 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.
💡
Things are changing in Oracle Database 23c, and you can use the OS Certificate Store instead of a DB Wallet. You can read more about this here.
### 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. ![Getting an SSL Certificate for a Wallet](https://cdn.hashnode.com/res/hashnode/image/upload/v1701872166793/3e2644d6-72d5-4e18-a906-3d33a5c1a1f7.png align="center") You can then copy the '.cer' file to the DB server and create a database wallet as follows: ```sql -- 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). ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1701871690777/29ccaf03-55dd-4078-baea-1f0763af856a.png align="center") 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`.
💡
If you use the Oracle OCI Autonomous or APEX Services, you do not need to worry about creating a database TLS wallet. Oracle creates a TLS wallet for you and populates it with almost every SSL/TLS certificate available.
> Thanks to @[Richard Soule](@RichSoule) 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](https://orclapex.hashnode.dev/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](https://developer.mozilla.org/en-US/docs/Web/HTTP/Status) 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`. ```sql 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.
💡
It is good practice always to specify a value for p_http_method. This makes things clear to the next developer who sees your code.
## 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. ![Screen shot showing Oracle APEX Monitor Activity > Web Service Activity Logs](https://cdn.hashnode.com/res/hashnode/image/upload/v1703881056611/b18bff48-7742-4dde-8878-69624ac83345.png align="center") You can control whether calls to APEX\_WEB\_SERVICE are logged in the Workspace Settings: ![Control if calls to APEX_WEB_SERVICE are logged](https://cdn.hashnode.com/res/hashnode/image/upload/v1703882128284/659cd752-a9a2-473a-8803-a60b7d9e9554.png align="center") ## 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.
💡
If you haven't already, I advise you to change the INTERNAL (Administration Services) default ASAP.
### Internal / Administration Services Level Login to the INTERNAL (Administration Services) workspace and click Manage instance> Security > Workspace Isolation > 'Maximum Web Service Requests'. ![Set Maximum Web Service Requests from the INTERNAL workspace.](https://cdn.hashnode.com/res/hashnode/image/upload/v1703881906396/d126a19a-1479-4f99-abb8-bd3063d7c4dc.png align="center") ### Workspace Level Login to the INTERNAL (Administration Services) workspace and Click Manage Workspaces > Existing Workspaces > Edit Workspace Information > Workspace Isolation. ![Set Maximum Web Service Requests for a specific workspace.](https://cdn.hashnode.com/res/hashnode/image/upload/v1703882470496/72300c57-8242-4c1e-a358-e7f9a54a458d.png align="center")
⚠
The workspace override option is unavailable for the OCI ATP or APEX Services.
### Set Via API Bonus tip: you can also set Maximum Web Service Requests for a Workspace using the `apex_instance_admin` PL/SQL API. ```plaintext 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](https://orclapex.hashnode.dev/ords-etags) and [Content-Type](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/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. ```sql -- 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. ```sql 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: ```bash 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): ```sql 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: ```bash 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. ```sql 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//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.
💡
One of the many advantages of APEX_WEB_SERVICE is that it automatically handles OAuth2 tokens. When you first call APEX_WEB_SERVICE, it gets a token and stores it. It will re-use that token until it expires, at which time it will automatically get a new one. The token exchange happens seamlessly to you as the developer.
* `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: ```bash curl --location 'https://login.microsoftonline.com//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: ```bash 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: ![APEX Web Credential](https://cdn.hashnode.com/res/hashnode/image/upload/v1701906964747/f1b716b5-0642-4225-adc1-3f08c034f6f3.png align="center") # Multi-Part Call APEX\_WEB\_SERVICE has supported [Multi-Part](https://swagger.io/docs/specification/describing-request-body/multipart-requests/) API calls since APEX 20.2. Here is an example of a multi-part request. ```sql 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. ```sql -- Fetch a BLOB from SharePoint DECLARE l_file_blob BLOB; l_sp_drive_id VARCHAR2(100) := ''; l_sp_file_id VARCHAR2(100) := ''; 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//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 ```
💡
APEX_WEB_SERVICE makes fetching a file from SharePoint pretty easy. Once you have a Web Credential in Place, we need just three lines of code to fetch a file.
# 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](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: ```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.
💡
This technique can turn all kinds of data into rows and columns. You could use JSON_TABLE to fetch and parse JSON. You could even combine another APEX PL/SQL API APEX_ZIP, to fetch a zipped JSON file, unzip it, parse it, and turn it into rows and columns in a single SQL statement. Marry that with APEX_DATA_PARSER, and the whole world is your oyster!
# 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](https://blog.cloudnueva.com/series/apex-shorts) * 📝 [APEX Posts](https://blog.cloudnueva.com/tag/orclapex)