How to send email from 10g Oracle Database (UTL_MAIL)

Posted on April 24, 2008. Filed under: Configuration | Tags: , , , , , |

Heres a simple solution to send out emails from 10g Database sql prompt.

This solution will be really helpful if the OS utility (mailx, sendmail) is restricted for end users.

Steps to enable Mailing from Database

1. sqlplus ‘/ as sysdba’
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora
alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;
25 = Default SMTP Port

If instance had been started with spfile

eg: alter system set smtp_out_server = ’172.25.90.165:25′ scope=both;

If instance had been started with pfile
alter system set smtp_out_server = ’172.25.90.165:25′;
Also make below entry in your initSID.ora

smtp_out_server = ’172.25.90.165:25′

Thats It, your database is configured to send emails ….

How to send an email

1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘oraclepitstop@wordpress.com’, recipients => ‘oraclepitstop@wordpress.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);
3. Check the inbox of the email id, to verify the email receipt.

To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.

eg: grant execute on utl_mail to apps;

Happy Mailing !!!

cheers,
OraclePitStop.

About these ads

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

14 Responses to “How to send email from 10g Oracle Database (UTL_MAIL)”

RSS Feed for OraclePitStop – Lets share some knowledge Comments RSS Feed

in line number 2, exec utl_file.send should that be exec utl_mail.send??

hey raeqwest,

you are damn right, it should be utl_mail. thanks for pointing that out.

cheers,
OraclePitStop

Hi,

this is the command i am trying to execute

begin
utl_mail.send(‘PROD APPLICATION’, ‘firstlastname@domain.com’, NULL, NULL, ‘TEST’, ‘TEST’);
end;
/

but i keep getting the error:
ERROR at line 1:
ORA-29279: SMTP permanent error: 501 5.5.4 Invalid arguments

because i have a white space in the senders name.

but the situation is, we need to have that white space.

if we have to remove the white space then a lot of changes need to be done enterprise wide which is not feasible at all.

is there any way around this problem.
is there a way i can pass in the white space as a special or ascii character.
if then can you please tell me how.

Thanks in advance.
Philip.

Philip,

Try this way and this worked for me.

exec utl_mail.send(’”RAM P”’, ‘oraclepitstopster@gmail.com’, NULL, NULL, ‘TEST’, ‘TEST’);

The catch here is the double quotes withing the single quotes ‘”RAM P”‘.

I even got the email.

cheers,
Ram.

Can this process be used to automagically send
out a bunch of emails from the result of a query?

Hi kldavis,

I guess you can.

cheers,
OraclePitStop.

very nice..
simple ,easy to understand and implement

Thank you very much…….

Hi,

I am trying to execute the following code.

begin
utl_mail.send(sender => ‘erpdev@sifycorp.com’, recipients => ‘erpdev@sifycorp.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘Test Mail’);
end;

I am getting the following errors.

Error at line 1
ORA-29261: bad argument
ORA-06512: at “SYS.UTL_TCP”, line 28
ORA-06512: at “SYS.UTL_TCP”, line 257
ORA-06512: at “SYS.UTL_SMTP”, line 116
ORA-06512: at “SYS.UTL_SMTP”, line 139
ORA-06512: at “SYS.UTL_MAIL”, line 395
ORA-06512: at “SYS.UTL_MAIL”, line 608
ORA-06512: at line 2

Please provide me the corrective options for
the problem.

Regards,

Murugan. AR.

hi,

i ran below command, but i got error.

begin
utl_mail.send(‘abc.def@xyz.com’,
‘abc.def@xyz.com’,’Testing UTL_MAIL Option’,’blah blah blah’);
end;

error is :

Error at line 1
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 20
ORA-06512: at “SYS.UTL_SMTP”, line 96
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “SYS.UTL_MAIL”, line 395
ORA-06512: at “SYS.UTL_MAIL”, line 608
ORA-06512: at line 2

how to solve this??

to check for smtp_out_server parameter i executed below query , and it gives the value. then where am i going wrong??

select * from v$parameter where name = ‘smtp_out_server’

hi ,

i ran below command, but i got error.

begin
utl_mail.send(‘abc.def@xyz.com’,
‘abc.def@xyz.com’,’Testing UTL_MAIL Option’,’blah blah blah’);
end;

error is :

Error at line 1
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 20
ORA-06512: at “SYS.UTL_SMTP”, line 96
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “SYS.UTL_MAIL”, line 395
ORA-06512: at “SYS.UTL_MAIL”, line 608
ORA-06512: at line 2

how to solve this??

to check for smtp_out_server parameter i executed below query , and it gives the value. then where am i going wrong??

select * from v$parameter where name = ‘smtp_out_server’

Hi I am getting error while running the procedure. Below is the code

create or replace procedure send_mail
as
begin
utl_mail.send(
sender => `abhi.rockin@gmail.com’,
recipients => `abhi.rockin@gmail.com’,
subject => `Testing UTL_MAIL Option’,
message => `blah blah blah’);
end;
/
Plz tell me where I am wrong.
I am using Oracle 11g.
Thanks in advance…….

Simple and very helpful article. mail me simple steps on dbms_scheduler.

Thank you.

nice work.

What is the possible way to send email in Arabic language …


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: