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!

Identifier UTL_SMTP must be declared on sending emails from Oracle Database

Discussion in 'SQL PL/SQL' started by Nithya, Mar 19, 2010.

  1. Nithya

    Nithya Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi frnds,

    I tried to send emails from oracle database(10g) using pl sql with UTL_SMTP as:

    Code (SQL):
    DECLARE
      v_From      VARCHAR2(80) := 'oracle@mycompany.com';
      v_Recipient VARCHAR2(80) := 'test@mycompany.com';
      v_Subject   VARCHAR2(80) := 'test subject';
      v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';
      v_Mail_Conn utl_smtp.Connection;
      crlf        VARCHAR2(2)  := chr(13)||chr(10);
    BEGIN
     v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
     utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
     utl_smtp.Mail(v_Mail_Conn, v_From);
     utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
     utl_smtp.DATA(v_Mail_Conn,
       'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
       'From: '   || v_From || crlf ||
       'Subject: '|| v_Subject || crlf ||
       'To: '     || v_Recipient || crlf ||
       crlf ||
       'some message text'|| crlf ||    -- Message body
       'more message text'|| crlf
     );
     utl_smtp.Quit(v_mail_conn);
    END;

    but i got a lot of error messages of kind " identifier UTL_SMTP must be declared".

    can any1 help me in it.
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    You may need to grant execute on sys.utl_smtp to the user account you are using. You can consider creating a public synonym utl_smtp for sys.utl_smtp. That may resolve the issue.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The PUBLIC synonym for UTL_SMTP already exiss in the database as it's created at package installation; the issue here is one of privileges. As previously stated you will need to connect to the database as SYS and grant execute on UTL_SMTP to your personal user account (or the user account attempting to use the UTL_SMTP package).