Sending Calendar Appointments from the Database
The Problem
We needed to send calendar appointments. The area we wanted to tackle first was when staff requested an absense through the self service module of Oracle HR. We realised it would be seen as a benefit if the requested holiday or absence also appeared in the person's calendar. We use Novell Groupwise, but this supports the iCal format so that is the approach we took.
The Solution
We extended the MAIL_PKG database package to add two new procedures and a function. I'd like to give credit where it's due - we didn't invent this ourselves. I adapted it from Dan McGhan's Oracle blog article. Here's the code I added to the MAIL_PKG package header that I described previously:
-------------------------------------------------------------------------------
-- SEND_APPOINTMENT is used to send an appointment to someone
procedure send_appointment
(
p_from_email IN VARCHAR2
, p_from_name IN VARCHAR2
, p_to_email IN VARCHAR2
, p_to_name IN VARCHAR2
, p_subject IN VARCHAR2
, p_body_html IN VARCHAR2
, p_start_date IN DATE
, p_end_date IN DATE
);
-------------------------------------------------------------------------------
-- SEND_ICAL_EMAIL is used by SEND_APPOINTMENT to send a formatted email
-- containing an ical_event
procedure send_ical_email
(
p_from IN VARCHAR2
, p_to IN VARCHAR2
, p_subject IN VARCHAR2
, p_body_html IN VARCHAR2
, p_body_ical IN VARCHAR2
);
-------------------------------------------------------------------------------
-- ICAL_EVENT is used by SEND_APPOINTMENT to prepare the specially formatted
-- calendar booking in the ICAL format.
FUNCTION ical_event
(
p_summary IN VARCHAR2
, p_organizer_name IN VARCHAR2
, p_organizer_email IN VARCHAR2
, p_attendee_name IN VARCHAR2
, p_attendee_email IN VARCHAR2
, p_start_date IN DATE
, p_end_date IN DATE
, p_version IN VARCHAR2 := NULL
, p_prodid IN VARCHAR2 := NULL
, p_calscale IN VARCHAR2 := NULL
, p_method IN VARCHAR2 := NULL
)
RETURN VARCHAR2;
I added this to the body of the package:
-------------------------------------------------------------------------------
procedure send_appointment
(
p_from_email IN VARCHAR2
, p_from_name IN VARCHAR2
, p_to_email IN VARCHAR2
, p_to_name IN VARCHAR2
, p_subject IN VARCHAR2
, p_body_html IN VARCHAR2
, p_start_date IN DATE
, p_end_date IN DATE
)
as
l_ical_event VARCHAR2(32767);
begin
l_ical_event := ical_event(
p_start_date => p_start_date
, p_end_date => p_end_date
, p_summary => p_subject
, p_organizer_name => p_from_name
, p_organizer_email => p_from_email
, p_attendee_name => p_to_name
, p_attendee_email => p_to_email
);
send_ical_email(
p_to => p_to_email
, p_from => p_from_email
, p_subject => p_subject
, p_body_html => p_body_html
, p_body_ical => l_ical_event
);
end send_appointment;
-------------------------------------------------------------------------------
procedure send_ical_email
(
p_from IN VARCHAR2
, p_to IN VARCHAR2
, p_subject IN VARCHAR2
, p_body_html IN VARCHAR2
, p_body_ical IN VARCHAR2
)
as
l_connection UTL_SMTP.CONNECTION;
l_mail_serv VARCHAR2(50) := '10.200.2.46';
l_mail_port PLS_INTEGER := '25';
l_lf VARCHAR2(10) := utl_tcp.crlf;
l_msg_body VARCHAR2(32767);
begin
l_msg_body :=
'Content-class: urn:content-classes:calendarmessage' || l_lf
|| 'MIME-Version: 1.0' || l_lf
|| 'Content-Type: multipart/alternative;' || l_lf
|| ' boundary="----_=_NextPart"' || l_lf
|| 'Subject: ' || p_subject || l_lf
|| 'Date: ' || TO_CHAR(SYSDATE,'DAY, DD-MON-RR HH24:MI') || l_lf
|| 'From: <' || p_from || '> ' || l_lf
|| 'To: ' || p_to || l_lf
|| '------_=_NextPart' || l_lf
|| 'Content-Type: text/plain;' || l_lf
|| ' charset="iso-8859-1"' || l_lf
|| 'Content-Transfer-Encoding: quoted-printable' || l_lf
|| l_lf
|| 'You must have an HTML enabled client to view this message.' || l_lf
|| l_lf
|| '------_=_NextPart' || l_lf
|| 'Content-Type: text/html;' || l_lf
|| ' charset="iso-8859-1"' || l_lf
|| 'Content-Transfer-Encoding: quoted-printable' || l_lf
|| l_lf
|| p_body_html || l_lf
|| l_lf
|| '------_=_NextPart' || l_lf
|| 'Content-class: urn:content-classes:calendarmessage' || l_lf
|| 'Content-Type: text/calendar;'
|| ' method=REQUEST;'
|| ' name="meeting.ics"' || l_lf
|| 'Content-Transfer-Encoding: 8bit' || l_lf
|| l_lf
|| p_body_ical || l_lf
|| l_lf
|| '------_=_NextPart--';
l_connection := utl_smtp.open_connection(l_mail_serv, l_mail_port);
utl_smtp.helo(l_connection, l_mail_serv);
utl_smtp.mail(l_connection, p_from);
utl_smtp.rcpt(l_connection, p_to);
utl_smtp.data(l_connection, l_msg_body);
utl_smtp.quit(l_connection);
end send_ical_email;
-------------------------------------------------------------------------------
FUNCTION ical_event
(
p_summary IN VARCHAR2
, p_organizer_name IN VARCHAR2
, p_organizer_email IN VARCHAR2
, p_attendee_name IN VARCHAR2
, p_attendee_email IN VARCHAR2
, p_start_date IN DATE
, p_end_date IN DATE
, p_version IN VARCHAR2 := NULL
, p_prodid IN VARCHAR2 := NULL
, p_calscale IN VARCHAR2 := NULL
, p_method IN VARCHAR2 := NULL
)
RETURN VARCHAR2
as
l_retval VARCHAR2(32767);
l_lf VARCHAR2(10) := utl_tcp.crlf;
begin
l_retval := ''
|| 'BEGIN:VCALENDAR' || l_lf
|| 'VERSION:' || NVL(p_version,'2.0') || l_lf
|| 'PRODID:' || NVL(p_prodid,'-//NONSGML ICAL_EVENT//EN') || l_lf
|| 'CALSCALE:' || NVL(p_calscale,'GREGORIAN') || l_lf
|| 'METHOD:' || NVL(p_method,'REQUEST') || l_lf
|| 'BEGIN:VEVENT' || l_lf
|| 'DTSTAMP:' || TO_CHAR(SYSDATE,'YYYYMMDD') || 'T'
|| TO_CHAR(SYSDATE,'HH24MISS') || 'Z' || l_lf
|| 'SUMMARY:' || p_summary || l_lf
|| 'ORGANIZER;CN="' || p_organizer_name
|| '":MAILTO:' || p_organizer_email || l_lf
|| 'ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=TRUE;CN="'
|| p_attendee_name || '":MAILTO:' || p_attendee_email || l_lf
|| 'DTSTART:' || TO_CHAR(p_start_date,'YYYYMMDD') || 'T'
|| TO_CHAR(p_start_date,'HH24MISS') || 'Z' || l_lf
|| 'DTEND:' || TO_CHAR(p_end_date,'YYYYMMDD') || 'T'
|| TO_CHAR(p_end_date,'HH24MISS') || 'Z' || l_lf
|| 'UID:' || RAWTOHEX(SYS_GUID()) || l_lf
|| 'DESCRIPTION:\N' || l_lf
|| 'SEQUENCE:0' || l_lf
|| 'PRIORITY:5' || l_lf
|| 'STATUS:NEEDS-ACTION' || l_lf
|| 'END:VEVENT' || l_lf
|| 'END:VCALENDAR';
RETURN l_retval;
end ical_event;
Dan McGahn's article describes the functioning of the SEND_ICAL_EMAIL procedure and the ICAL_EVENT function. I added a wrapper procedure called SEND_APPOINTMENT to make it easier.
We built this to work with Novell Groupwise, but it should work equally well with any iCal standards compliant calendar.