Tuesday, March 31, 2026

Making HTTP Requests from an Oracle Database

This blog is part of a series about aspects and features of Oracle 26 and Autonomous Transaction Processing Database.

I have created a training project that integrates Strava (an application that tracks athletes' activities – in my case, cycling) with an Oracle Autonomous database that then performs spatial data processing and sends the results back to Strava.  The Oracle database calls the Strava APIs in HTTP requests that either extract data from or send it back to Strava.  These calls are made with the UTL_HTTP package.

All web services must be secured.  Only HTTPS is permitted when using an Autonomous Database on a public endpoint.  The only allowed port is 443 when the Autonomous AI Database instance is on a public endpoint. 
External calls are made with the UTL_HTTP package.  It needs a certificate wallet.  Autonomous AI Database instance is preconfigured with an Oracle Wallet that contains more than 90 of the most commonly trusted root and intermediate SSL certificates.  

Access Control Lists (ACLs)

Before Oracle can call any external system, permission must be granted by creating an ACL.  The Strava APIs are all below https://www.strava.com/api/v3/.  The following script creates a new ACL with connect and http privileges for the STRAVA user in my database that runs my application, and assigns the privileges to www.strava.com.  On Autonomous Database, this script should be run by the ADMIN user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl(
    acl         => 'strava_acl.xml',
    description => 'Allow access to Strava API',
    principal   => 'STRAVA',   -- DB user
    is_grant    => TRUE,
    privilege   => 'connect');
END;
/
BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege 
  (acl          => 'strava_acl.xml'
  ,principal    => 'STRAVA'-- DB user
  ,is_grant     => TRUE
  ,privilege    => 'http');
END;
/
BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_acl
  (host         => 'www.strava.com'
  ,lower_port   => 443
  ,acl          => 'strava_acl.xml'
);
END;
/
If there is no ACL, I will get an error when I try to make the HTTP call.
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)

HTTP Redirects

Sometimes an HTTP request is redirected to another URL, which may be in another domain.  An ACL is needed to cover each redirection of the URL.  Otherwise, the HTTP request will fail.  
I have particularly encountered this when downloading a GeoJSON resource directly into PL/SQL, where the published URL redirects to the actual location.  Errors ORA-29273 and ORA-24247 are raised even though an ACL grants access to the published URL.  Either additional ACLs are required, and/or more widely defined ACLs are needed.
By default, UTL_HTTP will follow up to 3 redirections, but the maximum number of redirects can be limited to any value or disabled by setting the maximum to 0
  IF p_redirect >= 0 THEN --restrict http redirect - mainly for debug
    UTL_HTTP.set_follow_redirect(l_req, p_redirect);
  END IF;

The easiest way I have found to determine all the redirections is to start by disabling redirection, by setting the maximum number of redirects to 0, and then look at the body returned from the HTTP request for the redirected URL.  Add the new ACL for this address, increment the number of redirections in SET_FOLLOW_REDIRECT, and repeat the HTTP request.  Repeat this process until the full request returns the requested item.

Take, for example, downloading county boundaries from the Irish Government's Open Data Unit at https://data.gov.ie/dataset/counties-national-statutory-boundaries-20191.
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => '*.arcgis.com',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('connect','http'),
              principal_name => 'STRAVA',
              principal_type => xs_acl.ptype_db));
END;
/

HTTP Requests

To extract information from Strava, I make a GET request to one of the Strava API endpoints.  The response is a JSON message returned in the body of the response.  That is read in chunks of less than 32K into a CLOB variable.
Updates to Strava are made through PUT requests to the Strava API.  The information to be updated is put into the HTTP header.

REM strava_http.sql
…  
  l_req := UTL_HTTP.begin_request(p_url, p_req_type, 'HTTP/1.1');
  UTL_HTTP.set_header(l_req, 'Authorization', 'Bearer ' || g_access_token);
  utl_http.set_header(l_req, 'Accept-Charset', 'UTF-8');
  
  IF p_req_type = 'PUT' THEN
    UTL_HTTP.set_header(l_req, 'Content-Type', 'application/x-www-form-urlencoded');
    IF p_put_body IS NOT NULL THEN   -- Body
      l_header_body := escape_form_value(p_put_body);
      UTL_HTTP.set_header(l_req, 'Content-Length', LENGTH(l_header_body));
      UTL_HTTP.write_text(l_req, l_header_body);
    END IF;
  END IF;

  l_resp := UTL_HTTP.get_response(l_req);
