How to send e-mail in HTML format in Apex?
-
- Moderador
- Posts: 1177
- Joined: Thu, 15 Oct 2009 10:28 am
- Location: Recife
Which package do you use to send?
Context-Type, there is a bid like this ...
Mime-Type .. I do not remember right now ...
Context-Type, there is a bid like this ...
Mime-Type .. I do not remember right now ...
-
- Rank: DBA Júnior
- Posts: 193
- Joined: Thu, 24 Jan 2008 1:56 pm
- Location: SP
Dulima
DBA Oracle Jr
DBA Oracle Jr
Here is an example MASI using only proc, and view
View of View
CREATE OR REPLACE PROCEDURE teor.prc_relatorio_diario (strPara VARCHAR2,strCc VARCHAR2 )
IS
BEGIN
DECLARE
strPara VARCHAR2(50) := 'xxxxx@xxxx.com.br';
strCc VARCHAR2(50) := 'xxxx@xxxx.com.br';
strTitulo VARCHAR2(200):='Servidor de Banco de Dados Oracle';
strMsg01 VARCHAR2(8000):='<html><head>';
strMsg02 VARCHAR2(8000):=' ';
strMsg03 VARCHAR2(8000):=' ';
strMsg04 VARCHAR2(8000):=' ';
strMsg05 VARCHAR2(8000):=' ';
strMsg06 VARCHAR2(8000):=' ';
strMsg07 VARCHAR2(8000):=' ';
strMsg08 VARCHAR2(8000):=' ';
strMsg09 VARCHAR2(8000):=' ';
intListado number:=1;
intEmail number:=1;
h_host VARCHAR2(50):=' ';
intEnvia number:=1;
compSmtp utl_smtp.connection;
strDe VARCHAR2(50) := 'grupodba@xxxx.com.br';
smtpHost VARCHAR2(30) := 'xxxxxxx'; -- Servidor de Saida
BEGIN
select f_hostname into h_host from dual;
strMsg01:= strMsg01 || '<style type="text/css">';
strMsg01:= strMsg01 || '<!--';
strMsg01:= strMsg01 || 'table{ font-family: "Trebuchet MS", Tahoma, Arial; font-size: 12px; color: #333333; border: #000000; border-style: solid; border-top-width: thin; border-right-width: thin; border-bottom-width: thin; border-left-width: thin; text-align: justify; };';
strMsg01:= strMsg01 || 'th{ background-color: #000000; color: #FFFFFF; border: #000000; border-style: solid; border-top-width: thin; border-right-width: thin; border-bottom-width: thin; border-left-width: thin; };';
strMsg01:= strMsg01 || '#tr1 {background-color: #CBE7E7;};';
strMsg01:= strMsg01 || '#tr2 {background-color: #FFFFFF;};';
strMsg01:= strMsg01 || 'td{text-align:right;} td.first{text-align:left; padding:0 10px 0 0;}';
strMsg01:= strMsg01 || 'td{text-align:right;} td.second{ background-color: #FF0000;}';
strMsg01:= strMsg01 || 'td{text-align:right;} td.thirth{ background-color: #FFFF00;}';
strMsg01:= strMsg01 || 'td{text-align:right;} td.fourth{border-top: 2px solid black; }';
strMsg01:= strMsg01 || '-->';
strMsg02:= strMsg02 || '</style></head><Body> <td><font face="Trebuchet MS", Tahoma, Arial> BANCO DE DADOS </font></td><table><thead>';
strMsg02:= strMsg02 || '<tr><th style="padding:0 10px 0 0;">DBID</th><th>INSTANCE NAME</th><th>HOST</th><th style="padding:0 10px 0 0;">DB NAME</th><th style="padding:0 10px 0 0;">LOG MODE</th><th>DATABASE</th></tr></thead><tbody>';
strMsg03:= strMsg03 || '</style></head><Body> <td><font face="Trebuchet MS", Tahoma, Arial> ESPAÇO ASM </font><table><thead>';
strMsg03:= strMsg03 || '<tr><th>GRUPO</th><th>NOME DO GRUPO</th><th>STATUS</th><th>TIPO</th><th>TOTAL GB</th><th>FREE GB</th></tr></thead><tbody>';
strMsg04:= strMsg04 || '</style></head><Body> <td><font face="Trebuchet MS", Tahoma, Arial> ESPAÇO TABLESPACE </font><table><thead>';
strMsg04:= strMsg04 || '<tr><th style="padding:0 10px 0 0;">TABLESPACE</th><th> T </th><th>EM USO</th><th>TAMANHO ATUAL</th><th>TAMANHO MAXIMO</th><th>ESPACO LIVRE</th><th>ESPACO LIVRE TOTAL</th><th>OCUPACAO - %</th></tr></thead><tbody>';
compSmtp := utl_smtp.open_connection(smtpHost, 25); -- SMTP porta 25
utl_smtp.helo(compSmtp, smtpHost);
utl_smtp.mail(compSmtp, strDe);
utl_smtp.rcpt(compSmtp, strPara);
utl_smtp.rcpt(compSmtp, strCc);
for a in
(
select * from vw_inf_dba_base
)
loop
if length(strMsg02)<7500 then
if intListado=1 then intListado:=0; else intListado:=intListado +1; end if;
strMsg02:= strMsg02 || '<tr id="tr'|| to_char(intListado) || '">' || a.linha || '</tr>';
dbms_output.put_line('1 : ' || length(strMsg02));
end if;
end loop;
for b in
(
select * from VW_INF_DBA_ASM
)
loop
if length(strMsg03)<7500 then
if intListado=1 then intListado:=0; else intListado:=intListado +1; end if;
strMsg03:= strMsg03 || '<tr id="tr'|| to_char(intListado) || '">' || b.linha || '</tr>';
dbms_output.put_line('1 : ' || length(strMsg03));
end if;
end loop;
for c in (
select * from VW_INF_DBA_TBS
--select * from vw_session
)
loop
if length(strMsg05)<7500 then
if intListado=1 then intListado:=0; else intListado:=intListado +1; end if;
strMsg05:= strMsg05 || '<tr id="tr'|| to_char(intListado) || '">' || c.linha || '</tr>';
dbms_output.put_line('1 : ' || length(strMsg05));
else
if length(strMsg06)<7500 then
if intListado=1 then intListado:=0; else intListado:=intListado +1; end if;
strMsg06:= strMsg06 || '<tr id="tr'|| to_char(intListado) || '">' || c.linha || '</tr>';
dbms_output.put_line('2 : ' || length(strMsg06));
else
if length(strMsg07)<7500 then
if intListado=1 then intListado:=0; else intListado:=intListado +1; end if;
strMsg07:= strMsg07 || '<tr id="tr'|| to_char(intListado) || '">' || c.linha || '</tr>';
dbms_output.put_line('3 : ' || length(strMsg07));
else
if length(strMsg08)<7500 then
if intListado=1 then intListado:=0; else intListado:=intListado +1; end if;
strMsg08:= strMsg08 || '<tr id="tr'|| to_char(intListado) || '">' || c.linha || '</tr>';
dbms_output.put_line('4 : ' || length(strMsg08));
else
if length(strMsg09)<7500 then
if intListado=1 then intListado:=0; else intListado:=intListado +1; end if;
strMsg09:= strMsg09 || '<tr id="tr'|| to_char(intListado) || '">' || c.linha || '</tr>';
dbms_output.put_line('5 : ' || length(strMsg09));
else
if intEmail = 1 then
strMsg02:= strMsg02 || '</tbody></table><br>';
strMsg03:= strMsg03 || '</tbody></table><br>';
utl_smtp.data(compSmtp, 'From: ' || strDe || utl_tcp.crlf || 'To: ' || strPara || utl_tcp.crlf || 'Cc: ' || strCc ||utl_tcp.crlf || 'Subject: ' || strTitulo || ' ' || to_char(h_host) || ' ' || to_char(intEmail) || utl_tcp.crlf || 'MIME-Version: 1.0' || utl_tcp.crlf || 'Content-type: text/html'|| utl_tcp.crlf|| strMsg01 || utl_tcp.crlf || strMsg02 || utl_tcp.crlf || strMsg03 || utl_tcp.crlf || strMsg04 || utl_tcp.crlf || strMsg05 || utl_tcp.crlf || strMsg06 || utl_tcp.crlf ||strMsg07 || utl_tcp.crlf ||strMsg08 || utl_tcp.crlf ||strMsg09 || utl_tcp.crlf ||'</tbody></table></Body></html>');
dbms_output.put_line('De: ' || utl_tcp.crlf || 'Para: ' || strPara || utl_tcp.crlf || 'Cc: ' || strCc || utl_tcp.crlf || 'Título: ' || strTitulo );
intEmail:= intEmail + 1;
intEnvia:=intEnvia + 1;
compSmtp := utl_smtp.open_connection(smtpHost, 25); -- SMTP porta 25
utl_smtp.helo(compSmtp, smtpHost);
utl_smtp.mail(compSmtp, strDe);
utl_smtp.rcpt(compSmtp, strPara);
utl_smtp.rcpt(compSmtp, strCc);
strMsg01:='<html><head>';
strMsg01:= strMsg01 || '<style type="text/css">';
strMsg01:= strMsg01 || '<!--';
strMsg01:= strMsg01 || 'table{ font-family: "Trebuchet MS", Tahoma, Arial; font-size: 12px; color: #333333; border: #000000; border-style: solid; border-top-width: thin; border-right-width: thin; border-bottom-width: thin; border-left-width: thin; text-align: justify; };';
strMsg01:= strMsg01 || 'th{ background-color: #000000; color: #FFFFFF; border: #000000; border-style: solid; border-top-width: thin; border-right-width: thin; border-bottom-width: thin; border-left-width: thin; };';
strMsg01:= strMsg01 || '#tr1 { background-color: #CBE7E7;};';
strMsg01:= strMsg01 || '#tr2 { background-color: #FFFFFF;};';
strMsg01:= strMsg01 || 'td{text-align:right;} td.first{text-align:left; padding:0 10px 0 0;}';
strMsg01:= strMsg01 || '-->';
strMsg05 :=' ';
strMsg06 :=' ';
strMsg07 :=' ';
strMsg08 :=' ';
strMsg09 :=' ';
else
if intEmail >= 2 then
utl_smtp.data(compSmtp, 'From: ' || strDe || utl_tcp.crlf || 'To: ' || strPara || utl_tcp.crlf || 'Cc: ' || strCc ||utl_tcp.crlf || 'Subject: ' || strTitulo || ' ' || to_char(h_host) || ' ' || to_char(intEmail) || utl_tcp.crlf || 'MIME-Version: 1.0' || utl_tcp.crlf || 'Content-type: text/html'|| utl_tcp.crlf|| strMsg01 || utl_tcp.crlf || strMsg04 || utl_tcp.crlf || strMsg05 || utl_tcp.crlf || strMsg06 || utl_tcp.crlf ||strMsg07 || utl_tcp.crlf ||strMsg08 || utl_tcp.crlf ||strMsg09 || utl_tcp.crlf ||'</tbody></table></Body></html>');
dbms_output.put_line('De: ' || utl_tcp.crlf || 'Para: ' || strPara || utl_tcp.crlf || 'Cc: ' || strCc || utl_tcp.crlf || 'Título: ' || strTitulo );
intEmail:= intEmail + 1;
compSmtp := utl_smtp.open_connection(smtpHost, 25); -- SMTP porta 25
utl_smtp.helo(compSmtp, smtpHost);
utl_smtp.mail(compSmtp, strDe);
utl_smtp.rcpt(compSmtp, strPara);
utl_smtp.rcpt(compSmtp, strCc);
strMsg01:='<html><head>';
strMsg01:= strMsg01 || '<style type="text/css">';
strMsg01:= strMsg01 || '<!--';
strMsg01:= strMsg01 || 'table{ font-family: "Trebuchet MS", Tahoma, Arial; font-size: 12px; color: #333333; border: #000000; border-style: solid; border-top-width: thin; border-right-width: thin; border-bottom-width: thin; border-left-width: thin; text-align: justify; };';
strMsg01:= strMsg01 || 'th{ background-color: #000000; color: #FFFFFF; border: #000000; border-style: solid; border-top-width: thin; border-right-width: thin; border-bottom-width: thin; border-left-width: thin; };';
strMsg01:= strMsg01 || '#tr1 { background-color: #CBE7E7;};';
strMsg01:= strMsg01 || '#tr2 { background-color: #FFFFFF;};';
strMsg01:= strMsg01 || 'td{text-align:right;} td.first{text-align:left; padding:0 10px 0 0;}';
strMsg01:= strMsg01 || '-->';
strMsg05 :=' ';
strMsg06 :=' ';
strMsg07 :=' ';
strMsg08 :=' ';
strMsg09 :=' ';
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end loop;
if intEnvia = 1 then
strMsg02:= strMsg02 || '</tbody></table><br>';
strMsg03:= strMsg03 || '</tbody></table><br>';
utl_smtp.data(compSmtp, 'From: ' || strDe || utl_tcp.crlf || 'To: ' || strPara || utl_tcp.crlf || 'Cc: ' || strCc ||utl_tcp.crlf || 'Subject: ' || strTitulo || ' ' || to_char(h_host) || ' ' || to_char(intEmail) || utl_tcp.crlf || 'MIME-Version: 1.0' || utl_tcp.crlf || 'Content-type: text/html'|| utl_tcp.crlf|| strMsg01 || utl_tcp.crlf || strMsg02 || utl_tcp.crlf || strMsg03 || utl_tcp.crlf || strMsg04 || utl_tcp.crlf || strMsg05 || utl_tcp.crlf || strMsg06 || utl_tcp.crlf ||strMsg07 || utl_tcp.crlf ||strMsg08 || utl_tcp.crlf ||strMsg09 || utl_tcp.crlf ||'</tbody></table></Body></html>');
dbms_output.put_line('De: ' || utl_tcp.crlf || 'Para: ' || strPara || utl_tcp.crlf || 'Cc: ' || strCc || utl_tcp.crlf || 'Título: ' || strTitulo );
utl_smtp.quit(compSmtp);
else
if intEnvia > 1 then
utl_smtp.data(compSmtp, 'From: ' || strDe || utl_tcp.crlf || 'To: ' || strPara || utl_tcp.crlf || 'Cc: ' || strCc ||utl_tcp.crlf || 'Subject: ' || strTitulo || ' ' || to_char(h_host) || ' ' || to_char(intEmail) || utl_tcp.crlf || 'MIME-Version: 1.0' || utl_tcp.crlf || 'Content-type: text/html'|| utl_tcp.crlf|| strMsg01 || utl_tcp.crlf || strMsg04 || utl_tcp.crlf || strMsg05 || utl_tcp.crlf || strMsg06 || utl_tcp.crlf ||strMsg07 || utl_tcp.crlf ||strMsg08 || utl_tcp.crlf ||strMsg09 || utl_tcp.crlf ||'</tbody></table></Body></html>');
dbms_output.put_line('De: ' || utl_tcp.crlf || 'Para: ' || strPara || utl_tcp.crlf || 'Cc: ' || strCc || utl_tcp.crlf || 'Título: ' || strTitulo );
utl_smtp.quit(compSmtp);
end if;
end if;
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Operação inválida usando UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line('Email temporariamente fora do ar ou Caixa de entrada Cheia');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Conta inexistente!');
END;
END;
/
CREATE OR REPLACE VIEW teor.VW_INF_DBA_TBS
AS
select 1 nl,
'<td class="first">' || t.tablespace_name || '</td><td>' ||
substr(t.contents, 1, 1)|| '</td><td>' ||
to_char(trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024),'9,999,990') || '</td><td>' ||
to_char(trunc(d.tbs_size/1024/1024),'9,999,990') || '</td><td>' ||
to_char(trunc(d.tbs_maxsize/1024/1024),'9,999,990') || '</td><td>' ||
to_char(trunc(nvl(s.free_space, 0)/1024/1024),'9,999,990') || '</td><td>' ||
to_char(trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0))/1024/1024),'9,999,990') || '</td>'||
case
when decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) >= 75 AND decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) <= 89 then
'<td class="thirth">'
when decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) <= 90 then
'<td>'
ELSE
'<td class="second">'
end || decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) || '</td>' as linha,
decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) odr, t.tablespace_name odr2
from
( select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
tablespace_name tablespace
from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_data_files
union all
select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_temp_files
)
group by tablespace_name
) d,
( select SUM(bytes) free_space, tablespace_name tablespace
from dba_free_space group by tablespace_name ) s,
dba_tablespaces t
where t.tablespace_name = d.tablespace(+) and t.tablespace_name = s.tablespace(+)
union
select 2 nl,
'<td class="first"><b>TOTAL: </b></td><td> </td><td class="fourth"><b>' ||
to_char(sum(trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024)),'9,999,990') || '</b></td><td class="fourth"><b>' ||
to_char(sum(trunc(d.tbs_size/1024/1024)),'9,999,990') || '</b></td><td class="fourth"><b>' ||
to_char(sum(trunc(d.tbs_maxsize/1024/1024)),'9,999,990') || '</b></td><td class="fourth"><b>' ||
to_char(sum(trunc(nvl(s.free_space, 0)/1024/1024)),'9,999,990') || '</b></td><td class="fourth"><b>' ||
to_char(sum(trunc(d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0))/1024/1024),'9,999,990') || '</b></td>'||
'<td> </td>' as linha,
null odr, null odr2
from
( select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
tablespace_name tablespace
from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_data_files
union all
select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_temp_files
)
group by tablespace_name
) d,
( select SUM(bytes) free_space, tablespace_name tablespace
from dba_free_space group by tablespace_name ) s,
dba_tablespaces t
where t.tablespace_name = d.tablespace(+) and t.tablespace_name = s.tablespace(+)
order by 1,3,4
/
-
- Moderador
- Posts: 1177
- Joined: Thu, 15 Oct 2009 10:28 am
- Location: Recife
Enterprise Manager does not do this?
Alerts by e-mail I know he sends ..
Alerts by e-mail I know he sends ..
-
- Rank: Estagiário Sênior
- Posts: 12
- Joined: Tue, 22 Dec 2009 7:58 am
- Location: Gravataí - RS
Att, Paolo Cemim
I do not if this problem has already been solved, but I will answer the same that there is already for those who have the same doubt.
To use an HTML text body must use Context-Type: Text / HTML. Take an example:
Create a procedure:
Then only use the procedure being the last parameter Part of the HTML text, an example below:
When running this code vera that the message will be in bold. Hope this helps.
To use an HTML text body must use Context-Type: Text / HTML. Take an example:
Create a procedure:
create or replace procedure "SEND_EMAIL_HTML"(
P_ORIGEM in varchar2,
P_DESTINO in varchar2,
P_TITULO in varchar2,
P_ASSUNTO in varchar2,
P_TEXTO in varchar2
)
IS
C UTL_SMTP.CONNECTION;
BEGIN
c:= utl_smtp.open_connection('servidorSMTP',25);
utl_smtp.helo(c, 'servidorSMTP');
UTL_SMTP.MAIL (C, ('<' || P_ORIGEM || '>')); /* E-mail de quem está mandando */
UTL_SMTP.RCPT (C, ('<' || P_DESTINO|| '>')); /* Para quem vou mandar */
utl_smtp.open_data(c);
UTL_SMTP.WRITE_RAW_DATA( c,UTL_RAW.CAST_TO_RAW('From:' ||P_ORIGEM||utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( c,UTL_RAW.CAST_TO_RAW('To:' ||P_DESTINO||utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( c,UTL_RAW.CAST_TO_RAW('Subject:' ||P_ASSUNTO||utl_tcp.CRLF));
utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);
utl_smtp.write_data(c, utl_tcp.crlf || p_texto);
utl_smtp.close_data(c);
UTL_SMTP.QUIT (C);
END;
begin
SEND_EMAIL_HTML ('ORIGEM@ORIGEM',
'DESTINO@DESTINO',
'TITULO',
'ASSUNTO',
'<b>Howdy!');
end;
-
- Information
-
Who is online
Users browsing this forum: No registered users and 13 guests