hello
here is a script I wrote would like to share, could be useful for others. The goal is to send an HTML table from a select * from mytable@mycontainer
First we create the temp table:
create or replace table date_range_1@inmemorystorage
as
select *
from calendar@datadictionary
where day_date between to_date('01-01-2000', 'DD-MM-YYYY') and to_date('01-01-2015', 'DD-MM-YYYY');
then here is the (simplified) statement:
--
-- Start the PSQL script.
--
declare
l_email_table varchar2;
begin
set smtp-host-address@mail "my_smtp";
set smtp-minimum-deliver-duration-ms@mail 5000;
set smtp-user-name@mail "my_email";
set smtp-password@mail "my_pwd";
set [mail-from-name]@mail "my_name";
set [mail-from-email]@mail "my_email";
set [mail-body-html]@mail false;
set smtp-enable-ssl@mail true;
set mail-priority@mail -1;
set smtp-minimum-deliver-duration-ms@mail 1000;
set mail-body-html@mail true; -- important to be able to do some <td> <tr> stuff
set mail-priority@mail 0;
begin
-- Prepare email content (table with the last 5 rows).
l_email_table := '<table border = 1>
<tr>
<th>Day in week</th>
<th>week<br>number</th>
<th>Day<br>date</th>
<th>Week<br>nr ISO</th>
</tr>';
for r in (select * from date_range_1@InMemoryStorage order by opening_date DESC LIMIT 5)
loop
begin
l_email_table := l_email_table
||'<tr><td>'
||r.day_in_week
||'</td><td>'||r.week_number
||'</td><td>'||r.day_date
||'</td><td style="text-align:right">'||r.week_number
||'</td></tr>'
;
end;
end loop;
-- Sending email.
insert into smtp@mail
( toEmail
, ccEmail
, bccEmail
, subject
, body
, attachmentContents
, attachmentName
, attachmentMimeType
)
select 'email_to_send_to'
toEmail
, cast(null as varchar2)
ccEmail
, cast(null as varchar2)
bccEmail
, 'My subject'
subject
, 'hello here is your table<br>'
|| '<br>'
|| '<br>last 5 data:<br>'
|| l_email_table
body
, null -- rfe.file_contents
attachmentContents
, null -- basename('{USER_MULTI_PARTIES_FILE_NAME}')
attachmentName
, null -- cast(null as varchar2)
attachmentMimeType;
end;