Sample code to call a REST API to Get Changed Items

CN_MDM_UTL_PKB.sql

create or replace PACKAGE BODY CN_MDM_UTL_PK AS

  GC_SCOPE_PREFIX  CONSTANT VARCHAR2(100) := 'cn.mdm.'||LOWER($$plsql_unit) || '.';

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
PROCEDURE sync_changed_items (p_last_sync_time VARCHAR2) IS

  CURSOR cr_changed_items (cp_items_json IN CLOB) IS
    SELECT jt.*
    FROM   JSON_TABLE(cp_items_json, '$.items[*]'
            COLUMNS  (item_id              NUMBER         PATH '$.item_id',
                      item_number          VARCHAR2(50)   PATH '$.item_number',
                      item_description     VARCHAR2(4000) PATH '$.item_description',
                      uom_code             VARCHAR2(10)   PATH '$.uom_code',
                      order_enabled        VARCHAR2(1)    PATH '$.order_enabled',
                      purchase_enabled     VARCHAR2(1)    PATH '$.purchase_enabled',
                      status_code          VARCHAR2(10)   PATH '$.status_code',
                      lead_time_days       NUMBER         PATH '$.lead_time_days',
                      on_hand_quantity     NUMBER         PATH '$.on_hand_quantity',
                      date_introduced      TIMESTAMP WITH TIME ZONE PATH '$.date_introduced',
                      last_updated_utc     TIMESTAMP WITH TIME ZONE PATH '$.last_updated_utc')) jt;

  lt_parm_names        apex_application_global.VC_ARR2;
  lt_parm_values       apex_application_global.VC_ARR2;
  l_last_sync_time_utc TIMESTAMP WITH TIME ZONE;
  l_params_json        CLOB;
  l_items_clob         CLOB;
  l_base_url           VARCHAR2(500);
  l_item_exists        PLS_INTEGER;
  rest_call_failed     EXCEPTION;

BEGIN

  apex_automation.log_info('** Start **');
  apex_automation.log_info('p_last_sync_time: ' || p_last_sync_time);

  -- Fetch the Base URL for the Web Service from a remote server definiton
  --  stored at the workspace level. This avoids hard coding the URL of the REST API.
  SELECT base_url INTO l_base_url
  FROM   apex_workspace_remote_servers
  WHERE  remote_server_static_id = 'demo_cloudnueva_com_ords_demo';

  -- Store the Date and Time the Automation last run in the UTC Time zone.
  l_last_sync_time_utc := SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ
                           (p_last_sync_time, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'));

  -- Prepare 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';

  -- Build JSON used to pass the last changed date to the REST API on the Remote Server.
  --  In this example, the remote REST API is an ORDS Service, so we are using 
  --  ORDS query syntax that produces a query string like this:
  --  q={"last_updated_utc":{"$gt":{"$date":"2023-12-03T19:56:44Z"}}}
  apex_json.initialize_clob_output();
  apex_json.open_object ();
  apex_json.open_object ('last_updated_utc');
  apex_json.open_object ('$gt');
  apex_json.write('$date', TO_CHAR(l_last_sync_time_utc, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'));
  apex_json.close_all;
  l_params_json := apex_json.get_clob_output;
  apex_json.free_output;

  -- Prepare the Query String Parameters for the REST API Call.
  lt_parm_names.DELETE;
  lt_parm_names(1)  := 'q';
  lt_parm_values(1) := l_params_json;

  -- Call the REST API.
  l_items_clob := apex_web_service.make_rest_request
    (p_url                  => l_base_url || 'mdm/items',
     p_http_method          => 'GET',
     p_transfer_timeout     => 10,
     p_parm_name            => lt_parm_names,
     p_parm_value           => lt_parm_values,
     p_credential_static_id => 'credentials_for_mdm_items',
     p_token_url            => l_base_url || 'oauth/token');
  apex_automation.log_info('Status: ' || apex_web_service.g_status_code);

  IF apex_web_service.g_status_code = 201 THEN
    -- Loop through the changed items returned from the REST API.
    FOR r_item IN cr_changed_items (cp_items_json => l_items_clob) LOOP
      -- Check if the item already exists in the local table.
      SELECT COUNT(1) INTO l_item_exists
      FROM   mdm_items_local
      WHERE  item_id = r_item.item_id;

      IF l_item_exists = 0 THEN
        -- Create the item in the local table.
        apex_automation.log_info(' > Adding Item: ' || r_item.item_number);
        INSERT INTO mdm_items_local 
         (item_id, item_number, item_description,
          uom_code, order_enabled, purchase_enabled,
          status_code, lead_time_days, on_hand_quantity,
          date_introduced, last_updated_utc)
        VALUES 
         (r_item.item_id, r_item.item_number, r_item.item_description,
          r_item.uom_code, r_item.order_enabled, r_item.purchase_enabled,
          r_item.status_code, r_item.lead_time_days, r_item.on_hand_quantity,
          r_item.date_introduced, r_item.last_updated_utc);
      ELSE
        apex_automation.log_info(' > Updating Item: ' || r_item.item_number);
        -- Update the item in the local table.
        UPDATE mdm_items_local
        SET    item_number      = r_item.item_number
        ,      item_description = r_item.item_description
        ,      uom_code         = r_item.uom_code
        ,      order_enabled    = r_item.order_enabled
        ,      purchase_enabled = r_item.purchase_enabled
        ,      status_code      = r_item.status_code
        ,      lead_time_days   = r_item.lead_time_days
        ,      on_hand_quantity = r_item.on_hand_quantity
        ,      date_introduced  = r_item.date_introduced
        ,      last_updated_utc = r_item.last_updated_utc
        WHERE  item_id = r_item.item_id;
      END IF;
    END LOOP;

  ELSE
    raise rest_call_failed;
  END IF;

EXCEPTION WHEN rest_call_failed THEN
  apex_automation.log_error('Call to REST API failed');
  RAISE;
WHEN OTHERS THEN
  apex_automation.log_error('Unhandled Error ['||SQLERRM||']');
  RAISE;
END sync_changed_items;

END CN_MDM_UTL_PK;
/