Consuming Inbound HTTP Requests with REST Services

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

I have created a demo project that integrates Strava (an application that tracks athletes' activities – in my case, cycling) with an Oracle Autonomous database that then performs some spatial data processing and sends the results back to Strava.  

I have described how to call the Strava API in HTTP requests from the database to pull data from Strava or push it back.  To complete the integration, the application must receive and process inbound HTTP requests from Strava when activities are added, updated or deleted.  

The database has to handle two kinds of messages from Strava.  

• I have to create a subscription in Strava to receive notifications of new activities.  Part of the authentication of that request includes responding promptly to an HTTP GET request.
• Then, when I have created my subscription, I receive HTTP POST requests to tell me that an activity has been created, updated or deleted in Strava that I then process.

I have created PL/SQL packaged procedures and REST services for each of these requests.

ORDS URL

Oracle REST Data Services (ORDS) are configured by default for Autonomous Database.  You can find the public access URL for the database on the OCI console under Tool Configuration

Enabling REST

All my code exists within a database schema called STRAVA.  Access to ORDS must be granted to this schema.

ORDS.ENABLE_SCHEMA(
    p_enabled             => TRUE,
    p_schema              => 'STRAVA',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'strava',
    p_auto_rest_auth      => FALSE
  );

• See also ThatJeffSmith: ORDS 101: Enabling Oracle Schemas for HTTPS/REST

Defining REST services

My application runs entirely within the database schema STRAVA. To set up my Strava subscription, I had to give my API Application a name in Strava - so I called it PlaceCloud.  Therefore, my REST services are in a module also called PlaceCloud. 

  ORDS.DEFINE_MDULE(
    p_module_name    => 'placecloud',
    p_base_path      => 'placecloud/'
  );

Finally, I have to create a template

  ORDS.DEFINE_TEMPLATE(
    p_module_name => 'placecloud',
    p_pattern     => 'event'
  );

Schema, Module and Template combine to define the path of the REST Service.  

• The schema is STRAVA.
• The module is placecloud.
• The template pattern is event.

That is appended to the public access URL that I got from the OCI console above. Thus, the complete URL of my REST service is 

https://GE************9-GOFASTER1.adb.uk-london1.oraclecloudapps.com/ords/strava/placecloud/event.

When I request to create a Strava Webhook event subscription, Strava sends a callback to a URL I specify to validate the request.  It will be the URL of my REST service.  I can simulate the callback for testing with curl.

curl -i -S -X GET -H "Content-Type: application/json" "https://GE************9-GOFASTER1.adb.uk-london-1.oraclecloudapps
.com/ords/strava/placecloud/event?hub.verify_token=MyPlaceCloud&hub.challenge=abc123&hub.mode=subscribe"

NB: Double quotes, especially around HTTPS URLs, are important; otherwise, the & will be interpreted by the command line, and you will get an error from the REST service!

Get Handler

The REST service for GET calls an anonymous PL/SQL block that calls my packaged procedure, passing parameters from the query string.  A status code and JSON response are returned.

  ORDS.DEFINE_HANDLER(
    p_module_name => 'placecloud',
    p_pattern     => 'event',
    p_method      => 'GET',
    p_source_type => ORDS.SOURCE_TYPE_PLSQL,  
    p_source      => q'[
      DECLARE
        l_status_code NUMBER;
        l_response    VARCHAR2(200 CHAR);
        l_message     CLOB;
      BEGIN 
        strava.webhook_pkg.handle_get(:hub_challenge,:hub_verify_token, l_response, l_status_code, l_message); 
        owa_util.status_line(l_status_code, l_message, FALSE);
        owa_util.mime_header('application/json', FALSE);
        :status_code := l_status_code;
        owa_util.http_header_close;
        htp.p(l_response);
        :response := l_response;
      END;
      ]',
    p_mimes_allowed => 'application/json',
    p_items_per_page => 0
  );

Handling Parameters

