Introduction

In my previous post APEX & Office 365 Calendar Integration, I showed you how to integrate an MS Office 365 Calendar with an Oracle APEX Calendar Region. This post will show you how to integrate Oracle APEX with MS Office 365 Sharepoint. I have expanded on my APEX Office 365 Integration demo App to include the functionality of navigating through SharePoint folders and downloading a file from SharePoint. You can get the latest Demo App source code from my Github Repository.

Storing files in Sharepoint instead of your database replaces more expensive database storage with cheap cloud storage. It saves money, reduces database backup and restore times, and enables files to be shared across the enterprise.

This post is split into four parts:

• MS Azure AD Configurations
• Test SharePoint APIs with Postman
• APEX Integration
• Other SharePoint APIs to Explore

MS Azure AD Configurations

This post assumes you have read the first two posts in the series APEX & Office 365 Integration - Introduction and APEX & Office 365 Calendar Integration. It also assumes that you have already set up a Microsoft Active Directory App registration using 'Microsoft Entra'. Before proceeding, you will need to add the following permissions to your App registration:

• Sites.Read.All (allows you to list all SharePoint sites for the tenant)
• Files.ReadWrite.All (allows you to read and write files and folders)

Test SharePoint APIs with Postman

In this section, I will show you how to call the various MS Graph APIs we will use to list and download files in SharePoint.

MS Graph API Access Token

Please refer to my previous post APEX & Office 365 Integration - Introduction for details on obtaining an MS Graph token using Postman. The token returned from this step will be used as a Bearer Token in the following steps.

List Sharepoint Sites

• 📖 MS Graph Resource Documentation
• 🔗 Endpoint: https://graph.microsoft.com/v1.0/sites

First, we need to get the site ID of the Sharepoint site we want to access. I am using the $filter Parameter to get details for a SharePoint site called 'JD Test'. I am using the $select parameter to fetch just the name and sharepointIds.

❗Limiting the size of the response using these parameters improves performance. See my post Improving Performance when Consuming REST Services with Oracle APEX for more details. ✏️ Take note of the siteId field in the response.

Get Default Drive for the Sharepoint Site

• 📖 MS Graph Resource Documentation
• 🔗 Endpoint: https://graph.microsoft.com/v1.0/sites/#SITE_ID#/drive. Change #SITE_ID# to the siteId obtained in the previous step.

Next, we need to get the default Drive associated with the SharePoint site. ✏️ Take note of the id field in the response.

List Folders in a Sharepoint Drive

• 📖 MS Graph Resource Documentation
• 🔗 Endpoint: https://graph.microsoft.com/v1.0/drives/#DRIVE_ID#/root/children. Change #DRIVE_ID# to the drive id obtained in the previous step.

This step will fetch the root-level files and folders in the default drive associated with our SharePoint site. I use the $select parameter to select specific fields from the resource. ✏️ Take note of the name of one of the folders.

List Children of a Drive Item

• 📖 MS Graph Resource Documentation
• 🔗 Endpoint: https://graph.microsoft.com/v1.0/drives/#DRIVE_ID#/root:/#FOLDER_NAME#:/children. Change #DRIVE_ID# to the drive id obtained previously. Change #FOLDER_NAME# to the folder name obtained in the previous step.

In this step, we are listing folders and files that are within Folder1: ✏️ Take note of the id of one of the files.

Download a DriveItem

• 📖 MS Graph Resource Documentation
• 🔗 Endpoint: https://graph.microsoft.com/v1.0/drives/#DRIVE_ID#/items/#ITEM_ID#/content. Change #DRIVE_ID# to the drive id obtained previously. Change #ITEM_ID# to the item id obtained in the previous step.

The final step is to download the content of a Drive Item. ❗Remember to pass the mime type of the file in the Content-Type HTTP Header. The mime type is returned in the file.mimeType field of the 'List children of a Drive Item' service described above.

APEX Integration

Now that we understand how the SharePoint MS Graph APIs work, we can start integrating them with APEX. I developed a demo APEX App to show how this integration can be done. You can download the demo App from my Github Repository. In this section, I will highlight some key aspects of the demo App.

Pipelined Table Functions

