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:firstname.lastname@example.org ATTENDEE;CNemail@example.com;RSVP=FALSE:mailto:frederick.fl firstname.lastname@example.org ATTENDEE;CN="Christina Moore";RSVP=FALSE:mailto:email@example.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.
- Lines are Folded (RFC 5545 3.1 Content Lines)
- Date/Time are presented as ISO 8601 Format
- 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;
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:firstname.lastname@example.org'; ... 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;