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 thesiteId
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 driveid
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 driveid
obtained previously. Change#FOLDER_NAME#
to the foldername
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 driveid
obtained previously. Change#ITEM_ID#
to the itemid
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.
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.
Deleting Files and Folders
You can delete a file or folder by sending a DELETE
request to the item
resource.
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.
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