Using UTL_MAIL in Oracle
Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Using UTL_MAIL in Oracle

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Oracle introduced the utl_mail package, which provides a simpler and more intuitive email API.  The package is loaded by running the following scripts as the SYS user.

 

CONN sys/password AS SYSDBA

@$ORACLE_HOME/rdbms/admin/utlmail.sql

@$ORACLE_HOME/rdbms/admin/prvtmail.plb

GRANT EXECUTE ON UTL_MAIL TO test_user;

 

Before the package can be used, the SMTP gateway must be specified by setting the smtp_out_server parameter.  The parameter is dynamic, but the instance must be restarted before an email can be sent.

 

CONN sys/password AS SYSDBA

ALTER SYSTEM SET smtp_out_server='smtp.mycompany.com';

SHUTDOWN IMMEDIATE

STARTUP

 

With the configuration complete, it is now possible to send an email using the send procedure.

 

BEGIN

  UTL_MAIL.send(sender     => 'me@mycompany.com',

                recipients => 'you@mycompany.com',

                subject    => 'Test UTL_MAIL.SEND Procedure',

                message    => 'If you are reading this it worked!');

END;

/

 

As with the utl_smtp example, the code related to the job needs to be placed into a database procedure which captures errors using an exception handler and sends the appropriate email.  The following procedure is the Oracle equivalent of the one used in the utl_smtp example.

automated_email_alert_10g.sql

 

CREATE OR REPLACE PROCEDURE automated_email_alert_10g AS

  l_mail_host  VARCHAR2(50) := 'smtp.mycompany.com';

  l_from       VARCHAR2(50) := 'jobs@mycompany.com';

  l_to         VARCHAR2(50) := 'tim@mycompany.com';

BEGIN

  DBMS_STATS.gather_database_stats(cascade => TRUE,

                                   options => 'GATHER AUTO');

 

  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 automated_email_alert_10g;

/

SHOW ERRORS

 

Next, a mechanism for running operating system commands and scripts from within PL/SQL will be introduced.

 

If combining these techniques with the error logging method described previously, one may wish to send additional information in the email (prefix, start and end timestamps) to help pinpoint the errors in the error_logs table

Monitoring Oracle Job Execution

This section will introduce how Oracle jobs can be monitored using both database views and Oracle Enterprise Manager (OEM).  The scheduler available in Oracle is radically different from the one available in previous versions of Oracle.  For this reason, it will be dealt with separately. Explanations of the dbms_application_info and dbms_system packages are included as they can simplify the identification and monitoring of sessions related to scheduled jobs.

 

The list of currently scheduled jobs is displayed using the dba_scheduler_jobs view. This view provides a list of job names and the basic schedule information.  The scheduled_jobs.sql script shows how this view is used.

 

scheduled_jobs.sql

 

set linesize 200

 

column owner format a15

column next_run_date format a25

 

select

   job_name,

   owner,

   nvl(to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS'), schedule_name) as next_run_date,

   to_char(last_start_date, 'DD-MON-YYYY HH24:MI:SS') as last_run_date,

   job_class,

   run_count

from

   dba_scheduler_jobs

;

 

The output generated from the scheduled_jobs.sql script is shown below.

 

SQL> @scheduled_jobs

 

JOB_NAME                       OWNER           NEXT_RUN_DATE             LAST_RUN_DATE        JOB_CLASS                       RUN_COUNT

------------------------------ --------------- ------------------------- -------------------- ------------------------------ ----------

GATHER_STATS_JOB               SYS            MAINTENANCE_WINDOW_GROUP       24-JUN-2004 08:09:39 AUTO_TASKS_JOB_CLASS        4

PURGE_LOG                      SYS            24-JUN-2004 03:00:00      23-JUN-2004 03:00:01 DEFAULT_JOB_CLASS          19

TEST_FULL_JOB_DEFINITION       JOB_USER        24-JUN-2004 08:52:00      24-JUN-2004 08:52:00 DEFAULT_JOB_CLASS         281

TEST_PROG_SCHED_CLASS_JOB_DEF   JOB_USER        24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 TEST_JOB_CLASS             16

ARGUMENT_JOB_DEFINITION       JOB_USER        24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 DEFAULT_JOB_CLASS          16

TEST_SCHED_JOB_DEFINITION      JOB_USER        24-JUN-2004 09:00:16      24-JUN-2004 08:09:37 DEFAULT_JOB_CLASS          16

TEST_PROG_JOB_DEFINITION       JOB_USER        24-JUN-2004 09:00:09      24-JUN-2004 08:09:38 DEFAULT_JOB_CLASS          16

TEST_PROG_SCHED_JOB_DEFINITION   JOB_USER        24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 TEST_JOB_CLASS             16

 

This information is also available from OEM on the Scheduler Jobs (Scheduled) page (Administration > Jobs) shown in Figure 11.28.

 

Figure 11.28 - OEM DB Control: Scheduler Jobs (Scheduled)

 

The dba_scheduler_running_jobs view is the real starting point for job monitoring as it displays a list of the currently running jobs.  Using this view, the user is able to identify the session that is actually executing the job, thereby giving the ability to monitor session level information. 

 

The scheduled_jobs_running.sql script uses this view to identify the currently running jobs.  The extract function is used to retrieve the elapsed time in seconds from the interval returned by the view.

 

scheduled_jobs_running.sql

 

set linesize 200

column owner format a15

column next_run_date format a20

 

select

   rj.job_name,

   rj.owner,

   to_char(j.next_run_date, 'DD-MON-YYYY HH24:MI:SS') as next_run_date,

   extract(second from rj.elapsed_time) as elapsed_time,

   rj.cpu_used,

   rj.session_id,

   rj.resource_consumer_group,

   j.run_count

from

   dba_scheduler_running_jobs rj,

   dba_scheduler_jobs j

where

   rj.job_name = j.job_name

order by

   rj.job_name

;

 

The output generated from the scheduled_jobs_running.sql script is shown below.

 

SQL> @scheduled_jobs_running

 

JOB_NAME                       OWNER           NEXT_RUN_DATE        ELAPSED_TIME   CPU_USED SESSION_ID RESOURCE_CONSUMER_GROUP           RUN_COUNT

------------------------------ --------------- -------------------- ------------ ----------

TEST_FULL_JOB_DEFINITION       JOB_USER        24-JUN-2004 09:22:00        20.69          0        272                                         296

 

This information is also available from OEM on the Scheduler Jobs (Running) page (Administration > Jobs) shown in Figure 11.29.

 

Figure 11.29 - OEM DB Control: Scheduler Jobs (Running)


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.