…
  IF l_resp.status_code = 200 THEN
    NULL; --ok
  ELSIF l_resp.status_code = 401 THEN
    RAISE_APPLICATION_ERROR(-20401,'HTTP 401:Unauthorized');
…
  END IF;

  DBMS_LOB.createtemporary(l_clob, TRUE);
  LOOP
    DECLARE 
      l_buf VARCHAR2(32767);
    BEGIN
      UTL_HTTP.read_text(l_resp, l_buf, 32767);
    EXCEPTION WHEN UTL_HTTP.end_of_body THEN EXIT; 
    END;
  END LOOP;
    
  UTL_HTTP.end_response(l_resp);
…

Reading Headers

It is possible to access the header of the HTTP response much as you would an array.
/*list all headers*/
  FOR i IN 1 .. UTL_HTTP.get_header_count(l_resp) LOOP
    UTL_HTTP.get_header(l_resp, i, l_header_name, l_header_value);
    DBMS_OUTPUT.put_line(i ||':'|| l_header_name || ':' || l_header_value);
  END LOOP;
Strava returns a lot of information in the header or the HTTP response.  
  • The status of the HTTP request can be obtained from the response structure, but it is also recorded in the header.
  • Strava imposes usage limits.  By default, I can make 100 read calls within 15 minutes, and 1000 in a day.  The current usage counts and limits are reported in the header every time the Strava API is called.  My application tracks them to stop some processes from making too many requests.  
  • Other items are added by AWS (Strava's host).  x-Amz-Cf-Pop:LHR86-P2 indicates that my request to Strava was served by a CloudFront edge server in London (Heathrow area), specifically node 86, partition P2.
GET https://www.strava.com/api/v3/gear/b993101
1:Content-Type:application/json; charset=utf-8
2:Transfer-Encoding:chunked
3:Connection:close
4:Date:Thu, 26 Mar 2026 20:58:11 GMT
5:x-envoy-upstream-service-time:5779
6:server:istio-envoy
7:status:200 OK
8:x-ratelimit-usage:1,901
9:x-ratelimit-limit:200,2000
10:cache-control:max-age=0, private, must-revalidate
11:vary:Origin
12:referrer-policy:strict-origin-when-cross-origin
13:x-permitted-cross-domain-policies:none
14:x-xss-protection:1; mode=block
15:x-request-id:4a357a89-2935-4f41-9fa8-df0f112b804d
16:x-readratelimit-limit:100,1000
17:x-download-options:noopen
18:etag:W/"3c69eb05224d9014b96fb818f43215d7"
19:x-frame-options:DENY
20:x-readratelimit-usage:1,901
21:x-content-type-options:nosniff
22:X-Cache:Miss from cloudfront
23:Via:1.1 933ed3357b8f85661e4d84ebef8a63a8.cloudfront.net (CloudFront)
24:X-Amz-Cf-Pop:LHR86-P2
25:X-Amz-Cf-Id:_dx6YiTmtNagUoazFjSSXzM4nvFG2NEp_vKm0O2kJWmmAYYX4Z_EHg==
Specific named header values can be read directly without looping through the entire header.  I use this to read the usage counts and limits.
    -- Read usage limit headers
    BEGIN
      UTL_HTTP.get_header_by_name(l_resp, 'x-readratelimit-limit', l_header_value);
      IF l_header_value IS NOT NULL THEN
        g_short_read_limit := REGEXP_SUBSTR(l_header_value, '[^,]+', 1, 1);
        g_long_read_limit  := REGEXP_SUBSTR(l_header_value, '[^,]+', 1, 2);
      END IF;
    EXCEPTION WHEN e_http_request_failed THEN NULL;
    END;
The application logs and reports these limits.
API Log:15-min read usage: 1/100, 15-min all usage: 1/200, daily read usage: 901/1000, daily all usage: 901/2000
Posted by at
Labels: ,

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )