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

Thread: Oracle email

  1. #1
    Join Date
    Apr 2002
    Posts
    86
    Can some body guide me How to send mail from Oracle.
    Which package need to be install..?
    what configuration need to be done for that.

    Thanks,
    Harry

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    In order to send email from Oracle package UTL_SMTP must exist in the database.This package is created by running utlsmtp.sql and is available from Oracle version 8.1.6 and above.
    The following procedure allows to send the email through Oracle

    CREATE OR REPLACE PROCEDURE send_email
    ( sender IN VARCHAR2,
    recipient IN VARCHAR2,
    subject IN VARCHAR2,
    message IN VARCHAR2)
    AS
    mailhost VARCHAR2(100) := 'mailhost.fictional-domain.com';
    mail_conn utl_smtp.connection;
    BEGIN
    mail_conn :=utl_smtp.open_connection(mailhost);
    utl_smtp.helo(mail_conn,mailhost);
    utl_smtp.mail(mail_conn,sender); -- sender
    utl_smtp.rcpt(mail_conn,recipient); -- recipient
    utl_smtp.open_data(mail_conn);
    send_header(mail_conn,'From', '"Sender" <'||sender||'>');
    send_header(mail_conn,'To', '"Recipient" <'||recipient||'>');
    send_header(mail_conn,'Subject', subject);
    utl_smtp.write_data(mail_conn, utl_tcp.CRLF||message);
    utl_smtp.close_data(mail_conn);
    utl_smtp.quit(mail_conn);
    EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    utl_smtp.quit(mail_conn);
    raise_application_error(-20000,
    'Failed tosend mail due to the following error: ' || sqlerrm);
    WHEN OTHERS THEN
    raise_application_error(-20001,
    'The following error has occured: ' || sqlerrm);
    END;
    /


    sql>EXECUTE send_email('me@fictional-domain.com','you@fictional-domain.com','This is a UTL_SMTP-generated email','Hi There !');



    Regards,
    ROhit Nirkhe,Oracle DBA,OCP 8i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    All that will work if JVM is also installed.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Apr 2002
    Posts
    86
    How can I check that this UTL_SMTP is installed or not..?

    Thanks,
    Harry

  5. #5
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Cool

    Harry,

    Complete scripts and PL/SQL procedures could be found
    Here

    Examples include UTL_SMTP on 8i and 9i.

    Hope that helps,



    --clio_usa
    Senior Oracle DBA

    http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi
    -----------------------------------------------------------



  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by harrygulls
    How can I check that this UTL_SMTP is installed or not..?

    Thanks,
    Harry
    Harry, if select count(*) from dba_source where name = 'UTL_SMTP'; returns something like 409, then you have it, if it returns 0, obviously not.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    If you don't currently have the JServer loaded run the following as SYS:

    SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
    SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql

    http://www.oracle-base.com/Articles/...romPLSQL9i.asp

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  8. #8
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    Hi All,

    Rohit, I tried running the procedure and got some error. Where could I find the Send_header program unit?

    Thanks,
    Skid


    ==================================

    Errors for PROCEDURE SEND_EMAIL:

    LINE/COL ERROR
    -------- ----------------------------------------------------------------
    15/1 PLS-00201: identifier 'SEND_HEADER' must be declared
    15/1 PL/SQL: Statement ignored
    16/1 PLS-00201: identifier 'SEND_HEADER' must be declared
    16/1 PL/SQL: Statement ignored
    17/1 PLS-00201: identifier 'SEND_HEADER' must be declared
    17/1 PL/SQL: Statement ignored

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