OCI Queue Post Messages to the Queue

oci_queue_post_msg_example.sql

DECLARE
  l_payload         CLOB;
  l_response        CLOB;
  l_rest_api_url    VARCHAR2(255);
  l_item_obj        json_object_t := json_object_t();
  l_content_obj     json_object_t := json_object_t();
  l_json_obj        json_object_t := json_object_t();
  l_msgs_arr        json_array_t  := json_array_t();
BEGIN
  -- Handle Create/Update of Interactive Grid Record.
  CASE :APEX$ROW_STATUS
    WHEN 'C' THEN
      INSERT INTO DEMO_LOCAL_ITEMS (item_number, item_description, country_of_mfg, category_code)
      VALUES (:ITEM_NUMBER, :ITEM_DESCRIPTION, :COUNTRY_OF_MFG, :CATEGORY_CODE) RETURNING item_id INTO :ITEM_ID;
    WHEN 'U' THEN
      UPDATE DEMO_LOCAL_ITEMS
      SET    item_number      = :ITEM_NUMBER
      ,      item_description = :ITEM_DESCRIPTION
      ,      country_of_mfg   = :COUNTRY_OF_MFG
      ,      category_code    = :CATEGORY_CODE
      WHERE  item_id = :ITEM_ID;
  END CASE;

  -- Build the JSON for the Item Object.
  l_item_obj.put('item_id', :ITEM_ID);
  l_item_obj.put('item_number', :ITEM_NUMBER);
  l_item_obj.put('item_description', :ITEM_DESCRIPTION);
  l_item_obj.put('country_of_mfg', :COUNTRY_OF_MFG);
  l_item_obj.put('category_code', :CATEGORY_CODE);
  -- Append the item object as a string to the content object.
  -- The content object could be anything, not just JSON so we can't treat it like JSON.
  l_content_obj.put('content',l_item_obj.to_clob());
  -- Append the conent object to the messages array (this could be a loop for multiple messages)
  l_msgs_arr.append(l_content_obj);
  -- Add the messages array to the JSON object.
  l_json_obj.put('messages', l_msgs_arr);
  -- Convert to a CLOB in order to pass it to the REST API.
  l_payload := l_json_obj.to_clob();
  apex_debug.info('XX Payload [%s]', l_payload);

  -- Build the URL for the POST messages API
  l_rest_api_url := 'https://cell-1.queue.messaging.us-ashburn-1.oci.oraclecloud.com/20210201/queues/<OCID>/messages';

  -- Set the HTTP Headers for the REST API Call.
  apex_web_service.g_request_headers.DELETE;
  apex_web_service.g_request_headers(1).NAME := 'Content-Type';
  apex_web_service.g_request_headers(1).VALUE := 'application/json';

  -- Post the message to the Queue.
  -- Reference the OCI APEX Web Credential 'APEX_OCI_BLOG_CREDENTIAL' to authenticate the call.
  l_response := apex_web_service.make_rest_request
    (p_url                  => l_rest_api_url,
     p_http_method          => 'POST',
     p_body                 => l_payload,
     p_credential_static_id => 'APEX_OCI_BLOG_CREDENTIAL');
  apex_debug.info('XX Status [%s], Response [%s]', apex_web_service.g_status_code, l_response);

  IF apex_web_service.g_status_code = 200 THEN
    -- Optionally add code here to parse the response and get the created message IDs.
    NULL;
  ELSE
    -- Raise an Error as the Item Create/Update was not added too the Queue.
    apex_debug.error('XX Unable to Queue Item. Status [%s], Response [%s]', apex_web_service.g_status_code, l_response);
    apex_error.add_error
     (p_message          => 'Unable to Queue Item',
      p_display_location => apex_error.c_inline_in_notification );
  END IF;
END;