With the below code you can send individual emails to the users with an ics file as attachment.
Steps to Do:
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;
