Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Munzer.

Asked: January 06, 2002 - 1:47 pm UTC

Last updated: May 06, 2021 - 5:00 am UTC

Version: 8i

Viewed 50K+ times! This question is

You Asked

Tom:

1. If you want to write something in PL/SQL application server that send email automatically to an administrator requesting approval after a user create a record, which oracle package would be using.

Would you also use an after insert trigger for it or just include it in the insert routine of the triggering record?

Thank you,

and Tom said...

search this site for

UTL_SMTP

it does that.


I myself (as documented in my book if you have it) would use DBMS_JOB to schedule a call to a stored procedure that will send the mail right after I commit. The reason for doing this is twofold:

o sending mail is slow, it takes a bit of time.
o sending mail is not transactional. if you send the email in the trigger and then ROLLBACK the transaction -- the email won't get "unsent"

Using DBMS_JOB solves both of those issues. scheduling the job in the trigger is very fast (the end user is not affected) and if you rollback, the scheduled job is "unscheduled" as well.

Rating

  (38 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More please

A reader, January 07, 2002 - 9:34 am UTC

Hi, Tom,

Would you give us an example on how to schedule dbms_job
in a trigger?

Thanks

Tom Kyte
January 07, 2002 - 12:28 pm UTC

create trigger trig
after insert on tab for each row
declare
l_job number;
begin
dbms_job.submit( l_job, 'proc;' );
end;
/

In my book -- I have tons of material on this. Additionally, the supplied packages guide shows you how to use this documented package.

notification email

John, December 10, 2002 - 3:07 pm UTC

Hi Tom,

I have a table as follow

Table: work_to_do
Columns: work_id, what, work_time.

I need to write a background procedure which sends a notification email whenever the system time reach one hour before the "work time" for any rows in the table. The table is not static, any user may add/modify/delete rows in the table.

Any solution?

Thanks in advance.

Tom Kyte
December 10, 2002 - 8:36 pm UTC

You will either schedule a job via dbms_job to run every N minutes looking for notifications to send -- or you can schedule a job when they insert/update/delete the record.

both need another column to be added -- either a "flag" saying the email was sent or a "job" so we know what job is scheduled.

So, you could write a procedure:

procedure p
is
begin
for x in ( select rowid rid, work_to_do.*
from work_to_do
where work_time <= sysdate+1/24
and flag = 'N'
for update )
loop
send mail...
update work_to_do set flag = 'Y' where rowid = x.rid;
end loop;
end;


and schedule that 5 or 10 minutes

dbms_job.submit( :n, 'p;', sysdate, 'sysdate+5/24/60' )


Or, you could use a trigger on the table to schedule a JOB to be run upon insert, modify the job's when upon update of the time, remove the job upon delete. for this you would need to add a "job" column that the insert trigger would fill in and the update/delete would use.




A dbms_job question

Scott, February 01, 2004 - 5:24 am UTC

Hi Tom

I'm having an interesting problem with sending email from Oracle.

I have implemented a package that sends emails along the lines of what you have presented on this site. It uses dbms_job to send the messages in the background, sourcing the content of the email from a table.

The interesting part is if I manually run the send procedure the email is sent exactly as intended. If however the dbms_job queue executes the same procedure the email shows up with content along the lines of:

sender: from@example.com
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

Any ideas you have on why the procedure sends correctly from the sqlplus prompt, but not from dbms_job would be most appreciated.

An extract of the code I'm using is:

CREATE TABLE email_message
(job_id NUMBER,
recipient VARCHAR2(200),
sender VARCHAR2(200),
message CLOB);

CREATE OR REPLACE PACKAGE BODY pkg_email
PROCEDURE prc_send
(p_job_id IN email_message.job_id%TYPE)
IS

-- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;
-- variable to hold the contents of the email message
v_email_message email_message%ROWTYPE;
-- variable to hold the smtp host name
v_smtp_host VARCHAR2(100) DEFAULT 'SMTP_SERVER';
-- variable to hold the smtp port
v_smtp_port NUMBER DEFAULT 25;

BEGIN

SELECT *
INTO v_email_message
FROM email_message
WHERE job_id = p_job_id;

-- establish the connection to the smtp server
v_smtp_connection := utl_smtp.open_connection(v_smtp_host, v_smtp_port);

-- perform a handshake with the smtp server
utl_smtp.helo(v_smtp_connection, v_smtp_host);

-- set the 'from' address of the message
utl_smtp.mail(v_smtp_connection, v_email_message.sender);

-- add the recipient to the message
utl_smtp.rcpt(v_smtp_connection, v_email_message.recipient);

-- open the message for writing
utl_smtp.open_data(v_smtp_connection);

-- populate the message with the build up message header and body
utl_smtp.write_data(v_smtp_connection, v_email_message.message);

-- close the message for writing
utl_smtp.close_data(v_smtp_connection);

-- end the connection to the smtp server
utl_smtp.quit(v_smtp_connection);

END prc_send;
END pkg_email;

Thanks
Scott

Tom Kyte
February 01, 2004 - 1:25 pm UTC

one would need to see the data being sent but it looks as if it is a character set issue.

You might want to put a little debug in your code to see exactly what is happening.

Follow up

Scott, February 01, 2004 - 4:37 pm UTC

Ok, when sending manually this is what is sent:

<start of message>
MIME-Version: 1.0
Date: 01 Feb 2004 20:36:52
From: from@sample.com
To: to@sample.com
Subject: Test email subject
X-Priority: 3
Reply-To: reply-to@sample.com
Content-TYPE: multipart/alternative; boundary=D7D34C02C9684FC284D4E95CF9630699
--D7D34C02C9684FC284D4E95CF9630699
Content-Type: text/plain; charset=us-ASCII
message blah blah blah
--D7D34C02C9684FC284D4E95CF9630699
Content-Type: text/html;

<html><body><table><tr><td style="background-color: #ff0000;">hello</td><td style="background-color: #0000ff;">hello</td></tr></table></body></html>

--D7D34C02C9684FC284D4E95CF9630699--

<end of message>

However when sending via dbms_job (executes the sample command) it sends:

<start of message>
??????›???????????????????????????????›???????????????›???????????????›?????????????????????????›?????????????????????????????????????????????????????????›???????????????????????????????????????????????????›????????????????????????????????›????????????????????????????????????????????????????????????????????
<end of message>

My database is using the AL32UTF8 character set.

Thanks again
Scott

Tom Kyte
February 01, 2004 - 7:31 pm UTC

Nope, I sort of want to see something like:

alter system set job_queue_processes=0;
insert into table ...;
commit;

select job, what from user_jobs;
exec dbms_job.run( NNNN );
(run that job)


versus

alter system set job_queue_processes=1;
insert into table....;
commit;
(and letting the job run itself).


You do know that all email is sent as 7bit ascii by default -- this is looking seriously like "character set" related. You'd have to use the "raw" interface and make sure to include a character set in the email as per the smtp way of doing things to send multi-byte or even 8 bit characters.


error while sending mail to other accounts

Venu, September 16, 2005 - 5:22 am UTC

Hi Tom,

I have implemented a package that sends emails along the lines of what you have presented on this site. Oracle version is 9.2.0.4 on linux AS 3.0

The procedure is working fine when i am sending the mail to our internal mail account. But when i try to send the mail to yahoo, hotmail or any other account getting this error.

ERROR at line 1:
ORA-29279: SMTP permanent error: 550 not local host gmail.com, not a gateway
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 240
ORA-06512: at "SCOTT.MAIL_PKG", line 16
ORA-06512: at "SCOTT.MAIL_PKG", line 57
ORA-06512: at line 2

Please advice.

Tom Kyte
September 16, 2005 - 8:24 am UTC

talk to your network admins and ask them what smtp server you should be using to send mail.

sending mail through dbms_job

ravi, December 02, 2005 - 12:30 pm UTC

This is the way i submetted a job
begin
dbms_job.submit(:jobno,'lock_table.main_lock_table;',sysdate,'sysdate+3/1440');
commit;
end;
/

lock_table.main_lock_table is a procedure send the mail.

I am getting mail when i execute the procedure like
exec lock_table.main_lock_table;

and also getting mail when i manually run like

SQL> exec dbms_job.run(65);

PL/SQL procedure successfully completed.


-----
Is there any idea. or do you need more information?
Thanks in advance
 

Tom Kyte
December 03, 2005 - 9:50 am UTC

yeah, the information I'd need to know:

what exactly is the question?

mail using dba_job

ravi, December 02, 2005 - 12:43 pm UTC

This is the way i submetted a job
begin
dbms_job.submit(:jobno,'lock_table.main_lock_table;',sysdate,'sysdate+3/1440');
commit;
end;
/

lock_table.main_lock_table is a procedure send the mail.


I am getting mail when i execute the procedure like
exec lock_table.main_lock_table;

and also getting mail when i manually run like

SQL> exec dbms_job.run(65);

PL/SQL procedure successfully completed.


-----
But i am not getting mails when i submit the job. and there is errors too
................
Is there any idea. or do you need more information?
Thanks in advance 

timeout

Dani, February 04, 2007 - 5:10 pm UTC

When working with utl_smtp.open_connection, sometimes our smtp server is busy and not available, so the above command returns
421 Service not available which is ok. But the propblem is that this is returned in 60-70 secs waiting. Now we do not want that, we even tried to pas argumen for timeout paramter as 10 secs but that does not even happen, guess timeout value for open connection is useless.

Anyway we have a serious issue where what one need is that utl_smtp.open-connection should time out within10-15 secs if smtp server is not available. But it takes 60-70 secs. Now this produced a wait even in 10g for every session (i forgot the name of wait) that causes load on the db server in terms of latches.
So what is the solution? You should try tom at your test database and see that the timeout value is really meaningless and not working when you supply conn=utl_smtp.open_connection(smtphost,port,15)

cheers

Tom Kyte
February 05, 2007 - 7:37 am UTC

I never send email "in real time", it is way too slow under normal circumstances.

I queue it as a job using dbms_job (or dbms_scheduler). That way you can

a) control the number of people hitting the smtp server at the same time
b) not really care if it takes 1 second or 10 seconds or more, no one is really waiting
c) have the email eventually get sent without the end user having to hit reload over and over.

email program

Arthuro Olazo, May 29, 2008 - 11:50 pm UTC

Hi sir,
how can i send email outside using oracle program example sending email on yahoo account.

Tom Kyte
May 30, 2008 - 6:59 am UTC

email is typically sent using simple mail transfer protocol SMTP (eg: yahoo receives and transmits emails for it's users via SMTP). You are not really "sending email on yahoo account", you used SMTP to move the bytes around the network.


there is a UTL_SMTP package (9i and up)
there is a UTL_MAIL package (10g and up)

either one can be used to send email from the database, as written about in the original question and answer on this site.

To "send" email from your yahoo account, the sender would just be your_name@yahoo.com

The mail would appear to be from you at yahoo when the recipient looks at it.

Processing alert log ORA- : Sending mail from within a trigger in 10g

Shubha, October 14, 2008 - 8:17 am UTC

Hi Tom,

I am getting all alert log messages into an external table and have created a view to show only ORA- mesages as per date and timestamp. I would like to send email to myself upon every ORA- received into the view. So, I have designed an instead of trigger on the view which calls utl_mail.send to notify me for each INSERT of ORA- row in the view. My plsql code for trigger doesn't have any error and also if I execute utl_mail.send separately from an anonymous block, it works and sends mail as well. But this mailing is not happening at all when a new ROW is inserted into the view.
Also, the smtp_out_server is set properly.

Following is my code for Trigger:

CREATE OR REPLACE TRIGGER dummy_dba.send_mail_on_ora
   INSTEAD OF INSERT
   ON dummy_dba.alert_log_view
   REFERENCING NEW AS NEW
   FOR EACH ROW
BEGIN

   UTL_MAIL.send
      (sender          => 'shubhasree.chaki@kpitcummins.com',
       recipients      => 'shubhasree.chaki@kpitcummins.com',
       cc              => NULL,
       bcc             => NULL,
       subject         => 'ORA- Errors in alert_grphead.log',
       MESSAGE         =>    'Dear DBA, '
                          || CHR (10)
                          || CHR (13)
                          || CHR (10)
                          || CHR (13)
                          || 'Please check alert_log VIEW for ORA- errors raised now.'
                          || CHR (10)
                          || CHR (13)
                          || 'GRPHEAD',
       mime_type       => 'text/plain; charset=us-ascii',
       priority        => 2
      );
EXCEPTION
   WHEN UTL_MAIL.invalid_argument
   THEN
      DBMS_OUTPUT.put_line (' invalid_arg');
   WHEN UTL_MAIL.invalid_priority
   THEN
      DBMS_OUTPUT.put_line ('invalid_prio ');
   WHEN OTHERS
   THEN
      raise_application_error (-20001, SQLERRM);
      DBMS_OUTPUT.put_line (':NEW.alert_text = ' || SQLERRM);
END;


1) Please could you let me know if there is any init.ora parameter that must be affecting this?
2) Can this task be done in any other way apart from mailing from within a trigger?
3) By scheduling a job every regular interval this can be achieved but I feel that would not be a good approach to react faster in case of any serious error logged in alert.log.
4) Is there any restriction on instead of trigger which is preventing this to happen?

Regards,
Shubhasree

Tom Kyte
October 14, 2008 - 6:06 pm UTC

umm, you are not really inserting into this view are you - no one ever does an "insert into alert_log_view" do they.

Rather alert_log_view is a view of an external table. No one "inserts" into this view ever.

You would have to

a) periodically scan this view
b) looking for new records
c) sending yourself an email when you find one
d) log that you did that in a table somewhere so that you can do (b) later (look for new records, in order to find 'new' you have to remember old)

the fact that an OS process wrote to a file will not generate an "insert" into a view.

email

A reader, October 14, 2008 - 8:09 pm UTC


Alert log monitoring and mailing ORA- errors

Shubha, October 16, 2008 - 5:57 am UTC

Yes Tom,

I was wrong in writing that I'm inserting into the view. What I meant is the view will automatically get the records when the external table is updated by the elert log.
Well, I am doing it in a better way now:

1) Getting the entire logs into external table
2) Having a view to get only the ORA- records along with their actual timestamp in the view (which will be helpful for me to check what time the error has occured)
3) from that view, I am (truncating and) loading 2 tables: one of them (table1) will hold all the previous copy of alert log ORA- messages and the other (table2) will hold the latest copy (i.e. previous + latest changes directly from the view). And every 10min, a procedure will run to subtract the records as table2 - table1. This procedure will leave only latest ORA messges into Table2 along with their timestamp.
4) And finally decoding the changes from Table2 and mailing the DBA to check alert log for error msg and act upon.

As of now, this seems to work fine.

Please let me know what you feel about this...

Regards,
Shubha

utl_mail ORA-29279 Access denied

Dante, May 14, 2009 - 4:26 pm UTC

Oracle: 10204
OS: SunOS 5.10
Tom...
Here is my procedure to send email from pl/sql that gives me an error that I am not being able to figure out..
mailx and telnet (with port 25) works just fine

create procedure send_email 
(
   p_sender      VARCHAR2,
   p_recipients  VARCHAR2,
   p_cc          VARCHAR2 := NULL,
   p_bcc         VARCHAR2 := NULL,
   p_subject     VARCHAR2 := NULL,
   p_message     VARCHAR2,
   p_mime_type   VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
   p_priority    PLS_INTEGER DEFAULT NULL
)
as
v_hostname varchar2(30); 

begin
select host_name into v_hostname from v$instance;
--setting smtp_out_server (this parameter has to be set for emails to go out)
execute immediate 'alter session set smtp_out_server=''' || v_hostname || '.something.com''';
utl_mail.send (sender => p_sender,
recipients => p_recipients,
cc => p_cc,
bcc => p_bcc,
subject => p_subject,
message => p_message,
mime_type => p_mime_type,
priority => p_priority);
end;
/

SQL> alter session set smtp_out_server='hostname.something.com';

Session altered.

SYS:dbaslp1:SQL> exec mail_pkg.send_email (p_sender => 'test', p_recipients => 'some_name@somewhere.com',p_subject => 'testing subject',p_message=>'Message');
BEGIN mail_pkg.send_email (p_sender => 'test', p_recipients => 'some_name@somewhere.com',p_subject => 'testing subject',p_message=>'Message'); END;

*
ERROR at line 1:
ORA-29279: SMTP permanent error: 550 5.0.0 Access denied
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at "SYS.UTL_MAIL", line 397
ORA-06512: at "SYS.UTL_MAIL", line 608
ORA-06512: at "MAIL_PKG", line 27
ORA-06512: at line 1



Tom Kyte
May 14, 2009 - 5:45 pm UTC

prove it, cut and paste it all.

seriously, we just report the errors

mail_pkg is some code you have, I don't have it, reproduce with a standalone - small - simple - tiny - bare bones - plsql block of code.

utl_mail ORA-29279 Access denied

Dante, May 14, 2009 - 6:38 pm UTC

mail_pkg has just one procedure (send_email) that I posted ..which basically is a wrapper around the standard utl_mail package
Tom Kyte
May 15, 2009 - 1:09 pm UTC

I do not look up and down in the page, I look only at the current review - if you post what I've asked for, I'll take a look.

I want a hard coded example - simple, small, 100% complete and a total cut and paste of your telnet session - showing the hostnames and all.

A reader, May 14, 2009 - 9:17 pm UTC

Check with your Exchange / Mail Administrator for the correct
SMTP Address esp PORT .


The error is nothing to do with utl_mail package

How can we read email content from PL/SQL..??

Pavan Reddy, June 02, 2009 - 5:42 am UTC

How can I read email content from PL/SQL..?? Is it possible through Oracle..if possible ..can you please guide me through the process...!!!
Tom Kyte
June 02, 2009 - 7:46 am UTC

you would typically load a java jar file into the database (the java mail api for example) as implementing the various email protocols - POP, IMAP - can be very tedious.

you can do it in plsql (utl_tcp would let you do it), but you'd have to read about the protocols (POP and IMAP) and then implement them.

I suggest getting a java programmer to do this particular feat.

Schedule - Run - Send email

Daniel, July 18, 2009 - 6:51 am UTC

hi,

Almost what we discused in this is i want.
I want to send a report in excel/text every day to one user by email.
Please send me a clear procedure.
Tom Kyte
July 24, 2009 - 9:24 am UTC

well, I've showed you how to send email...
you know how to create your report....

you need to read about dbms_job

and then you are done..

external file as attachment

dev, August 13, 2009 - 8:57 pm UTC

What is the easiest way to send the external file as email attachment?
Tom Kyte
August 24, 2009 - 7:23 am UTC

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_mail.htm

just like I posted in the OTHER places you've asked this identical question

utl_mail grants

Anne, March 26, 2010 - 2:06 pm UTC

Hi Tom,

Could you please help me with this issue:

I have compiled utl_mail as Sys, and I am able to send emails to myself using utl_mail as Sys.

I would like other users to use this functionality and hence grant execute permisssion to utl_mail package.

As sys :
*********
SQL> desc utl_mail;
PROCEDURE SEND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
PROCEDURE SEND_ATTACH_RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
ATTACHMENT RAW IN
ATT_INLINE BOOLEAN IN DEFAULT
ATT_MIME_TYPE VARCHAR2 IN DEFAULT
ATT_FILENAME VARCHAR2 IN DEFAULT
PROCEDURE SEND_ATTACH_VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
ATTACHMENT VARCHAR2 IN
ATT_INLINE BOOLEAN IN DEFAULT
ATT_MIME_TYPE VARCHAR2 IN DEFAULT
ATT_FILENAME VARCHAR2 IN DEFAULT

Also:
SQL> SQL>
select owner, object_name
from dba_objects
where owner = 'PUBLIC'
and object_name like'UTL_MAIL%'
;

SQL> 2 3 4 5
OWNER OBJECT_NAME
------------------------------ -------------------------------------------

PUBLIC UTL_MAIL


SQL> SQL>

SQL> grant execute on utl_mail to system with grant option;





As system :
***********
SQL> desc utl_mail;
ERROR:
ORA-04043: object utl_mail does not exist


SQL>
SQL> desc sys.utl_mail
ERROR:
ORA-04043: object sys.utl_mail does not exist


How can I have system "see" utl_mail ?

Thanks so much,
Anne
Tom Kyte
March 26, 2010 - 3:50 pm UTC

can you do this as a single cut and paste please, showing everything from start to finish. this is not the way it works, something else is wrong here - a complete cut and past like this below will help us see what.

system%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> show user
USER is "SYS"
sys%ORA10GR2> desc utl_mail
PROCEDURE SEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
PROCEDURE SEND_ATTACH_RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
 ATTACHMENT                     RAW                     IN
 ATT_INLINE                     BOOLEAN                 IN     DEFAULT
 ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT
 ATT_FILENAME                   VARCHAR2                IN     DEFAULT
PROCEDURE SEND_ATTACH_VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
 ATTACHMENT                     VARCHAR2                IN
 ATT_INLINE                     BOOLEAN                 IN     DEFAULT
 ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT
 ATT_FILENAME                   VARCHAR2                IN     DEFAULT

sys%ORA10GR2> select owner, object_name
  2    from dba_objects
  3  where owner = 'PUBLIC'
  4  and object_name = 'UTL_MAIL';

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
PUBLIC                         UTL_MAIL

sys%ORA10GR2> grant execute on utl_mail to system;

Grant succeeded.

sys%ORA10GR2> connect system
Enter password: *******
Connected.
system%ORA10GR2> desc utl_mail
PROCEDURE SEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
PROCEDURE SEND_ATTACH_RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
 ATTACHMENT                     RAW                     IN
 ATT_INLINE                     BOOLEAN                 IN     DEFAULT
 ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT
 ATT_FILENAME                   VARCHAR2                IN     DEFAULT
PROCEDURE SEND_ATTACH_VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
 ATTACHMENT                     VARCHAR2                IN
 ATT_INLINE                     BOOLEAN                 IN     DEFAULT
 ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT
 ATT_FILENAME                   VARCHAR2                IN     DEFAULT


utl_mail issue

Anne, March 29, 2010 - 11:04 am UTC

I figured it out - I was as "SYS" in one database and as "System" in another database - no wonder it had no effect. Did not realize that the default ORACLE_SID was changed!

Sorry for taking your time. I've got it working now.

Thanks,
Anne

hi tom ,

ahmed wageh, April 06, 2010 - 11:30 pm UTC

i want to use trigger to sets the time work in database to sequrety

sending emails

smitha, May 22, 2010 - 12:03 am UTC

.
Hi Tom,

Below is an issue which i am facing , In QA , its sending mails but not in Production


Server - QA


create or replace procedure p_name_qa

is

begin

var :=

'insert into t_credit'
||'values('Laxman','Venu','Dimol')';
commit;


/* procedure with utl_smtp package to send emails */

procedure_to_send_email
('badiga.one@host.com',
'subject');


exception
when others then
null;

end;

EXECUTE p_name_qa;

>>>>>>>>>>>Email Recieved

Server - Production

Same procedure replicate as above

Execute p_name_prod;


>>>>>>>>>> Not sending Emails

Thank you.





































Tom Kyte
May 24, 2010 - 12:46 pm UTC

exception
when others then
null;

end;



http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22


remove that, right now.

using blob in email formatting

Ambika, September 07, 2010 - 8:06 am UTC

hi
I need to set the background image mails i am sending through pl/sql. Images are stored as blob in database tables.
can u please help me how to get that image in html formatting of e-mail?
thanks in advance.

Email from 11gr2

mfz, September 13, 2010 - 2:04 pm UTC

I am trying to send an email from Oracle 11g r2 . When I try the following command ( as SYS) , it works fine .
( I logged in as SYS , just to test ....I understand it is not good practice) .

But , as a non-SYS user , I get the following error ...


ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 1

The user has been granted execute privilges on the following packages .


Grantor Table_Name Privilege Gra
------------------------------ ------------------------------ ---------------------------------------- ---
SYS UTL_HTTP EXECUTE No
SYS UTL_INADDR EXECUTE No
SYS UTL_MAIL EXECUTE No
SYS UTL_SMTP EXECUTE No
SYS UTL_TCP EXECUTE No


SQL> Select * from product_component_version;

PRODUCT VERSION STATUS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------
NLSRTL 11.2.0.1.0 Production
Oracle Database 11g Enterprise Edition 11.2.0.1.0 64bit Production
PL/SQL 11.2.0.1.0 Production
TNS for 64-bit Windows: 11.2.0.1.0 Production


smtp is confiured correctly ( as the email works for SYS) .
Tom Kyte
September 13, 2010 - 2:57 pm UTC

$ oerr ora 24247
24247, 00000, "network access denied by access control list (ACL)"
// *Cause:    No access control list (ACL) has been assigned to the target
//            host or the privilege necessary to access the target host has not
//            been granted to the user in the access control list.
// *Action:   Ensure that an access control list (ACL) has been assigned to
//            the target host and the privilege necessary to access the target
//            host has been granted to the user.


http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e16760/u_mail.htm#ARPLS71198

has a link to:

"Managing Fine-grained Access to External Network Services " in the security section right at the top that explains the package.

that link is:

http://docs.oracle.com/docs/cd/E11882_01/network.112/e16543/authorization.htm#DBSEG40012

and it goes through the setup.

email from pl/sql

Samuel, September 22, 2010 - 2:26 pm UTC


I am pasting YOUR sql script below that is by the way very useful. I need help to modify this script to send an email within pl/sql code only if LAST_CALL_ET > 120. Thanks.

set pages 200

column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status
from v$session
where username = 'APPL'
and status = 'ACTIVE'
/

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;

end loop;
end loop;
end;
/
Tom Kyte
September 23, 2010 - 10:43 am UTC

add a where clause "and last_call_et > 120" to the query (that should be obvious)

turn the dbms_output calls into a call to the mail package. done.


show image as a message body

s.a, November 24, 2010 - 8:42 am UTC

Hi,

I am trying to send email from pl-sql code. I want to send an image(jpeg file) but not like as an attachment,i want to show jpg as message body.
I have written following procedure, it works but it cant show image, i think the path_name could not be read.
is it possibel to show, please can you help?

CREATE OR REPLACE PROCEDURE APPS.xxtt_send_mail_im(
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN CLOB)
as
l_mailhost VARCHAR2(255) := '172.1.1.7';
l_mail_conn utl_smtp.connection;
v_add_src VARCHAR2(10000);
v_addr VARCHAR2(10000);
slen number := 1;
l_message clob;
crlf VARCHAR2(2) := chr(13)||chr(10);
i NUMBER(12);
len NUMBER (12);
part NUMBER(12) := 16384;
l_stylesheet CLOB := '<html><head>
<style type="text/css">
body { font-family : Verdana, Arial;
font-size : 8pt;}

.green { color : #00AA00;
font-weight : bold;}

.red { color : #FF0000;
font-weight : bold;}

pre { margin-left : 10px;}

table { empty-cells : show;
border-collapse : collapse;
width : 100%;
border : solid 2px #444444;}

td { border : solid 1px #444444;
font-size : 8pt;
padding : 1px;}

th { background : #EEEEEE;
border : solid 1px #444444;
font-size : 8pt;
padding : 2px;}

dt { font-weight : bold; }
</style>
</head>
<body>
<img src="BANKALAR/HALKBANK/GelenSenetDosyalari/KUTLAMA.JPG"/>';
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, p_sender);
if(instr(p_recipient,';') = 0) then
utl_smtp.rcpt(l_mail_conn, p_recipient);
else
v_add_src := p_recipient || ';';
while(instr(v_add_src,';',slen) > 0) loop
v_addr := substr(v_add_src, slen, instr(substr(v_add_src, slen),';')-1);
slen := slen+instr(substr(v_add_src, slen),';');
Dbms_Output.put_line('rcpt ' || v_addr);
utl_smtp.rcpt(l_mail_conn, v_addr);
end loop;

end if;
--utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
utl_smtp.write_data(l_mail_conn,
'MIME-version: 1.0' || crlf ||
'Content-Type: text/html; charset=ISO-8859-9 ' || crlf || -- charset=ISO-8859-9
'Content-Transfer-Encoding: 8bit' || crlf ||
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || p_sender || crlf ||
'Subject: '|| p_subject || crlf ||
'To: ' || p_recipient || crlf);
utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(l_stylesheet));

i := 1;
len := DBMS_LOB.getLength(p_message);
WHILE (i < len) LOOP
utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(DBMS_LOB.SubStr(p_message,part, i)));
i := i + part;
END LOOP;
utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('</body></html>'));
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end;

Thanks.


A reader, February 21, 2012 - 11:02 pm UTC

Hi Tom,

i want a trigger which has to send email whenever if any user insert,update,delete on xxx table.

please help me out. Thanks

Regards,
john
Tom Kyte
February 23, 2012 - 12:42 am UTC

No you don't!!!

what happens if you rollback? SMTP won't rollback.

What happens when the statement restarts?
http://asktom.oracle.com/Misc/that-old-restart-problem-again.html

You want to use utl-mail (to send mail) from a procedure that runs AFTER you commit (dbms_job)


So, write a procedure that sends your email (that is simple, just read the documentation on utl_mail).

Then schedule that procedure using dbms_job.submit. Something like this:

create table my_job_table( job_id number primary key, data for email procedure...);

create procedure send_my_email( P_JOB in number )
as
   l_rec my_job_table%rowtype;
begin
   select * into L-rec from my_job_table where job_id = P_JOB;

   utl_mail..... using the contents of l_rec to populate email body.....;

   delete from my_job_table where job_id = P_JOB;
end;
/


create trigger my_trigger after insert or update or delete on XXX
declare
   l_job number;
begin
   dbms_job.submit( l_job, 'send_my_email(JOB);' );
   insert into my_job_table( job_id, ..... )
   values ( l_job, .... );
end;
/




that is all you want to do.... do the job the way I did above - with the TABLE to pass parameters, it is important (it makes it bind friendly!!!)

Raj, February 23, 2012 - 12:58 am UTC

Tom,

Thanks for the inputs...

I am kid in ORACLE as of now....i know that utl_smpt is used till 9i and from 10g we need to user UTL_MAIL.

But i am not sure how to use it.

can i have complete picture with an example, appreciate if i can have sample output of it,

Please Tom dont mind it , its very urgent for me..Thanks in advance
Tom Kyte
February 23, 2012 - 7:45 pm UTC

search for it on this site, there are many examples? Have you tried the search at all?

I gave you 99.99% of the code you needed - seriously. All you needed to do was to see how to send an email - literally ONE LINE OF CODE - really, just one line of code.

http://www.oracle.com/pls/db112/search?remark=quick_search&word=utl_mail

Sending mails through Oracle procedures

Garima, March 01, 2012 - 9:00 am UTC

Hi Tom,

I need a basic idea of how to send mails through oracle pl/sql procedure. I need to implement it in my procedure.

Thanks you.

mail

A reader, March 02, 2012 - 12:22 am UTC


email notification user expire 11g

Ahmed Adel, March 08, 2013 - 1:06 pm UTC

