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
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.
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
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
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.
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
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
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.
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
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
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
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...!!!
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.
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?
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
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.
May 24, 2010 - 12:46 pm UTC
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) .
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;
/
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
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
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.
March 01, 2012 - 1:00 pm UTC
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
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..!!
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?
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.
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
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) .