Skip to content

Instantly share code, notes, and snippets.

@jon-dixon
Created January 22, 2023 01:25
Show Gist options
  • Save jon-dixon/f23003f5ec517da2adef179ed704cb2b to your computer and use it in GitHub Desktop.
Save jon-dixon/f23003f5ec517da2adef179ed704cb2b to your computer and use it in GitHub Desktop.
ORDS Webhooks POST Handler Procedure
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment