Function to query TEQ JSON payloads in SQL

get_event_data.sql

create or replace function get_event_data(p_raw in raw, p_blob in blob) return clob
as
  key_len          PLS_INTEGER;
  value_offset     PLS_INTEGER;
  value_len        PLS_INTEGER;
  value_len_offset PLS_INTEGER;
  value_raw        raw(2000);
  temp_raw         raw(8);
  len_size         PLS_INTEGER := 4;
  begin_offset     PLS_INTEGER := 1;
  l_clob           CLOB;
  l_lang_context   PLS_INTEGER := DBMS_LOB.default_lang_ctx;
  l_warning        PLS_INTEGER;
begin
  if p_raw is not null then
    key_len := UTL_RAW.CAST_TO_BINARY_INTEGER( substr(p_raw, 0, 8), utl_raw.BIG_ENDIAN);   
    value_len_offset := 8 + (key_len)*2 + 1;
    value_len := UTL_RAW.CAST_TO_BINARY_INTEGER (substr(p_raw, value_len_offset, 8), utl_raw.BIG_ENDIAN);   
    value_offset := value_len_offset + 8;
    value_raw := substr(p_raw, value_offset, value_len*2);
    DBMS_LOB.createTemporary(lob_loc => l_clob, cache => TRUE);
    DBMS_LOB.WRITE(l_clob, value_len, 1, UTL_RAW.CAST_TO_VARCHAR2(value_raw));
    return l_clob;    
  elsif p_blob is not null then
    DBMS_LOB.READ(p_blob, len_size, begin_offset, temp_raw);
    key_len := UTL_RAW.CAST_TO_BINARY_INTEGER(temp_raw,utl_raw.BIG_ENDIAN);  
    value_len_offset := key_len + 4 + 1;
    DBMS_LOB.READ(p_blob, len_size, value_len_offset, temp_raw);
    value_len := UTL_RAW.CAST_TO_BINARY_INTEGER(temp_raw,utl_raw.BIG_ENDIAN);
    value_offset := value_len_offset + 4;
    DBMS_LOB.createTemporary(lob_loc => l_clob, cache => TRUE);
    DBMS_LOB.converttoclob(dest_lob => l_clob, src_blob => p_blob, amount => value_len, dest_offset   => begin_offset,
        src_offset => value_offset, blob_csid => NLS_CHARSET_ID('AL32UTF8'), lang_context => l_lang_context, warning => l_warning);
    return l_clob;
  else
    return null;
  end if;
end;
/

-- Query example
with data as (select msgid, enqueue_time, step_number, json(get_event_data(userdata_raw, userdata_blob)) as doc from sensors)
  select d.enqueue_time, 
         d.doc 
    from data d
   where d.enqueue_time > current_timestamp - interval '10' minute             
order by d.enqueue_time desc fetch first 4 rows only;