Hi Tom ,
this is my first question here , can you please tell me
how to send email to users that his password will be expire soon , i use oracle 11g.2 asm with rac 2 nodes , let's say send email to users before 5 days, now my OEM register in mail server and i test the utl_mail it's work but i don't know the pl-sql language , so can you please provide me with example ???

Number of Emails sent through Apex_Mail.Send

Himanshu Gupta, November 23, 2017 - 12:17 pm UTC

Hi Tom,
This information was very helpful.
I've a query in Apex regarding emails:
If I set the limit of number of emails in ORACLE APEX, does that means the actual number of emails or number of email addresses that we can sent out using APEX_MAIL.SEND
Chris Saxon
November 24, 2017 - 8:13 am UTC

And where exactly are you setting this limit?

Sending Mail procedure

sowjanya D N, July 20, 2018 - 6:34 am UTC

How to send mail by using pl/sql by using table as issues and i want to fetch all the columns in issues table like employee_name, issue_status, issue_type, issues.* and in mail it should having the contents of the table issues.. please help me sir..!!
Connor McDonald
July 20, 2018 - 10:43 am UTC

Firstly here is a top to bottom of using UTL_MAIL to send an email

https://asktom.oracle.com/pls/apex/asktom.search?tag=sending-mail-using-utl-mail

Once you've got working, it is just a case of populating the mail body with whatever you want, eg

for i in (
  select ename, issue_status
  from  issues
) loop
  l_body := l_body || i.ename||','||i.status||chr(10);
end loop;


or similar. Populating the body is just simple string concatenation in whatever shape you like.


need to send email notification when value in a view changes to failed

Seyon Rajagopal, August 02, 2020 - 2:28 pm UTC

I need to send an email notification with UTL_SMTP when a row in a view changes to failed. The email should also contain a table of the failed row or any other rows that have the "failed" status. The view is not updated by the user.

What is the best way to do this?
Chris Saxon
August 03, 2020 - 2:25 pm UTC

The view is not updated by the user.

How exactly does the column value change? You need to build the email sending routine into whatever process causes this to change.

utl_mail not working in stored procedure

sandesh, May 04, 2021 - 7:00 am UTC

Dear Sir,

I am fciing an interesting issue here,I have created one stored procedure to fetch information from all_scheduler_job table for jobs running more than 3 hrs and send mail to concern group.I have created a job to execute this procedure every 30 min,Interesting part is when i execute this procedure mail is not getting triggerd but when execute as pl/sql block mail is working,Could you please advice here.
Chris Saxon
May 04, 2021 - 9:38 am UTC

Please share an example of what happens in the (failing) job vs the (working) PL/SQL block

Email using SMTP with SSL authentication.

Zahir, May 04, 2021 - 2:58 pm UTC

I created a wallet and try to use the wallet in opening the SMTP connection ( to office 365) . It errors out as "ORA-28845: No certificate" .
I checked the existence of the certificate using openssl , I get the certificate .

Please advise , what other steps I am missing.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2       c utl_smtp.connection;
  3  BEGIN
  4    c := utl_smtp.open_connection(
  5       host => 'smtp.xxx.xxx',
  6       port => 587,
  7       wallet_path => 'file:/home/oracle/wallet/',
  8       wallet_password => 'walletpasswd123456',
  9       secure_connection_before_smtp => TRUE);
 10       utl_smtp.starttls(c);
 11* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-28845: No certificate
ORA-06512: at "SYS.UTL_TCP", line 63
ORA-06512: at "SYS.UTL_TCP", line 314
ORA-06512: at "SYS.UTL_SMTP", line 177
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at line 4


SQL> ! ls -lrt ~/wallet
total 8
-rw-------. 1 oracle oinstall   0 May  4 10:03 ewallet.p12.lck
-rw-------. 1 oracle oinstall   0 May  4 10:03 cwallet.sso.lck
-rw-------. 1 oracle oinstall 279 May  4 10:05 ewallet.p12
-rw-------. 1 oracle oinstall 324 May  4 10:05 cwallet.sso




Connor McDonald
May 06, 2021 - 5:00 am UTC

Have you followed the instructions in MOS note 1998596.1 for obtaining and loading the correct certs ?

Certs

A reader, May 06, 2021 - 2:53 pm UTC

I followed instructions in 2716386.1

orapki wallet add -wallet /home/oracle/walletsmtp/ -trusted_cert -cert m365_root_certs_20201012.p7b -pwd walletpassword
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

A reader, May 06, 2021 - 2:55 pm UTC

In the previous comment , I added the location of the wallet differently ( not to expose my actual wallet location) .



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library