1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

send two e-mails from Oracle database

Discussion in 'SQL PL/SQL' started by vamsioracle, Mar 24, 2009.

  1. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi all

    Can't we send two mails from the same procedure.

    My code is structured like this:

    create procedure ......


    capture data 1 and data2

    utl_smtp.connection()
    send data1 to mail id1
    utl_smtp.close conenction

    -- her i agian call utl_smtp

    utl_smtp.connection

    send data2 to mail id2

    close connection

    end procedure


    This is throwing an error. If ui comment the second utl_smtp.connection then procedure executes and sends mail.

    If we cant send two mail, then what is the best procedure to do so


    vamsi
     
  2. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    hi vamsi why do you want to use the cumbersome utl_smtp package to send emails? Is there any special requirement to use utl_smtp. If not, use the better utl_mail package. The UTL_MAIL package may not be installed by default in your database. If it is not it's very simple to set it up.

    To setup utl_mail follow the simple method below:
    Code (Text):

    SQL> connect sys/password as sysdba
    Connected.

    SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

    Package created.

    Synonym created.

    SQL> @$ORACLE_HOME /rdbms/admin/prvtmail.plb

    Package body created.

    No errors.
     
    That's it! Next, the SMTP_OUT_SERVER parameter must be configured. You must connect to SYS and then use the alter system command to configure SMTP_OUT_SERVER parameter as shown here:
    Code (Text):

    SQL> alter system set smtp_out_server = '<ip-address:port' scope=Both;

    System altered.
     
    The syntax to send emails is as below:
    Code (Text):

    UTL_MAIL.SEND( sender IN VARCHAR2,
                   recipients IN VARCHAR2,
                   cc IN VARCHAR2 DEFAULT NULL,
                   bcc IN VARCHAR2 DEFAULT NULL,
                   subject IN VARCHAR2 DEFAULT NULL,
                   message IN VARCHAR2,
                   mime_type IN VARCHAR2 DEFAULT
                   'text/plain; charset=us-ascii',
                   priority IN PLS_INTEGER DEFAULT  NULL
                 );
     
    As an example, let's put the utl_mail.send function in a begin-end block:
    Code (Text):

    begin
     utl_mail.send(
       sender => 'kirti@yahoo.com',
       recipients => 'vamsi@yahoo.com',
       subject => 'Testing utl_mail',
       message => 'The receipt of this email means'||
        ' that send mail'||
        ' works for UTL_MAIL '
       );
    end;
     
    See as simple as that. You can send as many times as you want, why only twice?

    :)
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    that's a brilliant post kirti...
     
  4. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Thanks kirti,

    That was really useful. I will use this.

    If at all my client's DBA is not ready to install the utl_mail package, i have to come back again to utl_smtp. so, it would be better if we keep the alternative solution too with us, using utl_smtp.


    Once again thanks a lot

    vamsi
     
  5. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Well, You can create a small procedure to send the mail and use it twice in your main program to send mails
    Example:
    Code (Text):
    CREATE OR REPLACE PROCEDURE SEND_MAIL (
      msg_to      varchar2,
      msg_subject varchar2,
      msg_text    varchar2   )
    IS
      c  utl_smtp.connection;
      rc integer;
      msg_from    varchar2(50) := 'Oracle9.2';
      mailhost    VARCHAR2(30) := '127.0.0.1';   -- local database host
     
    BEGIN
      c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
      utl_smtp.helo(c, mailhost);
      utl_smtp.mail(c, msg_from);
      utl_smtp.rcpt(c, msg_to);
     
      utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                             'To: ' || msg_to || utl_tcp.crlf ||
                             'Subject: ' || msg_subject ||
                              utl_tcp.crlf || msg_text);
      utl_smtp.quit(c);
     
      EXCEPTION
        WHEN UTL_SMTP.INVALID_OPERATION THEN
           dbms_output.put_line(' Invalid Operation in Mail attempt    
                                  using UTL_SMTP.');
        WHEN UTL_SMTP.TRANSIENT_ERROR THEN
           dbms_output.put_line(' Temporary e-mail issue - try again');  
        WHEN UTL_SMTP.PERMANENT_ERROR THEN
           dbms_output.put_line(' Permanent Error Encountered.');  
    END;
     
    [B]Main Procedure [/B]
     
    create procedure ......
     
     
    capture data 1 and data2
     
    send_mail(msg_to=><recipient address>,
                  msg_subject=><subject Line>,
                  msg_text=>data1
                 );
     
    -- her i agian call utl_smtp
     
    send_mail(msg_to=><recipient address>,
                  msg_subject=><subject Line>,
                  msg_text=>data2
                 );
     
    end procedure
     
     
     
  6. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Thanks a lot

    So, that means i cannot call utl_smtp twice in a procedure at a time. Am i right?
     
  7. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Thanks dude

    It worked for me.

    Can i use the same code on my laptop to send mails to my gmail ot Ymail or any other mail. I am using XP, oracle 10g. I tried to do that, but it either says SMTP transient error or cannot relay.

    Is it only when database is on unix server, i can use utl package. How can i make it work on my XP system.
     
  8. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi vamsi, utl_smtp just sends commands to your smtp server. If you don't have smtp running on your laptop (i presume not) there is nothing oracle can do.
     
  9. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    ok, i got it

    Then how smtp server
     
  10. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    sorry for my previous post

    How to setup smtp server on my laptop.


    vamsi
     
  11. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    You need a webserver like IIS or Apache running on your laptop. Since u r using XP, IIS might already be installed. If not it can be installed from your windows xp cd using the add remove programs. After you install IIS you need to configure smtp server. Refer the link below for more info

    http://msdn.microsoft.com/en-us/library/8b83ac7t.aspx
     
  12. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    It's not about smtp server running in your laptop. You need to have the addresses and ports of smtp and pop3 servers yahoo/googlemail uses, which means, you need to pay them something to get those.
     
  13. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    yes that's another way to do it... the idea is either you have an smtp server running yourself, or you get the smtp address of some email provider like yahoo, gmail. Gmail provides their smtp addresses for free i think... i am not sure though