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]
>