Strava puts three parameters in the callback.  Their names have a dot (".") in them.  I have to map them to a bind variable in the PL/SQL block named without the dot.

  ORDS.DEFINE_PARAMETER(
    p_module_name        => 'placecloud',
    p_pattern            => 'event',
    p_method             => 'GET',
    p_name               => 'hub.challenge',
    p_bind_variable_name => 'hub_challenge',
    p_source_type        => 'URI',
    p_param_type         => 'STRING',
    p_access_method      => 'IN'
  );
  ORDS.DEFINE_PARAMETER(
    p_module_name        => 'placecloud',
    p_pattern            => 'event',
    p_method             => 'GET', 
    p_name               => 'hub.verify_token',
    p_bind_variable_name => 'hub_verify_token',
    p_source_type        => 'URI',
    p_param_type         => 'STRING',
    p_access_method      => 'IN'
  );
  ORDS.DEFINE_PARAMETER(
    p_module_name        => 'placecloud',
    p_pattern            => 'event',
    p_method             => 'GET',
    p_name               => 'hub.mode',
    p_bind_variable_name => 'hub_mode',
    p_source_type        => 'URI',
    p_param_type         => 'STRING',
    p_access_method      => 'IN'
  );

Post Handler

Having created the subscription, Strava sends an HTTP POST request to the same URL with a JSON body every time an activity is created, updated or deleted.

  ORDS.DEFINE_HANDLER(
    p_module_name => 'placecloud',
    p_pattern     => 'event',
    p_method      => 'POST',
    p_source_type => ORDS.SOURCE_TYPE_PLSQL,
    p_source      => q'[
      BEGIN strava.webhook_pkg.handle_post(:body_text,:status_code); END;
      ]'
  );

I simply pass the message body to my procedure and return a status code.  See Loading and Processing JSON with PL/SQL.  Again, I can test this with curl.  I can put a sample JSON document in a file,

{
"object_type":"activity",
"object_id":123,
"aspect_type":"create",
"owner_id":999,
"event_time":1700000000
}

and post the file with curl.

curl -i -S -X POST --data-ascii @C:\temp\resttest.json -H "Content-Type: application/json" \
"https://GE************9-GOFASTER1.adb.uk-london-1.oraclecloudapps.com/ords/strava/placecloud/event"

Further Reading

ThatJeffSmith has several very helpful posts: REST APIs for Oracle Database, everything you need to know.

• Parameters and Binds for your RESTful Services: RESULTSETs.    This shows how a parameter can go into the path of the REST service, rather than the query string.
• In my example, I have explicitly coded my REST service.  However, the ORDS API can also create the entire REST definition with ORDS.ENABLE_OBJECT.  Example: ORDS and AutoPLSQL with Packages.

Loading GeoJSON Format GeoSpatial Data into Oracle Autonomous Database

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

As a training exercise, I have created a database application that loads GPS data from activities logged on Strava and compares them with geospatial data to identify the named places visited by the activity. This geospatial data is publicly available in many places, often from government, and is usually freely available, at least for non-commercial purposes.

ESRI Shapefiles

When I first wrote the spatial processing in 2020, I used spatial data formatted as ESRI shapefiles.  These were then converted to Oracle spatial geometries and loaded into a database using the oracle.spatial.util: Class SampleShapefileToJGeomFeature Java conversion.  

See also

• Go-Faster Blog: Spatial Data 4: Obtaining Geographical Data
• Oracle 26 Spatial Developers Guide: Loading ESRI Shapefiles into Spatial

It reads attributes from a dBase file and geometries from a Shapefile, and then writes them to a database table.  I was able to run it on the database server.

export clpath=$ORACLE_HOME/suptools/tfa/release/tfa_home/jlib/ojdbc5.jar:$ORACLE_HOME/md/jlib/sdoutl.jar:$ORACLE_HOME/md/jlib/sdoapi.jar
java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h <dbhost> -p 1521 -sn oracle_pdb -u strava -d strava \
     -t $table -f $base -r 4326 -g geom

However, this approach is not suitable for an autonomous database.  ADB cannot access the local file system, there is no host execution access, and it does not allow external libraries (JARs) required by the Shapefile utility.  It would have to be run on another host.  It only makes a simple JDBC connection and cannot handle the ADB wallet out of the box.

GeoJSON

I have found that many spatial data sets are now available as GeoJSON, often as well as a shapefile.  GeoJSON is much easier to handle, especially in the Autonomous database, because it can be processed entirely within the database.

Often, GeoJSON can be downloaded directly into a database with an HTTP call.  For example, the definitions of Areas of Outstanding Natural Beauty (AONBs) in England can be downloaded as GeoJSON file from the UK Government Planning Data website.  I can make the HTTP call to download the file directly from PL/SQL.  I usually load the JSON into a staging table before trying to convert it to a spatial geometry.  The HTTP request is made by a packaged function strava_http.http_request.

TRUNCATE TABLE strava.stage_geo_data;
DECLARE 
  l_url VARCHAR2(1000) := 'https://files.planning.data.gov.uk/dataset/area-of-outstanding-natural-beauty.geojson';
  l_clob CLOB;
BEGIN
  DBMS_LOB.createtemporary(l_clob, TRUE);
  l_clob:=strava_http.http_request(l_url,99);    
  INSERT INTO stage_geo_data (name, geo_json) VALUES ('Natural England', l_clob);
  DBMS_LOB.freetemporary(l_clob);
END;
/
SELECT x.*, length(geo_json) FROM stage_geo_data x;

However, sometimes the GeoJSON must be downloaded as a file, uploaded to OCI Bucket storage, and then read into the database from there.

DECLARE
  l_blob BLOB;
BEGIN
  -- Read the file from Object Storage into CLOB
  l_blob := DBMS_CLOUD.GET_OBJECT
            (credential_name => 'OBJECT_STORE_CRED'
            ,object_uri      => 'https://objectstorage.uk-london-1.oraclecloud.com/n/l**********a/b/bucket-gofaster1/o/ch0.json'
            );
  INSERT INTO strava.stage_geo_data(name, geo_json) VALUES ('Switzerland', l_blob);
  COMMIT;
END;
/

Converting Shapefiles to GeoJSON 

Some providers still prefer to make only Shapefiles available and not GeoJSON (for example, the Swiss Federal Office of Topography - swisstopo).  However, it is simple to convert the shapefiles to GeoJSON with the ogr2ogr utility available as a part of GDAL (a translator library for various geospatial data formats).  On Windows, I use OSGeo4W and run org2ogr within that.

Many countries have their own geoid and coordinate systems.  It can be more accurate over a limited region.

• In the UK, the Ordnance Survey uses EPSG 27700 – British National Grid for Great Britain.
• The Republic of Ireland and Northern Ireland both use EPSG 2157 - Irish Transverse Mercator
• In Switzerland, the Federal Office of Topography uses EPSG 2056 (Swiss CH1903+ / LV95) and 5728 (LN02 Height)
• Etc.

I can convert a shapefile to a GeoJSON, simultaneously changing the spatial reference identifier (SRID) to WGS84 (also known as EPSG 4326).

ogr2ogr -f GeoJSON swissBOUNDARIES3D_1_5_TLM_LANDESGEBIET.geojson swissBOUNDARIES3D_1_5_TLM_LANDESGEBIET.shp -t_srs EPSG:4326

Then I can proceed with the physical JSON file as previously described.

Converting GeoJSON to an Oracle Spatial Geometry

GeoJSON is just a JSON document, but it is structured in a particular way.  

• It supports Point, LineString, Polygon, MultiPoint, MultiLineString, and MultiPolygon geometries. 
• Geometric objects with additional properties are Feature objects. 
• Sets of features are contained by FeatureCollection objects.
• The specific fields in properties can vary.  I usually create a PL/SQL script specific to each GeoJSON to be loaded.

The sample below is taken from a GeoJSON of French departments.  The properties contain the department code, the name and the region number they are within.  There is no SRID; the data is already in WGS84.

{"type":"FeatureCollection"
,"features":
  [
    {"type":"Feature"
    ,"properties":
      {"code":"01","nom":"Ain","region":"84"}
    ,"geometry":
      {"type":"Polygon"
      ,"coordinates":[[[5.825,45.939],...,[5.825,45.939]]]
      }
    }
,   {"type":"Feature"
    ,"properties":
      {"code":"02","nom":"Aisne","region":"32"}
    ,"geometry":
      {"type":"Polygon"
      ,"coordinates":[[[3.987,49.379],...,[3.987,49.379]]]
      }
    }
…
  ]
}

• See also geojson.org and the GeoJSON Specification (RFC 7946)

This PL/SQL parses the GeoJSON, extracting the descriptive data.  The geometry data is converted with Oracle's SDO_UTIL.FROM_GEOJSON function.

DECLARE 
  l_clob        CLOB;
  j_root        JSON_OBJECT_T;
  j_crs         JSON_OBJECT_T;
  j_features    JSON_ARRAY_T;
  j_feature     JSON_OBJECT_T;
  j_properties  JSON_OBJECT_T;
  j_geometry    JSON_OBJECT_T;

  l_geom        MDSYS.SDO_GEOMETRY;

  l_id         INTEGER;
  l_srid       VARCHAR2(10 char);
  l_name       VARCHAR2(100 char);
  
  e_json_syntax_error  EXCEPTION;
  PRAGMA exception_init(e_json_syntax_error,-40441);
BEGIN 
  SELECT geo_json INTO l_clob FROM strava.stage_geo_data WHERE name = 'France Regions';
  l_clob := strava_http.clean_clob(l_clob);
  --strava_http.pretty_json(l_clob);
  j_root := JSON_OBJECT_T.parse(l_clob);

  j_crs := j_root.get_object('crs');
	  
  j_features := j_root.get_array('features');
  --l_srid     := REGEXP_SUBSTR(j_crs.get_object('properties').get_string('name'),'[^:]+',1,2);
  l_srid := 4326;
  FOR i IN 0 .. j_features.get_size - 1 LOOP
    j_feature := TREAT(j_features.get(i) AS JSON_OBJECT_T);
    j_properties := j_feature.get_object('properties');
    IF j_properties IS NULL THEN 
      dbms_output.put_line('J_PROPERTIES is null');
      l_id := i;
    ELSE
      l_clob := j_properties.to_clob;
      strava_http.print_clob(l_clob); --use this to understand new GeoJSON file properties, but comment it out later
      l_id               := j_properties.get_number('code');
      l_name             := j_properties.get_string('nom');
      dbms_output.put_line(l_id||', '||l_srid||', '||l_name);
    END IF;
	
    j_geometry := j_feature.get_object('geometry');
    IF j_geometry IS NULL THEN
      dbms_output.put_line(l_name||': j_geometry is null');
      l_geom := NULL;
    ELSE
      l_clob := j_geometry.to_clob /*Get coordinates array*/; 
      
      BEGIN
        l_geom := sdo_util.from_geojson(l_clob);
      EXCEPTION 
        WHEN e_json_syntax_error THEN
          dbms_output.put_line(sqlerrm||' during parse of '||l_name||' ('||l_id||'). Switch to own function.' );
          l_geom := strava_sdo.build_sdo_geometry_from_geojson(j_geometry, l_srid);
      END;

      l_geom.SDO_SRID := TO_NUMBER(l_srid);
      IF l_srid != 4326 THEN
        l_geom := SDO_CS.TRANSFORM(l_geom, 4326);
        l_geom.SDO_SRID := 4326;
      END IF;
      l_geom := sdo_util.rectify_Geometry(SDO_CS.MAKE_2D(l_geom),0.001);
    END IF;
    
    dbms_output.put_line(i||','||l_id||','||l_name);
    INSERT INTO stage_my_areas (area_code, area_number, name, geom)
    VALUES ('REG', l_id, l_name, l_geom);
  END LOOP;
  COMMIT;
END;
/

The resulting spatial geometry is written to a staging table.  From there, I can move it to where I finally want it.

Working Around Errors in SDO_UTIL.FROM_GEOJSON

The SDO_UTIL.FROM_GEOJSON function converts a GeoJSON object (or more specifically, a geometry object in GeoJSON format) to a Spatial geometry object.  It was introduced in Oracle 12.2.  However, even in Oracle 26, I have occasionally experienced errors with this function with certain data sets. 

• ORA-40441: JSON syntax error when using the optional FORMAT JSON clause

I am working with publicly available data sets.  Therefore, I will not easily get any problem resolved, even if I could identify the exact cause.  My workaround has been to use my own function  (build_sdo_geometry_from_geojson).  It loads the array of coordinates from the JSON and passes it to Oracle's sdo_geometry constructor to create the geometry. Though I have found that my function always succeeds when from_geojson fails, my function is much slower.  Therefore, I only use it in an exception handler when I get an error from Oracle's function.

For example, I experienced ORA-40441 when loading data for Co. Carlow from the Irish government's Open Data Unit (I don't know why), but not for any of the other 25 counties.

REM ireland_counties_load.sql 
…
DECLARE 
…
  e_json_syntax_error  EXCEPTION;
  PRAGMA exception_init(e_json_syntax_error,-40441);
BEGIN 
…
  BEGIN
    l_clob := j_geometry.to_clob /*Get coordinates array*/; 
…
    l_geom := sdo_util.from_geojson(l_clob);
  EXCEPTION 
    WHEN e_json_syntax_error THEN
      dbms_output.put_line(sqlerrm||' during parse of '||l_name||' ('||l_id||'). Switch to own function.');
      l_geom := strava_sdo.build_sdo_geometry_from_geojson(j_geometry, l_srid);
  END;
…
END;
/

In the log below, you can see that the error was reported, but then the alternative function was successful.

