Blog APEX & Office 365 Sharepoint Integration

sp_sites.sql

FUNCTION sp_sites
  (p_filter IN VARCHAR2 DEFAULT NULL) RETURN tt_sp_sites PIPELINED IS

  CURSOR cr_sp_sites (cp_json IN CLOB) IS
    SELECT jt.*
    FROM   JSON_TABLE(cp_json, '$.value[*]'
             COLUMNS (name         VARCHAR2(100)  PATH '$.name',
                      display_name VARCHAR2(100)  PATH '$.displayName',
                      site_id      VARCHAR2(100)  PATH '$.sharepointIds.siteId',
                      site_url     VARCHAR2(500)  PATH '$.sharepointIds.siteUrl')) jt;

  l_response_clob     CLOB;
  lt_parm_names       apex_application_global.VC_ARR2;
  lt_parm_values      apex_application_global.VC_ARR2;
  row_data            sp_site_rec;
  row_data_miss       sp_site_rec;

BEGIN

  -- Set HTTP Request Headers.
  apex_web_service.clear_request_headers;
  apex_web_service.set_request_headers
   (p_name_01   => 'Content-Type',
    p_value_01  => 'application/json',
    p_name_02   => 'Prefer',
    p_value_02  => GC_GRAPH_API_VER);

  -- Add $select parameter to fetch the minimum fields.
  lt_parm_names(1)  := '$select';
  lt_parm_values(1) := 'name,displayName,sharepointIds';
  
  -- Apply a filter if one was passed in.
  IF p_filter IS NOT NULL THEN
    lt_parm_names(2)  := '$filter';
    lt_parm_values(2) := p_filter;
  END IF;

  -- Call the SharePoint Sites Web Service.
  l_response_clob := apex_web_service.make_rest_request
    (p_url                  => GC_SP_SITES_URL,
     p_http_method          => 'GET',
     p_transfer_timeout     => GC_TIMEOUT_SECS,
     p_parm_name            => lt_parm_names,
     p_parm_value           => lt_parm_values,
     p_token_url            => token_url,
     p_credential_static_id => GC_SP_CRED_ID);

  IF apex_web_service.g_status_code = 200 THEN
    FOR r_site IN cr_sp_sites (cp_json => l_response_clob) LOOP
      BEGIN
        row_data              := row_data_miss;
        row_data.name         := r_site.name;
        row_data.display_name := r_site.display_name;
        row_data.site_id      := r_site.site_id;
        row_data.site_url     := r_site.site_url;
        pipe row(row_data);
      EXCEPTION WHEN OTHERS THEN
        row_data.status_code := 'W';
        row_data.status_msg  := SQLERRM;
        pipe row(row_data);
      END;
    END LOOP;
  ELSE
    row_data.status_code := 'E'; 
    row_data.status_msg  := 'Response: '||SUBSTR(l_response_clob,1,450);
    pipe row(row_data);
  END IF;

EXCEPTION WHEN NO_DATA_NEEDED THEN 
  NULL;
WHEN OTHERS THEN
  row_data.status_code := 'E'; 
  row_data.status_msg  := 'Error: '||SQLERRM;
  pipe row(row_data);
END sp_sites;