Secure IOT device uploads to Oracle Database via REST API

import http.client
import sys

conn = http.client.HTTPConnection("localhost:8181")
filename = sys.argv[1]

headers = {
     'Content-Type' : "application/json",
     'file' : filename
    }

with open(filename) as payload:
    conn.request("POST", "/ords/hr/_/par/ueh1MfXggkTzH2dZGSoXR1NhIjmt8MzzuNrk1DxS3kqhZQipolL5wuXa8miYWxaylrmF2vnY5EhZxbns5Pidf0Dp7Lw09Lcuu6HdtS3mIDplmH90iy0FooAUaYJ8t/thatjeffsmith/sensor_data/", payload, headers)

res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))

-- assumes your database is version 23ai
-- with a few tweaks, 100% possible in 19c 

-- create the table to store the data 
CREATE TABLE IF NOT EXISTS SENSOR_READINGS 
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	DEVICE_ID VARCHAR2(50), 
	SENSOR_TIME DATE DEFAULT sysdate, 
	TEMPERATURE NUMBER(5,2), 
	HUMIDITY NUMBER, 
	BAROMETRIC NUMBER, 
	 PRIMARY KEY (ID);
	 
-- process JSON to insert into table 
create or replace PROCEDURE add_sensor_data(
    p_json_data IN JSON, rows_added out varchar2
) AS
    v_rowcount NUMBER;
BEGIN
    -- Single INSERT statement with JSON_TABLE
    -- JSON array is read into a tabular format via JSON_TABLE Function
    -- $.notation allows youo to take attributes from JSON object and 
    --   map to columns
    -- Note that the date value is read in as a string and then formatted
    --   as a date before being inserted 
    INSERT INTO sensor_readings (
        device_id,
        temperature,
        humidity,
        sensor_time,
        barometric
    )
    SELECT device_id,
           temp,
           humidity,
           to_date(sensor_time, 'YYYY-MM-DD HH24:MI:SS'),
           barometric
    FROM JSON_TABLE(
        p_json_data,
        '$[*]'
        COLUMNS (
            device_id VARCHAR2(50) PATH '$.deviceId',
            temp number PATH '$.temp',
            humidity number PATH '$.humidity',
            sensor_time varchar2(50) PATH '$.time',
            barometric NUMBER PATH '$.mb'
        )
    );

    -- Capture the number of rows inserted
    v_rowcount := SQL%ROWCOUNT;
    rows_added := v_rowcount;

    -- Commit the transaction
    COMMIT;

    -- Output the result
    -- only useful while 'debugging' in database client 
    -- this value is also returned via OUT parameter for REST API 
    DBMS_OUTPUT.PUT_LINE(v_rowcount || ' records inserted successfully');

    -- do BETTER than WHEN OTHERS, but this will catch everything that
    --  fails and print error stack on oracle client console 
EXCEPTION
    WHEN OTHERS THEN
        -- Rollback on error
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
        RAISE;
END add_sensor_data;
/

-- Create the REST API 

-- Generated by ORDS REST Data Services 24.4.0.r3451601
-- Schema: HR  Date: Tue Mar 25 07:04:34 2025 
--
        
DECLARE
  l_roles     OWA.VC_ARR;
  l_modules   OWA.VC_ARR;
  l_patterns  OWA.VC_ARR;

BEGIN
  -- assumes ORDS is installed for your database
  -- replace HR for schema where your table/procedure were created
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'HR',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'hr',
      p_auto_rest_auth      => FALSE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'thatjeffsmith',
      p_base_path      => '/thatjeffsmith/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'For thatjeffsmith.com Oracle Database tutorials. Please review and test all code before copy/pasting into your projects.');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'thatjeffsmith',
      p_pattern        => 'sensor_data/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'For storage of your IOT Sensor data. JSON Object Definition:

{
  "deviceId": "text",
  "temp": 65.4,
  "humidity": 56,
  "time": "DDDD-MM-DD HR24:MI:SS"",
  "mb": 1015.6
}');

-- this is where the POST handler logic is defined 
-- :body_text is an implicit :bind variable that makes POST REQUEST BODY
--    payload available to plsql program as a CLOB 
--    we convert that with JSON() function as we send to PL/SQL API
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'thatjeffsmith',
      p_pattern        => 'sensor_data/',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => 'Accepts an array of our objects, example: 

[
    {"deviceId": "Sue", "temp": 65.4, "humidity": 56, "time": "2024-03-24 07:30:01", "mb": 1015.6},
    {"deviceId": "Sue", "temp": 65.8, "humidity": 55, "time": "2024-03-24 07:31:01", "mb": 1015.7},
    {"deviceId": "Sue", "temp": 66.1, "humidity": 54, "time": "2024-03-24 07:32:01", "mb": 1015.5}
]',
      p_source         => 
'begin
    add_sensor_data(p_json_data => json(:body_text), rows_added => :response);
    :status_code := 201;
EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            :message := ''Sorry cannot find who you are looking for, get with the program'';
            :status := 400;
        WHEN VALUE_ERROR
        THEN
            :message := ''Your name and message were, weird? Check inputs and try again.'';
            :status := 400;
        WHEN OTHERS
        THEN
            :message := ''That does not look quite right, ensure your payload matches this format [{"deviceId": "Sue", "temp": 65.4, "humidity": 56, "time": "2024-03-24 07:30:01", "mb": 1015.6},...]. Here is the system response: '' || SQLERRM;
            :status := 400;
end;');

-- this captures. the OUT parameter for PLSQL api to get number of rows added
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'thatjeffsmith',
      p_pattern            => 'sensor_data/',
      p_method             => 'POST',
      p_name               => 'rowsAdded',
      p_bind_variable_name => 'response',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);

-- this captures oracle error message returned from calling PL/SQL program

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'thatjeffsmith',
      p_pattern            => 'sensor_data/',
      p_method             => 'POST',
      p_name               => 'message',
      p_bind_variable_name => 'message',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);

-- SECURITY Mechanism for locking down access to API
    
  ORDS.CREATE_ROLE(p_role_name => 'DEMO');
    
  l_roles(1) := 'DEMO';
  l_modules(1) := 'thatjeffsmith';

  ORDS.DEFINE_PRIVILEGE(
      p_privilege_name => 'sensors',
      p_roles          => l_roles,
      p_patterns       => l_patterns,
      p_modules        => l_modules,
      p_label          => 'sensors',
      p_description    => 'lock down IOT device APIs',
      p_comments       => NULL);

  l_roles.DELETE;
  l_modules.DELETE;
  l_patterns.DELETE;
        
COMMIT;

END;
/

-- Creating. the Preq-Authenticated Request
-- Pick-up the PAR URL output from this block to substitute in 
--   line #13 of sensor-data.py 
DECLARE
  l_uri clob;
BEGIN
   l_uri := ORDS_PAR.DEFINE_FOR_HANDLER(
    p_module_name => 'thatjeffsmith',
    p_pattern => 'sensor_data/',
    p_method => 'POST',
    p_duration => 15769728
  );
  COMMIT;
  dbms_output.put_line(l_uri);
END;
/