ORDS Webhooks POST Handler Procedure

ORDS_WEBHOOKS_POST_HANDLER.sql

PROCEDURE sendgrid_webhook 
  (p_json   IN CLOB,
   x_status OUT NUMBER) IS

  l_logger_scope        logger_logs.SCOPE%TYPE := GC_SCOPE_PREFIX || utl_call_stack.subprogram(1)(2);
  l_logger_params       logger.tab_param;

BEGIN

  -- Assume Success.
  x_status := 200;

  logger.append_param(l_logger_params, 'json', SUBSTR(p_json,1,500));

  INSERT INTO sendgrid_events 
    (source_code, source_id, email_type_code,
     sg_event_id,sg_message_id,sg_orig_msg_id,
     event,email,event_timestamp,
     reason,bounce_classification,type,status)
  SELECT jt.source_code
  ,      jt.source_id
  ,      jt.email_type_code
  ,      jt.sg_event_id
  ,      jt.sg_message_id
  ,      SUBSTR(jt.sg_message_id,1,INSTR(jt.sg_message_id,'.')-1)
  ,      jt.event
  ,      jt.email
  ,      to_timestamp_tz('1970-01-01 +0:00', 'YYYY-MM-DD TZH:TZM') + numtodsinterval(jt.event_timestamp, 'SECOND') event_time
  ,      jt.reason
  ,      jt.bounce_classification
  ,      jt.type
  ,      jt.status
  FROM   JSON_TABLE(p_json, '$[*]'
           COLUMNS (source_code            VARCHAR2(100)  PATH '$.source_code',
                    source_id              NUMBER         PATH '$.source_id',
                    email_type_code        VARCHAR2(100)  PATH '$.email_type_code',
                    sg_event_id            VARCHAR2(100)  PATH '$.sg_event_id',
                    sg_message_id          VARCHAR2(100)  PATH '$.sg_message_id',
                    event                  VARCHAR2(100)  PATH '$.event',
                    email                  VARCHAR2(100)  PATH '$.email',
                    event_timestamp        NUMBER         PATH '$.timestamp',
                    reason                 VARCHAR2(500)  PATH '$.reason',
                    bounce_classification  VARCHAR2(500)  PATH '$.bounce_classification',
                    type                   VARCHAR2(75)   PATH '$.type',
                    status                 VARCHAR2(75)   PATH '$.status')) jt;

  logger.log('Complete', l_logger_scope, NULL, l_logger_params);
  
EXCEPTION WHEN OTHERS THEN
  x_status := 400;
  logger.log_error('Unhandled Error: ['||SQLERRM||']', l_logger_scope, NULL, l_logger_params);
END sendgrid_webhook;