9 Replies Latest reply: Mar 7, 2007 11:46 AM by 454783 RSS

    Email in Oracle

    515228
      Hi I am Shahzad and i want to ask that is it possible in oracle that we can send the email to a adress.for example i want to get some new rates for shipment from a courier or forwarder. thanks
        • 1. Re: Email in Oracle
          475314
          pl/sql or java?
          • 2. Re: Email in Oracle
            518905
            Yes you can send an email in pl_sql through utl_smtp package

            there is another package utl_mail which is available in oracle 10g.

            cheers

            ravi
            • 3. Re: Email in Oracle
              475314
              It is true, if you do not want to send really simple, plain text e-mails. If you want do something fancy; like attachments, multipart, embedded content... etc. I advice you to implement in Java; Use Java EE Mail APIs.
              • 4. Re: Email in Oracle
                518905
                can i invoke java mail API in forms 6i?

                if yes then how?

                thx

                ravi
                • 5. Re: Email in Oracle
                  475314
                  can you call any store procedures in Form 6i?
                  • 6. Re: Email in Oracle
                    518905
                    i have created a pl/sql procedure which i called in form 6i for sending
                    mail, it sends only plain text.now my users wants to editing their mail before
                    sending, it's not possible in forms so i wanna to use java . if u can help then help.

                    thx

                    ravi
                    • 7. Re: Email in Oracle
                      475314
                      If only you need is plain text simple e-mail, you do not need to use Java. PL/SQL mail package is enough. For editing; why don't you create a form with textarea in it, to let user edit body of the mail. then pass that modified mail body to your prodecure.

                      anyhow; here is the quintessential java program to send e-mail; This example demonstrates the simplest program that will send a textual E-mail message to a single recipient. I am sure you can easily convert that sample to Java stored produce and call it.

                      import java.io.*;
                      import javax.mail.*;
                      import javax.mail.internet.*;
                      import javax.activation.*;

                      public class SendApp {
                      public static void send(String smtpHost, int smtpPort,
                      String from, String to,
                      String subject, String content)
                      throws AddressException, MessagingException {

                      // Create a mail session
                      java.util.Properties props = new java.util.Properties();
                      props.put("mail.smtp.host", smtpHost);
                      props.put("mail.smtp.port", ""+smtpPort);
                      Session session = Session.getDefaultInstance(props, null);

                      // Construct the message
                      Message msg = new MimeMessage(session);
                      msg.setFrom(new InternetAddress(from));
                      msg.setRecipient(Message.RecipientType.TO, new InternetAddress(to));
                      msg.setSubject(subject);
                      msg.setText(content);

                      // Send the message
                      Transport.send(msg);
                      }

                      public static void main(String[] args) throws Exception {
                      // Send a test message
                      send("hostname", 25, "joe@example.com", "sue@example.com", "re: meeting", "How about at 2 PM?");
                      }
                      }
                      • 8. Re: Email in Oracle
                        518905
                        thx

                        i'm trying to convert your code into oracle java procedure.

                        v k ravi
                        • 9. Re: Email in Oracle - PL/SQL code to send email
                          454783
                          CREATE OR REPLACE PROCEDURE SR3PMAIL ( P_MAILHOST IN VARCHAR2,
                          P_SENDER IN VARCHAR2,
                          P_RECIPIENT IN VARCHAR2,
                          P_CC IN VARCHAR2,
                          P_SUBJECT IN VARCHAR2,
                          P_MENSAG IN VARCHAR2 ) IS
                          ----------------------------------------------------------------------------------
                          -- --
                          -- APLICACAO...........UTILITARIOS --
                          -- --
                          -- SISTEMA.............SQL@REPLICATOR VERSAO 4.3 --
                          -- --
                          -- VERSAO..............4.3.1(MARÇO 2003) --
                          -- --
                          -- OBSERVACOES: --
                          -- --
                          -- AUTHOR..............RENAN MEDEIROS --
                          -- --
                          -- DATA................17/06/2004 - CODIFICAÇÃO --
                          -- --
                          -- 28/06/2006 - BUG - NAO ENVIAVA CC(COM COPIA) --
                          ----------------------------------------------------------------------------------

                          SR3$VER VARCHAR2(50) := '==>V5.0.1,28/06/2006-10:40,RENAN<==';
                          SR3NAM VARCHAR2(15) := 'SQL@Replicator';
                          SR3VER VARCHAR2(3) := '501'; /* VERSAO */


                          MENSAGEM VARCHAR2(30000) := P_MENSAG;
                          MENSAGEM2 VARCHAR2(30000) := NULL;

                          MAIL_CONN UTL_SMTP.CONNECTION;

                          CRLF VARCHAR2(0002) := CHR(13) || CHR(10);
                          MESG VARCHAR2(4000) := NULL;
                          DESTINO VARCHAR2(2000) := NULL;
                          DESTINO2 VARCHAR2(2000) := NULL;
                          TAMANHO BINARY_INTEGER := 1;

                          POSINI BINARY_INTEGER := 1;
                          POSFIM BINARY_INTEGER := 1;
                          POSEND BINARY_INTEGER := 1;

                          BEGIN
                          MENSAGEM := NULL;
                          MENSAGEM2:= NULL;
                          MENSAGEM := P_MENSAG;
                          DESTINO2 := REPLACE(P_RECIPIENT,';',' ')||' ';
                          FOR I IN 1..LENGTH(DESTINO2)
                          LOOP

                          DESTINO2 := REPLACE(DESTINO2,' ',' ');
                          END LOOP;

                          MAIL_CONN := UTL_SMTP.OPEN_CONNECTION( P_MAILHOST, 25 );
                          UTL_SMTP.HELO( MAIL_CONN, P_MAILHOST );
                          UTL_SMTP.MAIL( MAIL_CONN, P_SENDER );

                          MESG:=
                          'FROM: <' || P_SENDER || '>' || CRLF ||
                          'SUBJECT: ' || P_SUBJECT || CRLF;
                          MESG := MESG||
                          'TO:' || NVL(P_RECIPIENT,' ') || CRLF;

                          MESG := MESG||
                          'CC:' || NVL(P_CC,' ') || CRLF;

                          --
                          --
                          -- SEPARA OS DESTINATARIOS
                          -- -----------------------
                          --
                          --
                          POSINI:=1;
                          POSFIM:=0;
                          POSEND:=INSTR(DESTINO2,' ',-1,1);
                          TAMANHO:=POSFIM-POSINI;
                          WHILE POSFIM < POSEND
                          LOOP
                          POSINI:=POSFIM+1;
                          POSFIM:=INSTR(DESTINO2,' ',POSINI,1);
                          TAMANHO:=POSFIM-POSINI;
                          DESTINO:=SUBSTR(DESTINO2,POSINI,TAMANHO);
                          UTL_SMTP.RCPT( MAIL_CONN, DESTINO);
                          END LOOP;
                          DESTINO2:=NULL;
                          DESTINO :=NULL;
                          IF P_CC IS NOT NULL
                          THEN DESTINO2 := REPLACE(P_CC,';',' ')||' ';
                          FOR I IN 1..LENGTH(DESTINO2)
                          LOOP

                          DESTINO2 := REPLACE(DESTINO2,' ',' ');
                          END LOOP;

                          POSINI:=1;
                          POSFIM:=0;
                          POSEND:=INSTR(DESTINO2,' ',-1,1);
                          TAMANHO:=POSFIM-POSINI;
                          WHILE POSFIM < POSEND
                          LOOP
                          POSINI:=POSFIM+1;
                          POSFIM:=INSTR(DESTINO2,' ',POSINI,1);
                          TAMANHO:=POSFIM-POSINI;
                          DESTINO:=SUBSTR(DESTINO2,POSINI,TAMANHO);
                          UTL_SMTP.RCPT( MAIL_CONN, DESTINO);
                          END LOOP;
                          END IF;

                          UTL_SMTP.OPEN_DATA( MAIL_CONN);
                          UTL_SMTP.WRITE_DATA( MAIL_CONN, MESG||CRLF);
                          WHILE LENGTH(MENSAGEM) > 2000
                          LOOP
                          UTL_SMTP.WRITE_DATA( MAIL_CONN, SUBSTR(MENSAGEM,1,2000));
                          MENSAGEM2 := SUBSTR(MENSAGEM,2001,LENGTH(MENSAGEM)-2000);
                          MENSAGEM := MENSAGEM2;
                          END LOOP;
                          UTL_SMTP.WRITE_DATA( MAIL_CONN, MENSAGEM||CRLF);
                          UTL_SMTP.CLOSE_DATA( MAIL_CONN);
                          utl_smtp.quit(mail_conn);
                          END SR3PMAIL;
                          /
                          SHOW ERRORS