ICS File - Calendar Invitations

Calendar invitations are crude text files that follows standards established in RFC 5545. Creating these files from within Oracle PL/SQL requires understanding the rules. Often when putting together a new utility, I think if my youngest days with Lego (yes, my first computer was actually made from Lego. My inspiration came from the 1960s Bat Computer and the rectangular punch cards that could be found in every neighbor’s home.) You snap existing stuff together in new ways (or old ways). The information I read didn’t actually allow me to create an invitation that was accepted by Microsoft Outlook 365.

Step 0 – Read a few blogs & Wing it!

Other blogs and toolkits emphasized the simplicity of these vCalendar text files. I’d write one in NotePadd++, then pull it into MS Outlook 365. I got error messages, most commonly:

“We couldn’t find this meeting in the calendar. It may have been moved or deleted.”

Well, thank you says I. It isn’t in the calendar; you are so right my dear error message. I want to add it to my calendar. No amount of clicking accept helped (nor did the error message).

Step 1 – Actually Research

The calendar format (vCalendar, ics, ical, etc) is defined in RFC 5545. The authoritative references is here: https://icalendar.org/RFC-Specifications/iCalendar-RFC-5545/

Reading the specifications is made more boring by having to click next after each paragraph. Apparently, the Spartan-inspired authors forbade skimming. Even the Oracle document gives the occasional example, I couldn’t find any on their website.

Step 2 – Hack a message that Works

In my Outlook, I found a robust activity on my calendar: it came from outside source; it had a location; it had long description; it had multiple attendees. I exported it as text file with an ics extension. This, when I opened it in NotePad++, clearly looked like the stuff in RFC 5545.

I changed a date and time, saved it and Outlook gladly accept it as a new invitation. It also imported well into Google Calendar.

BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook 16.0 MIMEDIR//EN
VERSION:2.0
METHOD:REQUEST
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VEVENT
ATTENDEE;CN="Barnibus A Rubble";RSVP=FALSE:mailto:barney.rubble@fakeemail.com
ATTENDEE;CN=frederick.flintstone@fakeemail.com;RSVP=FALSE:mailto:frederick.fl
	intstone@fakeemail.com
ATTENDEE;CN="Christina Moore";RSVP=FALSE:mailto:not.here@storm-petrel.com
CLASS:PUBLIC
CREATED:20161216T134426Z
DESCRIPTION:You have been invited to a join.me online meeting \n\nJoin the 
	meeting: https://join.me/************ \n\nOn a computer\, use any browser. 
	Nothing to download. \nOn a phone or tablet\, launch the join.me app and e
	nter meeting code: *********** \n\nJoin the audio conference: \nDial a pho
	ne number and enter access code\, or connect via internet. \n\nBy phone: \
	nUnited States - Los Angeles\, CA   +1.213.***.1066 \nUnited States - New 
	York\, NY   +1.646.***.1990 \nUnited States - Hartford\, CT   +1.***.970.0
	010 \nUnited States - Camden\, DE   +1.***.202.5900 \nUnited States - Tamp
	a\, FL   +1.813.***.0500 \nUnited States - Washington\, DC   +1.***.602.12
	95 \nUnited States - San Francisco\, CA   +1.***.594.5500 \nUnited States 
	- Atlanta\, GA   +1.404.***.8750 \nAccess Code   ***-687-515# \n\nOther in
	ternational numbers available \n\nBy computer via internet: \nJoin the mee
	ting\, click the phone icon and select 'Call via internet'. A small downlo
	ad might be required. \n\nStart time by time zones \n\n\n