I developed several pipelined table functions to make consuming data from the MS Graph APIs in APEX easier. Pipelined table functions are PL/SQL functions that allow you to return rows in the same way as you do from a SQL statement. The difference is that you can have any PL/SQL code you want generating and emitting these rows. Ideally, I would have used APEX REST Data Sources, but in this case, pipelined functions provide more flexibility to perform multiple operations within one call. Most of the pipelined functions follow the same pattern:

• Call the appropriate MS Graph API using APEX_WEB_SERVICE
• Parse the JSON response using JSON_TABLE
• Return rows of data for APEX to consume

SharePoint Site and Drive Selection

Page 30 of the Demo App returns a list of Sharepoint sites for the Office 365 Tenant. The list of SharePoint sites is generated using the pipelined function sp_sites

SELECT *
FROM   TABLE(cn_ms_graph_utl_pk.sp_sites (p_filter => NULL))

I have included the code for the 'sp_sites' pipelined function below. This function returns a list of all SharePoint Sites for the tenant.

❗Note the parameter p_token_url in the APEX_WEB_SERVICE call. token_url is a function I developed that takes the Microsoft OAuth server URL and adds my Microsoft Office 365 tenant ID to it. This function turns https://login.microsoftonline.com/#TENANTID#/oauth2/v2.0/token into something like this https://login.microsoftonline.com/484494rt-88e5-3edr-9ic4-9589584rt21/oauth2/v2.0/token

When you select a SharePoint site on page 30, you are re-directed to page 35. The Before Header Process Set SharePoint Drive ID on page 35 fetches the Drive ID related to the Selected SharePoint Site using the following code:

SELECT id INTO :P35_SP_SITE_DRIVE_ID
FROM   TABLE(cn_ms_graph_utl_pk.sp_site_drive (p_sp_site_id  => :P35_SP_SITE_ID));

Traversing Folders

Page 35 uses the Drive ID to list files and folders within that drive. The following pipelined function fetches the files and folders for the current drive and path:

SELECT *
FROM   TABLE(cn_ms_graph_utl_pk.sp_folder_list 
              (p_drive_id  => :P35_SP_SITE_DRIVE_ID,
               p_full_path => :P35_FILE_PATH))

The page is submitted whenever you click on a link in the 'Name' column. If the link is a folder, then code in the Before Header process Set Current Folder appends the selected folder to the current path within the drive.

Download a File

If you click on a link in the 'Name' column which is a file, the Before Page Process Download File calls the cn_ms_graph_utl_pk.download_file PL/SQL API. This procedure fetches the file from SharePoint and then downloads the file to your desktop.

Other SharePoint APIs to Explore

There are several MS Graph APIs related to managing files in SharePoint that I have not covered in this post. This post would be far too long if I went into detail about all of them. In this section, I will highlight some of these APIs so you can look into them yourself.

Creating Folders

You can create folders by sending a POST request with a simple JSON document to the parent folder URL.

• 📖 Create Folder Documentation

Uploading Files

Microsoft has two APIs for uploading files. For files <= 4MB in size, you can use the 'upload' API. For larger than 4MB, you must establish an upload session and upload the file in chunks; each chunk must be 60MB or less.

• 📖 Upload Files <= 4MB Documentation
• 📖 Upload files > 4MB Documentation.

Deleting Files and Folders

You can delete a file or folder by sending a DELETE request to the item resource.

• 📖 Delete File or Folder Documentation

Search

One of the most powerful aspects of Microsoft SharePoint and OneDrive is that you can search the content of files. Microsoft offers a Graph API that provides the same powerful document search functionality via a REST call.

• 📖 Search Files Documentation

Conclusion

This post showed you how you can integrate SharePoint folders and files with your Oracle APEX Applications.

Moving files from more expensive database storage to a cloud-based object storage solution is a good idea. It saves money, reduces database backup and restore times, and enables these files to be shared more easily across the entire enterprise.

Check out the rest of the series on integrating APEX with MS Office 📖 MS Office 365 Integration Series

🔗 Read More

• 🩳 APEX Shorts
• #️⃣ APEX Posts
• #️⃣ ORDS Posts