Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: sending mails from Insert trigger

    I have a Requirement. I have to send mail to the internal users whenever a proposal created and will sending mails from Insert trigger.
    How to do that.Any help it would be appreciated

    Regards
    mohan

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: sending mails from Insert trigger

    Originally posted by mohan
    I have a Requirement. I have to send mail to the internal users whenever a proposal created and will sending mails from Insert trigger.
    How to do that.Any help it would be appreciated

    Regards
    mohan
    You can use UTL_SMTP like this:

    DECLARE
    l_mailhost VARCHAR2(255) := 'smtp.mydomain.com';
    l_mail_conn utl_smtp.connection;
    BEGIN
    l_mail_conn := utl_smtp.open_connection( l_mailhost, 25 );
    utl_smtp.helo(l_mail_conn, l_mailhost);
    utl_smtp.mail(l_mail_conn, 'me@mydomain.com');
    utl_smtp.rcpt(l_mail_conn, 'you@yourdomain.com');
    utl_smtp.open_data(l_mail_conn);
    utl_smtp.write_data(l_mail_conn,'This is the message');
    utl_smtp.close_data(l_mail_conn);
    utl_smtp.quit(l_mail_conn);
    END;
    /

    Rather than call this directly from the trigger, it may be preferable to submit a job using DBMS_JOB to do it because:
    - sending email is quite slow, could impact performance if done synchronously
    - the job will only run, and therefore the mail will only be sent, if the user commits the insert

  3. #3
    Join Date
    Oct 2002
    Posts
    78

    Service not available

    Thnaks for your prompt reply .whenexecuting the code u sent i got the below error message.
    Thanks in advance
    mohan



    SQL> DECLARE
    2 l_mailhost VARCHAR2(255) := 'mail.lookworld.com';
    3 l_mail_conn utl_smtp.connection;
    4 BEGIN
    5 l_mail_conn := utl_smtp.open_connection( l_mailhost, 25 );
    6 utl_smtp.helo(l_mail_conn, l_mailhost);
    7 utl_smtp.mail(l_mail_conn, 'mohan_203@yahoo.com');
    8 utl_smtp.rcpt(l_mail_conn, 'mohan_anp@yahoo.com');
    9 utl_smtp.open_data(l_mail_conn);
    10 utl_smtp.write_data(l_mail_conn,'urgent message');
    11 utl_smtp.close_data(l_mail_conn);
    12 utl_smtp.quit(l_mail_conn);
    13 END;
    14 /
    DECLARE
    *
    ERROR at line 1:
    ORA-20001: 421 Service not available
    ORA-06512: at "SYS.UTL_SMTP", line 83
    ORA-06512: at "SYS.UTL_SMTP", line 121
    ORA-06512: at line 5



    SQL> host ping mail.lookworld.com

    Pinging mail.lookworld.com [65.222.233.3] with 32 bytes of data:

    Reply from 65.222.233.3: bytes=32 time=765ms TTL=112
    Reply from 65.222.233.3: bytes=32 time=766ms TTL=112
    Reply from 65.222.233.3: bytes=32 time=766ms TTL=112
    Reply from 65.222.233.3: bytes=32 time=750ms TTL=112

    Ping statistics for 65.222.233.3:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
    Minimum = 750ms, Maximum = 766ms, Average = 761ms
    SQL>

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Service not available

    Originally posted by mohan
    Thnaks for your prompt reply .whenexecuting the code u sent i got the below error message.

    ORA-20001: 421 Service not available
    ORA-06512: at "SYS.UTL_SMTP", line 83
    ORA-06512: at "SYS.UTL_SMTP", line 121
    ORA-06512: at line 5
    I think the problem is not in the code (it works for me), but in the setup of your mail service. Sorry I don't know enough to suggest what to do next.

  5. #5
    Join Date
    Mar 2003
    Posts
    4

    utl_smpt

    Using the utl_smtp and utl_tcp packages requires that the JServer option is
    installed in the database.

    Reference: Oracle8i Supplied PL/SQL Packages Reference, 65 UTL_SMTP

Posting Permissions

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