{"OBJECTID":16,"CO_ID":"10000","ENGLISH":"CARLOW","GAEILGE":"Ceatharlach","LOGAINM_ID":"100004","GUID":"2ae19629-143d-13a3-e055-000000000001"
,"CONTAE":"Ceatharlach","COUNTY":"CARLOW","PROVINCE":"Leinster","CENTROID_X":680448.23,"CENTROID_Y":660624.58,"AREA":896306186.01}
10000, 2157, Carlow, Co. Carlow, Province:Leinster, Centre:-6.80998032929769,52.690783228944
ORA-40441: JSON syntax error during parse of Carlow. Switch to own function.
ID:10000, Province:Leinster, County:Carlow, Council:Carlow, Centre:-6.82336146940769,52.7293094021289, 14445 points

• Further reading: Tim Hall: GeoJSON Data Support in Oracle Database 19c

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.

See Using Oracle Autonomous AI Database Serverless: PL/SQL Package Notes for Autonomous AI: Database, UTL_HTTP PL/SQL Package Notes

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.APPEND_HOST_ACE(
    host => 'www.strava.com',
    upper_port => 443,
    lower_port => 443,
    ace  => xs$ace_type(
              privilege_list => xs$name_list('connect','http'),
              principal_name => 'STRAVA',
              principal_type => xs_acl.ptype_db));
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.

• The URL of this GeoJSON is given as https://data-osi.opendata.arcgis.com/api/download/v1/items/e6f6418eb62442c4adbe18d0a64135a2/geojson?layers=0
• But first, you get redirected to https://services-eu1.arcgis.com/FH5XCsx8rYXqnjF5/arcgis/rest/services/Province_Boundaries_Ungeneralised/FeatureServer/replicafilescache/Province_Boundaries_Ungeneralised_-1998524587992549972.geojson
• And then you are redirected to https://stg-arcgisazurecdataprodeu1.az.arcgis.com/exportfiles-2209-745/Province_Boundaries_Ungeneralised_-1998524587992549972.geojson?sv=2025-05-05&st=2026-03-26T21%3A44%3A49Z&se=2026-03-26T22%3A49%3A49Z&sr=c&sp=r&sig=a0IMIZehMwFFvG1klphLt69o8ZrdllLMKs8GwJB28xA%3D
• In the end, I have created a single ACL with a wildcard for *.arcgis.com rather than just data-osi.opendata.arcgis.com.

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 in the body.  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 HTTP response header 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.

e_http_request_failed EXCEPTION;
PRAGMA EXCEPTION_INIT(e_http_request_failed,-29273);
…
-- 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

Loading and Processing JSON with PL/SQL

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

"JSON (JavaScript Object Notation) is a text-based format for storing and exchanging data in a way that’s both human-readable and machine-parsable. … it has grown into a very capable data format that simplifies data interchange across diverse platforms and programming languages."

