APEX Hands on Scripts: APEX PL/SQL: Sending Outlook/Thunderbird calendar invites as an ics file attachment

With the below code you can send individual emails to the users with an ics file as attachment.
Steps to Do:

Create a Package Header

create or replace package ORA_SEND_EMAILS_API as

PROCEDURE notify_user

(p_email IN varchar2

, p_subject IN varchar2

, p_body IN clob

, p_reply_to IN varchar2

, p_calendar IN blob DEFAULT NULL);

FUNCTION get_calendar_event

(p_summary IN varchar2

, p_description IN varchar2

, p_event_location IN varchar2

, p_start_date IN date

, p_end_date IN date

, p_reminder IN number) RETURN clob;

PROCEDURE notify_users

(p_event_id IN number

, p_subject IN varchar2

, p_body IN clob

, p_reply_to IN varchar2

, p_location IN varchar2

, p_start_time IN date

, p_end_time IN date

, p_event_location IN varchar2

, p_reminder IN number);

end;

Create Package Body

create or replace package body "ORA_SEND_EMAILS_API" is

FUNCTION clob_to_blob

(p_clob IN clob) RETURN blob IS

l_blob blob;

l_dest_offset integer DEFAULT 1;

l_source_offset integer DEFAULT 1;

l_lang_context integer DEFAULT dbms_lob.default_lang_ctx;

l_warning integer DEFAULT dbms_lob.warn_inconvertible_char;

BEGIN

dbms_lob.createtemporary ( l_blob, TRUE );

dbms_lob.converttoblob

( dest_lob => l_blob

, src_clob => p_clob

, amount => dbms_lob.lobmaxsize

, dest_offset => l_dest_offset

, src_offset => l_source_offset

, blob_csid => dbms_lob.default_csid

, lang_context => l_lang_context

, warning => l_warning );

RETURN l_blob;

END clob_to_blob;

FUNCTION get_calendar_event

(p_summary IN varchar2

, p_description IN varchar2

, p_event_location IN varchar2

, p_start_date IN date

, p_end_date IN date

, p_reminder IN number) RETURN clob IS

l_lf varchar2(2) DEFAULT chr ( 13 ) ||

chr ( 10 );

l_ret clob;

BEGIN

l_ret := 'BEGIN:VCALENDAR' ||

l_lf ||

'VERSION:2.0' ||

l_lf ||

'PRODID:-//This is a generic ID//NONSGML ICAL_EVENT//EN' ||

l_lf ||

'CALSCALE:GREGORIAN' ||

l_lf ||

'METHOD:PUBLISH' ||

l_lf ||

'BEGIN:VEVENT' ||

l_lf ||

'CLASS:PUBLIC' ||

l_lf ||

'SUMMARY;LANGUAGE=en-us:[OraOvations2014]' ||

p_summary ||

l_lf ||

'DESCRIPTION:[OraOvations2014]' ||

p_description ||

l_lf ||

'LOCATION:' ||

p_event_location ||

l_lf ||

'CREATED:' ||

to_char ( sysdate, 'YYYYMMDD' ) ||

'T' ||

to_char ( sysdate, 'HH24MISS' ) ||

l_lf ||

'DTSTAMP:' ||

to_char ( sysdate, 'YYYYMMDD' ) ||

'T' ||

to_char ( sysdate, 'HH24MISS' ) ||

l_lf ||

'DTSTART:' ||

to_char ( p_start_date, 'YYYYMMDD' ) ||

'T' ||

to_char ( p_start_date, 'HH24MISS' ) ||

l_lf ||

'DTEND:' ||

to_char ( p_end_date, 'YYYYMMDD' ) ||

'T' ||

to_char ( p_end_date, 'HH24MISS' ) ||

l_lf ||

'UID:' ||

rawtohex ( sys_guid ( ) ) ||

'@oracle.com' ||

l_lf ||

'BEGIN:VALARM' ||

l_lf ||

'TRIGGER:-PT' ||

p_reminder ||

'M' ||

l_lf ||

'ACTION:DISPLAY' ||

l_lf ||

'DESCRIPTION:Reminder' ||

l_lf ||

'END:VALARM' ||

l_lf ||

'END:VEVENT' ||

l_lf ||

'END:VCALENDAR';

RETURN l_ret;

END get_calendar_event;

PROCEDURE notify_user

(p_email IN varchar2

, p_subject IN varchar2

, p_body IN clob

, p_reply_to IN varchar2

, p_calendar IN blob DEFAULT NULL) IS

l_id number;

BEGIN

l_id := apex_mail.send

( p_to => p_email

, p_from => 'user@domanin.com'

, p_subj => p_subject

, p_body => p_body

, p_body_html => p_body

, p_replyto => p_reply_to );

IF p_calendar IS NOT NULL THEN

apex_mail.add_attachment

( p_mail_id => l_id

, p_attachment => p_calendar

, p_filename => 'Calendar.ics'

, p_mime_type => 'application/hbs-ics' );

END IF;

apex_mail.push_queue ( );

EXCEPTION

WHEN others THEN

htp.p ( 'Unable to send emails' );

END notify_user;

PROCEDURE notify_users

(p_subject IN varchar2

, p_body IN clob

, p_reply_to IN varchar2

, p_start_time IN date

, p_end_time IN date

, p_event_location IN varchar2

, p_reminder IN number) IS

CURSOR c_users IS

SELECT nvl ( rtrim ( rtrim ( XMLAGG ( XMLELEMENT ( NAME e,emailid ||

',' ) ORDER BY 1 ).extract ( '//text()' ) ), ',' ), p_reply_to ) emails

FROM user_emails;

l_body clob;

l_cc varchar2(4000);

l_calendar blob;

BEGIN

l_calendar := clob_to_blob ( get_calendar_event

( p_summary => p_subject

, p_description => p_subject

, p_event_location => p_event_location

, p_start_date => p_start_time

, p_end_date => p_end_time

, p_reminder => p_reminder ) );

FOR l_email IN c_users LOOP

notify_user

( p_email => l_email.email

, p_subject => p_subject

, p_body => l_body

, p_reply_to => p_reply_to

, p_calendar => l_calendar );

END LOOP;

END schedule_for_participants;

end "ORA_SEND_EMAILS_API";

Execute

BEGIN

ora_send_emails_api.notify_users

( p_subject => 'Test Subject'

, p_body => 'Test Body'

, p_reply_to => 'myemail@domain.com'

, p_start_time => to_date ( sysdate + 1 / 24 )

, p_end_time => to_date ( sysdate + 2 / 24 )

, p_event_location => 'Test Location'

, p_reminder => 15 );

END;

5 comments:

UnknownTuesday, May 13, 2014 8:48:00 pm
did any one tried with this?
Aneesh Mon NSunday, May 18, 2014 9:56:00 am
This is a working script.
UnknownThursday, May 22, 2014 2:02:00 pm
Can you show me any demo app, so that I can understand easily. Please help me out.
UnknownWednesday, May 28, 2014 4:57:00 pm
Hi Aneesh,

This is working fine. but the problem is that it is not updating the calendar automatically.

Thanks,
Bala
Minh TuanFriday, March 22, 2019 8:03:00 am
Pls help me. Run error ORA-00937: not a single-group group function
at statement:
SELECT nvl ( rtrim ( rtrim ( XMLAGG ( XMLELEMENT ( NAME e,emailid ||
',' ) ORDER BY 1 ).extract ( '//text()' ) ), ',' ), p_reply_to ) emails
FROM user_emails;

Tuesday 4 February 2014

APEX PL/SQL: Sending Outlook/Thunderbird calendar invites as an ics file attachment

5 comments: