CODEWITHSUNDEEP
sqloracle-databaseoracle12c
raju
raju

Reputation: 209

sending email from oracle 12c proc

i am trying to send emails from oracle 12c from a stored procedure ,but i get some errors when i compile it.

create or replace PROCEDURE email_alert as

  l_mail_host  VARCHAR2(50) := 'smtp.companyname.com';
  l_from       VARCHAR2(50) := 'email';
  l_to         VARCHAR2(50) := 'email';
BEGIN
  UTL_MAIL.send(sender     => l_from,
                recipients => l_to,
                subject    => 'AUTOMATED_EMAIL_ALERT_10G (MYSID): Success',
                message    => 'AUTOMATED_EMAIL_ALERT_10G (MYSID) completed
successfully!');
EXCEPTION
  WHEN OTHERS THEN
    UTL_MAIL.send(sender     => l_from,
                  recipients => l_to,
                  subject    => 'AUTOMATED_EMAIL_ALERT_10G (MYSID): Error',
                  message    => 'AUTOMATED_EMAIL_ALERT_10G (MYSID) failed with the
following error:' || SQLERRM);
END email_alert;

when i complie the above procedure it gives error as 

: PLS-00201: identifier 'UTL_MAIL.SEND' must be declared. what am i missing here?

Upvotes: 0

Views: 7338

Answers (1)

Ahamed
Ahamed

Reputation: 312

You have install UTL_MAIL Package in your database. Follow the below steps. Hope this helps you.

  • Go to your Oracle Home directory and navigate to the path rdbms > admin. For example C:\ProgramFiles\OracleXE\app\oracle\product\11.2.0\server\rdbms\admin
  • Connect to SQLPLUS from that path and run @utlmail.sql and @prvtmail.plb packages
  • After creating the packages, grant execute permission as shown below if required to the DB user GRANT EXECUTE ON UTL_MAIL TO <DB_USER>;

  • Make sure ACL is created for the host if not create as shown below (ACL creation code for 12c or above) and retry your procedure again.Provide host and principal_name in the below code block.Lower and Upper Ports if required. Default port for Mail server is 25.

    begin dbms_network_acl_admin.append_host_ace( host=>'<example.com>', lower_port=>25, --port if required or NULL upper_port=>25, --port if required or NULL ace=>xs$ace_type(privilege_list =>xs$name_list('connect','resolve'), principal_name=>'<DB_USER>', principal_type =>xs_acl.ptype_db)); END;

Upvotes: 4

Related Questions