I have created a demo project that integrates Strava (an application that tracks athletes' activities – in my case, cycling) with an Oracle Autonomous Database, that then performs some spatial data processing.  The Strava APIs all return data in JSON.  My application, written in PL/SQL, reads and processes those messages.

Mostly, I want to hold that data in a regular database table, structured conventionally in reasonably named columns.  In different places, I have loaded that JSON data in different ways, depending on requirements and circumstances.  There are three options to choose from:

1. Directly through a JSON Duality View
2. Convert each name-value pair in explicit code
3. Extract Values from JSON in Virtual Columns

Directly through a JSON Duality View

Oracle introduced JSON Relational Duality in Oracle 23ai.  A JSON duality view is a mapping between table data and JSON documents.  It is possible to extract data from a table as a JSON document simply by querying a duality view based on that table.  It is also possible to insert data into the table through duality views.

For example, Strava tracks my gear (the bike I ride, or the shoes I wear).  I can extract details of each item with the Strava API, and I get a simple JSON document in return.  This is what I get for one of my bikes.

{
  "id" : "b4922223",
  "primary" : false,
  "name" : "Saracen",
  "nickname" : "Saracen",
  "resource_state" : 3,
  "retired" : false,
  "distance" : 1321925,
  "converted_distance" : 1321.9,
  "brand_name" : null,
  "model_name" : null,
  "frame_type" : 3,
  "description" : "",
  "weight" : 14
}

I want to import that into a table in my database that corresponds to that document.

CREATE TABLE gear 
(gear_id            VARCHAR2(20) NOT NULL
,primary            BOOLEAN
,name               VARCHAR2(60) 
,nickname           VARCHAR2(60) 
,resource_state     INTEGER
,retired            BOOLEAN
,distance_m         INTEGER      
,distance_km        NUMBER       
,brand_name         VARCHAR2(60) 
,model_name         VARCHAR2(60)
--frame_type
,description        CLOB
,weight             NUMBER       
,last_updated       TIMESTAMP DEFAULT SYSTIMESTAMP
,CONSTRAINT gear_pk PRIMARY KEY(gear_id)
);

I can use a JSON duality view to make the JSON document correspond to my table structure, rather than code it explicitly.

My table uses the column GEAR_ID as the primary key, but the Strava JSON document just has an ID. A JSON duality view must have a key value called '_id'.  I will map that to the primary key column in this table.  

All the other name-value pairs each map to the corresponding column in the table.  However, I am not bothering to import 'frame type'

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW gear_dv AS
SELECT JSON {'_id'    : g.gear_id
,'primary'            : g.primary
,'name'               : g.name
,'nickname'           : g.nickname
,'resource_state'     : g.resource_state
,'retired'            : g.retired
,'distance'           : g.distance_m
,'converted_distance' : g.distance_km
,'brand_name'         : g.brand_name
,'model_name'         : g.model_name
--frame_type
,'description'        : g.description
,'weight'             : g.weight
}
FROM gear g
WITH INSERT UPDATE
/

The JSON document arrives as an HTTP response and is held in a CLOB variable.  That has to be parsed into a JSON object with JSON_OBJECT_T.PARSE(). Then I either update an existing record or insert a new one.  In either case, that is done via the duality view.  Note that

• The Strava id name is updated to _id to match the duality view, and it has to be _id.  
• I have removed the frame_type and notification_distance name-value pairs.

  j_obj := JSON_OBJECT_T.parse(l_clob);
  l_id  := j_obj.get_string('id');

  BEGIN
    SELECT * INTO r_gear FROM gear WHERE gear_id = p_gear_id FOR UPDATE;
  EXCEPTION
    WHEN no_data_found THEN null;
  END;
  
  IF r_gear.gear_id = p_gear_id THEN
    UPDATE gear_dv d
    SET    d.data = JSON_TRANSFORM
           (value
           ,RENAME '$.id' = '_id'
           ,REMOVE '$.frame_type'
           ,REMOVE '$.notification_distance'
           )
    FROM JSON_TABLE(
           l_clob,
           '$[*]'
           COLUMNS (
             value CLOB FORMAT JSON PATH '$'
           ))
    WHERE d.data."_id" = l_id;
  ELSE
    INSERT INTO gear_dv
    SELECT JSON_TRANSFORM
           (value
           ,RENAME '$.id' = '_id'
           ,REMOVE '$.frame_type'
           ,REMOVE '$.notification_distance'
           )
    FROM JSON_TABLE(
           l_clob,
           '$[*]'
           COLUMNS (
             value CLOB FORMAT JSON PATH '$'
           )
     );
  END IF;

This approach works well where the JSON document structure closely matches the database table structure, and where I don't have to convert the inbound data with any function.  

However, if, for example, the name of the gear had to be upper case, I might put that into the definition of the duality view, thus

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW gear_dv AS
SELECT JSON {'_id'    : g.gear_id
,'name'               : UPPER(g.name)
…
}
FROM gear g WITH INSERT UPDATE
/

But then I would not be able to insert name via the duality view.  I wouldn't get an error, but it simply wouldn't process the name column.   It would be null after the insert, or would not be updated.

While the duality view is a very elegant way to map the data, it has limitations.  As soon as you need to transform data during the import, you probably have to go back to coding the mapping for each name-value pair.

See also 

• Oracle 26 Documentation: JSON-Relational Duality Developer's Guide  
• That Jeff Smith: 
• REST APIs for Oracle Database, everything you need to know
• Oracle Database 23ai JSON Relational Duality Views REST APIs

Convert each name-value pair

The more conventional approach is to copy each name-value pair to a column using one of the get functions, sometimes passing the value through a function, and possibly with logic to determine whether to copy the data.

Here, I have used a row type variable and selected the whole current row from the database before updating data values 

BEGIN
    SELECT * INTO r_activities 
    FROM   activities 
    WHERE  activity_id = p_activity_id 
    FOR UPDATE;
  EXCEPTION
    WHEN no_data_found THEN r_activities.activity_id := p_activity_id;
  END;
…
  j_obj := JSON_OBJECT_T.parse(l_clob);

  r_activities.activity_id       := j_obj.get_number('id');
  r_activities.athlete_id        := j_obj.get_object('athlete').get_number('id');
  r_activities.start_date_utc    := iso8601_utc(j_obj.get_string('start_date'));
  r_activities.start_date_local  := iso8601_tz(j_obj.get_string('start_date_local'), j_obj.get_string('timezone'));
…
  r_activities.distance_km       := j_obj.get_number('distance')/1000;

  r_activities.gear_id           := j_obj.get_string('gear_id');
  IF r_activities.type IN('Ride','Walk','Hike','VirtualRide','Run') THEN
    j_subobj                     := j_obj.get_object('gear');
    IF j_subobj IS NOT NULL THEN
      r_activities.gear_name     := j_subobj.get_string('name');
    END IF;
  END IF;
…
  r_activities.photo_count       := j_obj.get_object('photos').get_number('count');
…

Then the entire row can be inserted or updated at the end from the row-type variable.

  BEGIN  
    INSERT INTO activities VALUES p_activities;
    dbms_output.put_line(sql%rowcount||' activity inserted');
    COMMIT;

  EXCEPTION 
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE activities
      SET ROW = p_activities
      WHERE  activity_id = p_activities.activity_id;
      dbms_output.put_line(sql%rowcount||' activity updated');   
      COMMIT;
  END;

Extract Values from JSON in Virtual Columns

The other option is to store the JSON in a CLOB column in the database and convert it on demand via virtual columns.  Whenever I log a new Strava activity or update, or delete an existing activity, I have subscribed to receive a message from Strava. That message is received by the database using a REST service.  

The message from Strava just tells me that an activity has been created, updated or deleted.  Then I have to process it.  Sometimes, I get multiple messages for the same activity in quick succession.

{
    "aspect_type": "update",
    "event_time": 1516126040,
    "object_id": 1360128428,
    "object_type": "activity",
    "owner_id": 134815,
    "subscription_id": 120475,
    "updates": {
        "title": "Messy"
    }
}

Strava requires that the REST service respond within 2 seconds, so any processing in it must be kept light.  I want to avoid:

• spending time converting the JSON data while the REST service is running,
• any malformed or unexpected variation in JSON causing an error in the REST service,
• concurrent processing of different requests relating to the same activity causing one REST service handler to block another.  

Therefore, my REST service just stores the JSON in a CLOB column on a table and then triggers a scheduler job to process the message.  The subsequent processing needs to access the name-values in the JSON, so I have created virtual columns on the queue table that will only be evaluated on demand.

CREATE TABLE webhook_events
(ID                NUMBER GENERATED ALWAYS AS IDENTITY
,PAYLOAD           CLOB
,processing_status NUMBER DEFAULT 0 NOT NULL
…
,CONSTRAINT webhook_events_pk PRIMARY KEY (id)
);

ALTER TABLE webhook_events ADD aspect_type      GENERATED ALWAYS AS (JSON_VALUE(payload, '$."aspect_type"')) VIRTUAL;
ALTER TABLE webhook_events ADD object_type      GENERATED ALWAYS AS (JSON_VALUE(payload, '$."object_type"')) VIRTUAL;
ALTER TABLE webhook_events ADD object_id NUMBER GENERATED ALWAYS AS (JSON_VALUE(payload, '$."object_id"'  )) VIRTUAL;

In Strava, times are held in Unix 'Epoch Time' (the number of non-leap seconds since midnight UTC on 1st Jan 1970).  I have created a deterministic PL/SQL function to convert it to an Oracle timestamp and have referenced it in my virtual column definition.  

One virtual column cannot reference another.  So, I could not reference the virtual column EVENT_TIME in another virtual column EVENT_TIMESTAMP.  Instead, I had to reference the event_time name-value pair in both column definitions.

CREATE OR REPLACE FUNCTION strava.epoch_to_tstz 
(p_epoch_seconds IN NUMBER
) RETURN TIMESTAMP DETERMINISTIC IS
BEGIN
  RETURN TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR') 
       + NUMTODSINTERVAL(p_epoch_seconds, 'SECOND');
END epoch_to_tstz;
/

ALTER TABLE webhook_events ADD event_time NUMBER 
   GENERATED ALWAYS AS (JSON_VALUE(payload, '$."event_time"' )) VIRTUAL;
ALTER TABLE webhook_events ADD event_timestamp TIMESTAMP WITH TIME ZONE 
   GENERATED ALWAYS AS (epoch_to_tstz(JSON_VALUE(payload, '$."event_time"'))) VIRTUAL;
…

If the message is an update, it contains a JSON object listing the updated items and their new values.  The column updates contains this JSON document.

ALTER TABLE webhook_events ADD updates GENERATED ALWAYS AS (JSON_QUERY(payload, '$."updates"' )) VIRTUAL;

I can now reference the virtual columns in SQL in the queue handler without converting and storing the values in regular columns.

…
  FOR i IN ( --interate requests
    SELECT h.*, a.activity_id
    FROM webhook_events h
      LEFT OUTER JOIN activities a ON a.activity_id = h.object_id 
    WHERE h.processing_status = 0
    AND   h.object_type = 'activity'
    ORDER BY h.id
    FOR UPDATE OF h.processing_status 
  ) LOOP
…

Oracle 23ai/26ai: The New RETURNING Clause for the MERGE Statement

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

The SQL MERGE statement was introduced in Oracle version 9i, allowing what is sometimes called UPSERT logic: a single SQL statement that conditionally inserts or updates rows.  However, one limitation remained.  Unlike INSERT, UPDATE, and DELETE, the MERGE statement did not support the RETURNING clause.  Oracle 23ai/26ai removes this restriction. Developers can now use the RETURNING clause directly in MERGE statements to retrieve values of affected rows. 

The Problem Before Oracle 23

Before Oracle 23, I would have to code a query loop capturing the values that were going to be updated and then update them in separate statements within the loop with additional exception handling as required.

…
  l_rows_processed := FALSE;

  FOR s IN (
    SELECT a.activity_id
    ,      listagg(DISTINCT ma.name,', ') WITHIN GROUP (ORDER BY ma.area_level, ma.name) area_list
    FROM   activities a
      INNER JOIN activity_areas aa ON a.activity_id = aa.activity_id
      INNER JOIN my_areas ma ON ma.area_code = aa.area_code and ma.area_number = aa.area_number
    WHERE a.activity_id = p_activity_id
    AND a.processing_status = 4
    And ma.matchable = 1
    GROUP BY a.activity_id;
  ) LOOP
    l_rows_processed := TRUE;

    UPDATE activities u
    SET    u.area_list = s.area_list
    WHERE  u.activity_id = s.activity_id

    update_activity_description(l_new_area_list,l_description);
  END LOOP;

  IF NOT l_rows_processed THEN 
    RAISE e_activity_not_found;
  END IF;
…

New Syntax in Oracle 23/26

Alternatively, I can use the MERGE statement to write a single SQL statement to generate the new value for a column and then update it in one go.  Now, the return clause also captures that new value in a variable that can be passed to another procedure.

MERGE INTO activities u
  USING (
    SELECT a.activity_id
    ,      listagg(DISTINCT ma.name,', ') WITHIN GROUP (ORDER BY ma.area_level, ma.name) area_list
    FROM   activities a
      INNER JOIN activity_areas aa on a.activity_id = aa.activity_id
      INNER JOIN my_areas ma on ma.area_code = aa.area_code and ma.area_number = aa.area_number
    WHERE a.activity_id = p_activity_id
    AND a.processing_status = 4
    AND ma.matchable = 1
    GROUP BY a.activity_id
  ) S 
  ON (s.activity_id = u.activity_id)
  WHEN MATCHED THEN UPDATE 
  SET u.area_list = s.area_list
  RETURNING new area_list INTO l_new_area_list; --new in Oracle 23
  
  IF SQL%ROWCOUNT = 0 THEN 
    RAISE e_activity_not_found;
  ELSE
    update_activity_description(l_new_area_list,l_description);
  END IF;

The benefits are

• Less and simpler code, which ought therefore to be easier to test and maintain, requiring less additional logic and exception handling.
• Fewer SQL statements and therefore fewer context switches between PL/SQL and SQL.

Just like the return clause on UPDATE and DELETE, it is also possible to

• Reference new and/or old column values,
• Single values into a scalar (single value) variable
• Bulk collect multiple rows into an array variable
• Aggregate multiple rows into a scalar variable

I am far from the first to blog about this feature, but it deserves to be better known.

See also:

• Andrej Pashchenko: MERGE and DML RETURNING clause in Oracle 23ai.
• Connor McDonald: MERGE in 23ai – so much more than RETURNING. This blog goes much further, also looking at bulk collecting multiple rows into an array.
• Tim Hall: DML RETURNING Clause Enhancements in Oracle Database 23ai/26ai.