DTEND:20161216T160000Z
DTSTAMP:20161207T162659Z
DTSTART:20161216T150000Z
LAST-MODIFIED:20161216T134426Z
LOCATION:join.me/*******\, see conference numbers in the invitation
ORGANIZER;CN="Christina Moore via join.me":mailto:scheduler@****.me
PRIORITY:5
SEQUENCE:2
SUMMARY;LANGUAGE=en-us:Tempest-Bid (CB&I)
TRANSP:OPAQUE
UID:11235811@******rel.com
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
X-MICROSOFT-CDO-INTENDEDSTATUS:BUSY
END:VEVENT
END:VCALENDAR

Step 3 – Find the rules that matter

My good luck with my Join.Me calendar invitations are that they have a lot of text and a number of attendees. When taking a look at the source text, it became clear that more steps are required then concatenating text.

  1. Lines are Folded (RFC 5545 3.1 Content Lines)
  2. Date/Time are presented as ISO 8601 Format
  3. Time Zones Matter

The iCalendar.org website provides a Validator tool.

3.1 Lines Are Folded

JSON and XML structures identify field name and data. The vCalendar text does not. Rooted firmly in the early days of network computers, it uses the format of the text to discriminate data from not-data. To that end, lines are folded.

  • A line ends with carriage return/linefeed [CRLF, concat(chr(10), chr(13)), etc].
  • A line must not be longer than 75 bytes
  • If a line is longer, cut it off with a CRLF, start a new line with a horizontal tab [HTAB, chr(9)]

This is discussed here:

To preserve your original CRLF you’ll need to replace them with ‘\n’. I observed that the comma character [chr(44)] was also escaped as ‘\,’

So before folding your lines, escape your CRLF and commas.

DESCRIPTION:You have been invited to a join.me online meeting \n\nJoin the 
	meeting: https://join.me/************ \n\nOn a computer\, use any browser. 
	Nothing to download. \nOn a phone or tablet\, launch the join.me app and e
	nter meeting code: *********** \n\nJoin the audio conference: \nDial a pho

Here is a little snippet of code to start with:

function escape_text (
	P_STRING	in clob
) return clob
as
	l_return		clob;
begin
	-- escape the comma character
	l_return := replace(P_STRING, chr(44), '\,');
	l_return := replace(l_return, chr(10), '\n');
	l_return := replace(l_return, chr(13), '');
	return l_return;
end escape_text;

3.2 Date and Time in ISO 8601 Format

This date format is familiar to those who use Oracle PL/SQL to exchange data with JSON. A few quick tricks and Bob’s your uncle:

function ISO_8601 (
		P_DATE		in timestamp,
		P_TIMEZONE	in varchar2
		) return varchar2
as
	l_timestamp		timestamp;
	l_iso_8601		varchar2(60);
begin
	-- convert the date/time to UTC/Zulu/GMT
	select 
		cast(P_DATE as timestamp with time zone) at time zone 'UTC' 
		into
		l_timestamp
	from dual;
	-- convert the format to ISO_8601/JSON format
	if l_timestamp is not null then
		l_iso_8601 := to_char(l_timestamp, g_ISO8601_format);
	else
		l_iso_8601 := null;
	end if;
	return l_iso_8601;
end iso_8601;

Note that the package has a constant for the date format as shown:

  g_ISO8601_format	constant varchar2(30) := 'YYYYMMDD"T"HH24MISS"Z"';

3.3 Time Zone Matter

There is a lot of blah-blah-blah in the protocol about time zones. The easiest solution is to just use UTC. Convert your times to Zulu and be done with it.

select 
		cast(P_DATE as timestamp with time zone) at time zone 'UTC' 
into
		l_timestamp
from dual;

package tree

Step 4 – Write Some Code

First, I don’t like looking at code with a bunch of concatenated strings. So instead, I use substitution strings. I’ll argue you can make updates and changes with less effort too. The only real challenge in finishing the effort is deciding how to handle your list of attendees. That will clearly be data driven, each line of attendees will have to be folded as you build your list. With the list of attendees appropriately formatted, a quick substitution will land them into your calendar invitations (ics text file).

I took the good/source vCalendar text and converted it and put it at the top of the package body.

create or replace package body ical_pkg
as
  g_ISO8601_format    constant varchar2(30) := 'YYYYMMDD"T"HH24MISS"Z"';
  crlf				        constant varchar2(2) := chr(13) || chr(10);
	tab                 constant varchar2(1) := chr(9);
	vcal                constant varchar2(4000) :=
q'[BEGIN:VCALENDAR
PRODID:-//Oracle APEX//Outlook 16.0 MIMEDIR//EN
VERSION:2.0
METHOD:REQUEST
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VEVENT
#ATTENDEE_LIST#
CLASS:PUBLIC
#CREATED#
#DESCRIPTION#
#DTEND#
#DTSTAMP#
#DTSTART#
#LAST_MODIFIED#
#LOCATION#
#ORGANIZER#
PRIORITY:5
SEQUENCE:2
#SUMMARY#
TRANSP:OPAQUE
#UID#
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
X-MICROSOFT-CDO-INTENDEDSTATUS:BUSY
END:VEVENT
END:VCALENDAR]';
	
function escape_text ( ...

I know that the code is not fully functional, they are just hints to encourage exploration.

function event_invite(
	P_EVENT_PK		in number
	) return clob
as
	r_event        bid_event_v%ROWTYPE;
	r_asset        bid_asset_v%ROWTYPE;
	l_select_count number;
	l_ics          clob;
...
begin
	select sessiontimezone into l_timezone from dual;
	select * into r_event from bid_event_v where event_pk = P_EVENT_PK;
	l_ics            := vcal;
	l_created        := ical_pkg.iso_8601(localtimestamp, l_timezone);
	l_created        := 'CREATED:' || l_created;
	l_last_modified  := ical_pkg.iso_8601(localtimestamp, l_timezone);
	l_last_modified  := 'LAST-MODIFIED:' || l_last_modified;
	l_dtstamp        := ical_pkg.iso_8601(localtimestamp, l_timezone);
	l_dtstamp        := 'DTSTAMP:' || l_dtstamp;
	l_location       := 'LOCATION:Tempest-Bid';
	l_organizer      := 'ORGANIZER:CN="Tempest-Bid":mailto:do_not_reply@storm-petrel.com';
	
...

	l_attendees			:= ical_pkg.attendees(P_EVENT_PK);
...

	l_ics		:= replace(l_ics, '#ATTENDEE_LIST#', l_attendees);
	l_ics		:= replace(l_ics, '#CREATED#', l_created);
	l_ics		:= replace(l_ics, '#DESCRIPTION#', l_description);
...

return (l_ics);
end event_invite;

-/qed