Skip to content

Instantly share code, notes, and snippets.

@jon-dixon
Last active December 31, 2022 16:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jon-dixon/aab1d461bc85de4d995a93672b7102fa to your computer and use it in GitHub Desktop.
Save jon-dixon/aab1d461bc85de4d995a93672b7102fa to your computer and use it in GitHub Desktop.
OCI Queue Post Messages to the Queue
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment