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.