DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: utl_smtp won't send mail

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    164

    utl_smtp won't send mail

    Dear all,

    I am trying to send email to user to notify password expiration in my 9i database; however, I am not able to send email by using utl_smtp package.

    These are what I am trying to do:
    1. list all users whose passwords are expirying in 12 days
    2. send one email to designated users with this list of users.

    Here are my codes:
    CREATE OR REPLACE PROCEDURE sendmail
    ( sender IN VARCHAR2,
    recipient IN VARCHAR2,
    subject IN VARCHAR2,
    message IN varchar2)

    IS
    mailhost VARCHAR2(100) := 'oracleserver';
    c UTL_SMTP.Connection;

    BEGIN
    dbms_output.put_line('message: '|| message);

    c :=UTL_SMTP.open_connection(mailhost,25);
    UTL_SMTP.Helo(c,mailhost);
    UTL_SMTP.Mail(c,sender);
    UTL_SMTP.Rcpt(c,recipient);
    UTL_SMTP.Open_data(c);
    UTL_SMTP.Write_Data(c, 'From: <' || sender || '>' || utl_tcp.CRLF);
    UTL_SMTP.Write_Data(c, 'To: <' || recipient || '>' || utl_tcp.CRLF);
    UTL_SMTP.Write_Data(c, 'Subject: ' || subject || utl_tcp.CRLF);
    UTL_SMTP.Write_Data(c, utl_tcp.CRLF||message);
    UTL_SMTP.close_data(c);
    UTL_SMTP.quit(c);

    EXCEPTION
    WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
    UTL_SMTP.quit(c);
    raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm);
    WHEN OTHERS THEN
    raise_application_error(-20001, 'The following error has occured: ' || sqlerrm);
    END;
    /


    create or replace procedure ck_expiry AS
    Cursor c_getexpiry is
    select username,expiry_date
    from dba_users;

    exp_date Date;
    day_left Number(3);
    grace_day Number(3) := 12;
    mesg Varchar2(2000) := null;
    email_list varchar2(2000) := 'user1@yahoo.com,user2@yahoo.com,user3@yahoo.com';
    sender varchar2(2000) := 'oracle@oracleserver.com';
    host_name SYS.v_$instance.host_name%type;

    Begin
    For i in c_getexpiry Loop
    day_left := (to_date(i.expiry_date,'dd-mm-yyyy')- to_date(sysdate,'DD-MM-YYYY'));

    If (day_left <= grace_day and day_left >= 0) then
    mesg := mesg||i.username||'-'|| day_left ||chr(10);
    End If;
    End Loop;

    select host_name Into host_name
    from sys.v_$instance;

    sendmail (SENDER => sender,
    RECIPIENT => email_list,
    SUBJECT => host_name||': Password Notification',
    MESSAGE => mesge);
    Exception
    When others then
    dbms_output.put_line ('Main: '||sqlerrm);
    End;
    /


    When I execute the ck_expiry procedure, I got no email but these returns:
    message: MSGING-7
    METARW-7
    METARO-7
    METADW-7
    META-7

    If I execute sendmail procedure directly I got email:
    exec sendmail (sender=>'oracle@oracleserver.com',recipient=>'user1@yahoo.com',subject=> 'Password Notification',message=>'MSGING-7'||chr(10)||'METARW-7'||chr(10)||'METARO-7'||chr(10)||'METADW-7'||chr(10)||'META-7');

    Are there limitations for utl_smtp.write_data? How can I go around it? Any help is appreciated.


    Thanks,
    Unna

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Hard for me to follow ck_expiry storedproc logic.

    I would expect...

    1- Populate the cursor with only expired account -not all accounts.
    2- While looping the cursor either to send an individual email for each account or append each individual email account to the recipient's list to send a spam alike email at the end -none of these appear to happen.

    Last but not least it beats me how the storedproc is going to learn the email account of each affected user in a real world scenario.
    Last edited by PAVB; 05-11-2009 at 12:37 PM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Posts
    164
    Pavb,

    Thanks!

    Our user accounts are not managed by the individual users but rather by application managers. That's why it works best for me to send an email to each of the managers about the expiring user accounts rather than an email to each expiring user.

    In 10g, I use the same logic by using the utl_mail package, which works just fine; however, in 9i, I not not able to send mail with utl_smtp procedure. My goal is able to send an email with a list of users (multiple lines, one line per user); but it doesn't seem the utl_smtp works that way.

    Can you advice how I can accomplish this?


    Thanks,
    Unna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width