This discussion is archived
9 Replies Latest reply: Mar 7, 2007 9:46 AM by 454783 RSS

Email in Oracle

515228 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    pl/sql or java?
  • 2. Re: Email in Oracle
    518905 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    can i invoke java mail API in forms 6i?

    if yes then how?

    thx

    ravi
  • 5. Re: Email in Oracle
    475314 Newbie
    Currently Being Moderated
    can you call any store procedures in Form 6i?
  • 6. Re: Email in Oracle
    518905 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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