Augusto,
Se a tua intenção for enviar uma mensagem html pura e simplismente sem
anexo,você não precisa de uma JSP(java stored procedure) pode fazer isso
usando apenas as utls do próprio oracle abaixo segue uma procedure que eu
desenvolvi que efetua envio de emails com o body em html.A variável mensagem
recebe o texto em linguagem html.
exemplo da chamada da procedure.
Declare
crlf varchar2(2):= CHR(13) || CHR(10);
Begin
prc_email([EMAIL PROTECTED],'Html via Oracle',
'<HTML>'||crlf||
'<HEAD>'||crlf||
'<TITLE>'||crlf||
'Html via Oracle'||crlf||
'</TITLE>'||crlf||
'</HEAD>'||crlf||
'<BODY>'||crlf||
'Efetuando envio de email via oracle.<BR>'
'</BODY>'
'</HTML>',[EMAIL PROTECTED]);
End;
create or replace PROCEDURE PRC_EMAIL
(DESTINATARIO IN VARCHAR2
,ASSUNTO IN VARCHAR2
,MENSAGEM IN VARCHAR2
,P_REMETENTE IN VARCHAR2
)
IS
CONEXAO UTL_SMTP.CONNECTION;
crlf varchar2(2):= CHR(13) || CHR(10);
host varchar2(20) := 'xxx.x.xx.xx';
BEGIN
conexao := utl_smtp.open_connection (host,25);
utl_smtp.helo(conexao, host);
utl_smtp.mail(conexao, P_remetente);
utl_smtp.rcpt(conexao, DESTINATARIO);
utl_smtp.open_data(conexao);
UTL_SMTP.WRITE_RAW_DATA(conexao,UTL_RAW.CAST_TO_RAW('From:' ||
p_remetente ||utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA(conexao,UTL_RAW.CAST_TO_RAW('To:' ||
destinatario|| utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( conexao,UTL_RAW.CAST_TO_RAW('Subject:' ||
assunto||utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( conexao,UTL_RAW.CAST_TO_RAW('Content-
Type:text/html'||utl_tcp.CRLF));
utl_smtp.write_data(conexao,' '||utl_tcp.CRLF);
utl_smtp.write_raw_data(conexao,utl_raw.cast_to_raw(utl_tcp.CRLF||mensagem
));
utl_smtp.close_data(conexao);
utl_smtp.quit(conexao);
Exception
WHEN OTHERS THEN
utl_smtp.quit (conexao);
raise_application_error(-20300, 'erro: '||sqlerrm);
End;
Atenciosamente,
Sirleno Vidaletti
Desenvolvedor Oracle
Fundação Aplub de Crédito Educativo.
-----Mensagem original-----
De: oracle_br@yahoogrupos.com.br [mailto:[EMAIL PROTECTED]
nome de Augusto Cesar R. Costa
Enviada em: quinta-feira, 22 de fevereiro de 2007 10:06
Para: oracle_br@yahoogrupos.com.br
Assunto: [oracle_br] Enviar e-mail pelo Oracle com Conteúdo HTML
Pessoal, bom dia.
Estou precisando de uma ajuda, se alguém já tiver algo pronto ou souber como
fazer agradeço.
Tenho uma rotina que envia e-mail com conteúdo em html através do Oracle,
utilizando Java Stored Procedure.
O problema é que o body do e-mail é montado utilizando uma variável varchar2
de tamanho 32767, e, estou precisando aumentar o tamanho do body deste
e-mail. Pensei em utilizar campo CLOB para a montagem do body do e-mail, mas
não consegui fazer isso pois a varíavel que a Java Stored Procedure recebe
como parâmetro é do tipo string.
Não consigo alterar o fonte da Java Stored Procedure por não conhecer nada
de Java.
De antemão aviso que não é possível dividir o e-mail em vários.
As informações em relação a versão do Oracle e do SO estão a seguir:
BANNER
----------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
O Linux utilizado é o Red Hat EL 4.
Segue abaixo os fontes da rotina de envio de e-mail, infelizmente não tenho
os créditos de quem fez.
Atenciosamente.
Augusto Cesar Rodovalho Costa
----------------------------------------------------------
PACKAGE em PL/SQL que executa a Java Stored Procedure
----------------------------------------------------------
CREATE OR REPLACE PACKAGE SendMailJPkg AS
-- EOL is used to separate text line in the message body;
EOL CONSTANT STRING(2) := CHR(13) || CHR(10);
TYPE ATTACHMENTS_LIST IS TABLE OF VARCHAR2(4000);
-- high-level interface with collections;
PROCEDURE SendMail(SMTPServerName IN STRING
,Sender IN STRING
,Recipient IN STRING
,CcRecipient IN STRING DEFAULT ''
,BccRecipient IN STRING DEFAULT ''
,Subject IN STRING DEFAULT ''
,Body IN STRING DEFAULT ''
,ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST DEFAULT NULL);
PROCEDURE SendMailHTML(SMTPServerName IN STRING
,Sender IN STRING
,Recipient IN STRING
,CcRecipient IN STRING
,BccRecipient IN STRING
,Subject IN STRING
,Body IN STRING
,ErrorMessage OUT STRING);
END SendMailJPkg;
CREATE OR REPLACE PACKAGE BODY SendMailJPkg AS
PROCEDURE ParseAttachment(Attachments IN ATTACHMENTS_LIST
,AttachmentList OUT VARCHAR2)
IS
AttachmentSeparator CONSTANT VARCHAR2(12) := '///';
BEGIN
-- boolean short-circuit is used here;
IF Attachments IS NOT NULL AND Attachments.COUNT > 0 THEN
AttachmentList := Attachments(Attachments.FIRST);
-- scan the collection if there is more than one element. If there
-- is not, skip the next part for parsing elements 2 and above. If there
-- is, skip the first element since it has been already processed
IF Attachments.COUNT > 1 THEN
FOR I IN Attachments.NEXT(Attachments.FIRST).. Attachments.LAST LOOP
AttachmentList := AttachmentList || AttachmentSeparator || Attachments(I);
END LOOP;
ELSE
-- whe have to terminate the list with the one element with /// for the java
function
AttachmentList := AttachmentList || AttachmentSeparator;
END IF;
ELSE
AttachmentList := '';
END IF;
END ParseAttachment;
-- forward declaration;
FUNCTION JSendMail(SMTPServerName IN STRING
,Sender IN STRING
,Recipient IN STRING
,CcRecipient IN STRING
,BccRecipient IN STRING
,Subject IN STRING
,Body IN STRING
,ErrorMessage OUT STRING
,Attachments IN STRING)
RETURN NUMBER;
-- high-level interface with collections;
PROCEDURE SendMail(SMTPServerName IN STRING
,Sender IN STRING
,Recipient IN STRING
,CcRecipient IN STRING
,BccRecipient IN STRING
,Subject IN STRING
,Body IN STRING
,ErrorMessage OUT STRING
,Attachments IN ATTACHMENTS_LIST)
AS
AttachmentList VARCHAR2(4000) := '';
v_Retorno NUMBER;
BEGIN
ParseAttachment(Attachments,AttachmentList);
v_Retorno := JSendMail(SMTPServerName
,Sender
,Recipient
,CcRecipient
,BccRecipient
,Subject
,Body
,ErrorMessage
,AttachmentList);
END SendMail;
-- JSendMail's body is the java function SendMail.Send();
-- thus, no PL/SQL implementation is needed;
FUNCTION JSendMail(SMTPServerName IN STRING
,Sender IN STRING
,Recipient IN STRING
,CcRecipient IN STRING
,BccRecipient IN STRING
,Subject IN STRING
,Body IN STRING
,ErrorMessage OUT STRING
,Attachments IN STRING)
RETURN NUMBER
IS
LANGUAGE JAVA NAME
'SendMail.Send(java.lang.String,java.lang.String,java.lang.String,java.lang.
String,java.lang.String,java.lang.String,java.lang.String,java.lang.String[]
,java.lang.String) return int';
FUNCTION JSimpleMail1(SMTPServerName IN STRING
,Sender IN STRING
,Recipient IN STRING
,CcRecipient IN STRING
,BccRecipient IN STRING
,Subject IN STRING
,Body IN STRING
,ErrorMessage OUT STRING)
RETURN NUMBER;
PROCEDURE SendMailHTML(SMTPServerName IN STRING
,Sender IN STRING
,Recipient IN STRING
,CcRecipient IN STRING
,BccRecipient IN STRING
,Subject IN STRING
,Body IN STRING
,ErrorMessage OUT STRING)
AS
v_Retorno NUMBER;
BEGIN
v_Retorno := JSimpleMail1(SMTPServerName
,Sender
,Recipient
,CcRecipient
,BccRecipient
,Subject
,Body
,ErrorMessage);
END SendMailHTML;
FUNCTION JSimpleMail1(SMTPServerName IN STRING
,Sender IN STRING
,Recipient IN STRING
,CcRecipient IN STRING
,BccRecipient IN STRING
,Subject IN STRING
,Body IN STRING
,ErrorMessage OUT STRING)
RETURN NUMBER
IS
LANGUAGE JAVA NAME
'SendMail.SendMailHTML(java.lang.String,java.lang.String,java.lang.String,ja
va.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.
String[]) return int';
END SendMailJPkg;
----------------------------------------------------------
Fonte da Java Stored Procedure
----------------------------------------------------------
create or replace and compile java source named sendmail as
import java.util.*;
import java.io.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
public class SendMail
{
// Sender, Recipient, CCRecipient, and BccRecipient are comma-
// separated lists of addresses;
// Body can span multiple CR/LF-separated lines;
// Attachments is a
///-separated list of file names;
public static int Send(String SMTPServer,
String Sender,
String Recipient,
String CcRecipient,
String BccRecipient,
String Subject,
String Body,
String ErrorMessage[],
String Attachments)
{
// Error status;
int ErrorStatus = 0;
// create some properties and get the default Session;
Properties props = System.getProperties();
props.put("mail.smtp.host", SMTPServer);
Session session = Session.getDefaultInstance(props, null);
try
{
// create a message;
MimeMessage msg = new MimeMessage(session);
// extracts the senders and adds them to the message;
// Sender is a comma-separated list of e-mail addresses as
// per RFC822;
{
InternetAddress[] TheAddresses = InternetAddress.parse(Sender);
msg.addFrom(TheAddresses);
}
// extract the recipients and assign them to the message;
// Recipient is a comma-separated list of e-mail addresses
// as per RFC822;
{
InternetAddress[] TheAddresses = InternetAddress.parse(Recipient);
msg.addRecipients(Message.RecipientType.TO,TheAddresses);
}
// extract the Cc-recipients and assign them to the
// message;
// CcRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != CcRecipient)
{
InternetAddress[] TheAddresses = InternetAddress.parse(CcRecipient);
msg.addRecipients(Message.RecipientType.CC,TheAddresses);
}
// extract the Bcc-recipients and assign them to the
// message;
// BccRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != BccRecipient)
{
InternetAddress[] TheAddresses = InternetAddress.parse(BccRecipient);
msg.addRecipients(Message.RecipientType.BCC,TheAddresses);
}
// subject field;
msg.setSubject(Subject);
// create the Multipart to be added the parts to;
Multipart mp = new MimeMultipart();
// create and fill the first message part;
{
MimeBodyPart mbp = new MimeBodyPart();
mbp.setText(Body);
// attach the part to the multipart;
mp.addBodyPart(mbp);
}
// attach the files to the message;
if (null != Attachments)
{
int StartIndex = 0, PosIndex = 0;
while (-1 != (PosIndex = Attachments.indexOf("///",StartIndex)))
{
// create and fill other message parts;
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds = new
FileDataSource(Attachments.substring(StartIndex,PosIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
PosIndex += 3;
StartIndex = PosIndex;
}
// last, or only, attachment file;
if (StartIndex < Attachments.length())
{
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds = new FileDataSource(Attachments.substring(StartIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
}
}
// add the Multipart to the message;
msg.setContent(mp);
//msg.setContent("<h1>Hello world</h1>", "text/html");
// set the Date: header;
msg.setSentDate(new Date());
// send the message;
Transport.send(msg);
}
catch (MessagingException MsgException)
{
ErrorMessage[0] = MsgException.toString();
Exception TheException = null;
if ((TheException = MsgException.getNextException()) != null)
ErrorMessage[0] = ErrorMessage[0] + "\n" + TheException.toString();
ErrorStatus = 1;
}
return ErrorStatus;
}
public static int SendMailHTML(String SMTPServer,
String Sender,
String Recipient,
String CcRecipient,
String BccRecipient,
String Subject,
String Body,
String ErrorMessage[])
{
// Error status;
int ErrorStatus = 0;
// create some properties and get the default Session;
Properties props = System.getProperties();
props.put("mail.smtp.host", SMTPServer);
Session session = Session.getDefaultInstance(props, null);
try
{
// create a message;
MimeMessage msg = new MimeMessage(session);
// extracts the senders and adds them to the message;
// Sender is a comma-separated list of e-mail addresses as
// per RFC822;
{
InternetAddress[] TheAddresses = InternetAddress.parse(Sender);
msg.addFrom(TheAddresses);
}
// extract the recipients and assign them to the message;
// Recipient is a comma-separated list of e-mail addresses
// as per RFC822;
{
InternetAddress[] TheAddresses = InternetAddress.parse(Recipient);
msg.addRecipients(Message.RecipientType.TO,TheAddresses);
}
// extract the Cc-recipients and assign them to the
// message;
// CcRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != CcRecipient)
{
InternetAddress[] TheAddresses = InternetAddress.parse(CcRecipient);
msg.addRecipients(Message.RecipientType.CC,TheAddresses);
}
// extract the Bcc-recipients and assign them to the
// message;
// BccRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != BccRecipient)
{
InternetAddress[] TheAddresses = InternetAddress.parse(BccRecipient);
msg.addRecipients(Message.RecipientType.BCC,TheAddresses);
}
// subject field;
msg.setSubject(Subject);
// add the Multipart to the message;
msg.setContent(Body, "text/html");
// set the Date: header;
msg.setSentDate(new Date());
// send the message;
Transport.send(msg);
}
catch (MessagingException MsgException)
{
ErrorMessage[0] = MsgException.toString();
Exception TheException = null;
if ((TheException = MsgException.getNextException()) != null)
ErrorMessage[0] = ErrorMessage[0] + "\n" + TheException.toString();
ErrorStatus = 1;
}
return ErrorStatus;
}
}
[As partes desta mensagem que não continham texto foram removidas]
