Chiappa, obrigado pela indicação. Teria me enviar esta nota? O acesso ao metalink que temos está temporariamente suspenso. Atenciosamente.
----- Original Message ----- From: jlchiappa To: oracle_br@yahoogrupos.com.br Sent: Thursday, February 22, 2007 11:34 AM Subject: [oracle_br] Re: Enviar e-mail pelo Oracle com Conteúdo HTML Em PL/SQL o tamanho máximo de uma string é 32767, ponto, não tem como vc aumentar. O que vc deverá fazer é RE-ESCREVER a rotina de modo a trabalhar com "pedaços" de informação, e já que vc NÃO sabe nada de java E tem bd 10g (aonde NÃO é mais necessário vc usar java pra enviar e-mail, a package UTL_MAIL substituiu isso) eu diria pra vc seguir a nota Subject: How To Send Attachments Of Size Greater Than 32 KB Using UTL_SMTP Package , Doc ID: Note:357385.1 no metalink. []s Chiappa --- Em oracle_br@yahoogrupos.com.br, "Augusto Cesar R. Costa" <[EMAIL PROTECTED]> escreveu > > 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,j ava.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,java.lang.String,j ava.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] > [As partes desta mensagem que não continham texto foram removidas]