Creating a Dynamic Calendar Feed

In this blog, I will discuss how to provide dynamic calendar feeds for users in Oracle APEX using ORDS.

Why Build a Dynamic Calendar Feed?

My app offers faculty a calendar feature to view their teaching schedules. The APEX component allows exporting to iCal, and many users like adding their schedules to their device's calendar app. The issue is that the data is static. If the schedule changes, the calendar apps won't show the latest information. For instance, if a teacher needs to substitute for another class, it won't appear in their calendar app. To fix this, we need to use an internet calendar subscription.

What is an Internet Calendar?

An Internet Calendar (or calendar subscription) is a link that allows users to add a live calendar to their personal calendar apps. It updates automatically with new events. This is often used for schedules, team events, or public calendars and is supported by most major apps like Google Calendar, Outlook, and Apple Calendar.

The .ics file format (iCalendar) is a standard format used for these feeds. It includes event details like title, date, time, location, and recurrence, and it works well across different platforms. In this blog, I won't go into the details of the .ics format, but I will explain how to offer users a dynamic feed.

My Approach

The first step was to generate the content of the .ics file based on a teacher’s unique schedule. The following function will then be called anytime a refresh of the calendar data is requested by the calendar app.

CREATE OR REPLACE FUNCTION generate_ics_file_teacher_schd (
   p_teacher_id   IN NUMBER)
   RETURN CLOB
IS
   l_ics CLOB := 'BEGIN:VCALENDAR' || CHR(13) ||
                 'VERSION:2.0' || CHR(13) ||
                 'X-WR-CALNAME:Class Schedule' || CHR(13) ||
                 'X-PUBLISHED-TTL:PT1H' || CHR(13);
BEGIN
   FOR r IN (
     SELECT event_title, event_start, event_end, location, uid
     FROM teacher_calendar_events
     WHERE teacher_id = p_teacher_id
   ) LOOP
     l_ics := l_ics || 'BEGIN:VEVENT' || CHR(13) ||
                      'UID:' || r.uid || CHR(13) ||
                      'DTSTAMP:' || TO_CHAR(SYSDATE, 'YYYYMMDD"T"HH24MISS"Z"') || CHR(13) ||
                      'DTSTART:' || TO_CHAR(FROM_TZ(CAST(r.event_start AS TIMESTAMP), 'America/Chicago') AT TIME ZONE 'UTC',
                                            'YYYYMMDD"T"HH24MISS"Z"') || CHR(13) ||
                      'DTEND:' || TO_CHAR(FROM_TZ(CAST(r.event_end AS TIMESTAMP), 'America/Chicago') AT TIME ZONE 'UTC',
                                          'YYYYMMDD"T"HH24MISS"Z"') || CHR(13) ||
                      'SUMMARY:' || r.event_title || CHR(13) ||
                      CASE
                        WHEN r.location IS NOT NULL THEN 'LOCATION:' || r.location || CHR(13)
                        ELSE ''
                      END ||
                      'END:VEVENT' || CHR(13);
   END LOOP;
   l_ics := l_ics || 'END:VCALENDAR';
   RETURN l_ics;
END;

Next, I created a module and resource handler in RESTful Services.

The Source Type is set to PL/SQL. Using PL/SQL gives you the ability to set specific headers needed for .ics files, particularly the Content-Type header as text/calendar. This is essential to ensure that calendar applications recognize the response as a calendar file.

Below is the Source:

DECLARE
   l_ics_content   CLOB;
BEGIN
   HTP.init;

   OWA_UTIL.mime_header ('text/calendar', FALSE);
   OWA_UTIL.http_header_close;

   l_ics_content := generate_ics_file_teacher_schd (:id);

   HTP.PRINT (l_ics_content);
END;

Lastly, I removed the iCal export option from the calendar component in my APEX app. Instead, I provided a popup with instructions for users on how to subscribe to their personal calendar feed.

The URL can now be used in most major calendar apps to subscribe to a personal class schedule that can be updated. Now when I am scheduled for a dreaded substitution assignment, it appears directly in my calendar app.

Conclusion

Creating a dynamic calendar feed using APEX and ORDS has been an interesting project that taught me a lot about both the potential and limitations of these tools. One key consideration was managing the amount of data served by ORDS.

Pro Tip: To maintain steady performance, I implemented a rolling date range that shows only the most relevant events—the past 7 days and the upcoming 60—ensuring users receive timely, up-to-date information without overwhelming ORDS or their calendar apps.

While I thought about adding alerts to the calendar feed, I realized it might not add much value since many calendar apps have notifications turned off for subscribed calendars by default. This keeps the feed focused on delivering relevant events without overwhelming users with reminders.

Through this experience, I learned how powerful ORDS and RESTful services are for delivering dynamic, user-centered content. They allowed me to build a flexible, live-updating calendar feed that’s easy to share across platforms. If you’re interested in building a similar feature, I highly encourage you to try creating your own dynamic calendar feed using APEX and ORDS. With the flexibility and control of PL/SQL and RESTful services, you can create highly personalized and useful features that enhance the user experience in ways static data just can't match.