How to send an email through oracle stored procedure

forgot to add:  the sneaky part is the smtp_out_server init.ora/spfile parameter.

in 8i or higher, or if you need more control than utl_mail offers you can use utl_smtp

DECLARE
    v_connection   UTL_SMTP.connection;
BEGIN
    v_connection := UTL_SMTP.open_connection('your.email.server.com');
    UTL_SMTP.helo(v_connection, 'your.domain.com');
    UTL_SMTP.mail(v_connection, 'your_return_address@your.domain.com');
    UTL_SMTP.rcpt(v_connection, 'your_recipient@your.domain.com');
    UTL_SMTP.open_data(v_connection);
 
    UTL_SMTP.write_data (v_connection,
                         'From: test from address' || UTL_TCP.crlf
                        );
    UTL_SMTP.write_data (v_connection, 'To: test to address' || UTL_TCP.crlf);
    UTL_SMTP.write_data (v_connection,
                         'Subject: test subject' || UTL_TCP.crlf);
       UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data (v_connection, 'test body' || UTL_TCP.crlf);
    UTL_SMTP.close_data (v_connection);
    UTL_SMTP.quit (v_connection);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

also note in 11g or higher you might need your dba to open up an ACL to the smtp server and port if it isn't open already

This will apply to both utl_smtp as well as utl_mail

Man you caught me typing up a test case.  Oh well, since I already have it...

drop table tab1;
drop table tab2; 
create table tab1 (col1 char(1));
create table tab2 (col1 char(1)); 
insert into tab1 values('a'); 
insert into tab2 values('a');
insert into tab2 values('a');
commit; 
declare 
	sender_v		varchar2(50) := 'databaseServer@mydomain.com';
	recipient_v	varchar2(50) := 'someuser@mydomain.com';
	subj_v		varchar2(100);
	difference	number; 
begin
	 
		select count(*) into difference from tab1; 
		select count(*) - difference  into difference from tab2; 

	if difference != 0 then
		utl_mail.send(
			sender => sender_v,
			recipients => recipient_v,
			subject => 'We have a problem.',
			message => 'Counts are off by ' || to_char(difference)
		);
	end if;
end;
/
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:

:)  your work isn't duplicated though, now there is an example with each package

Hi slightwv,
From where, I can find sender_v email address?
----
declare
      sender_v            varchar2(50) := 'databaseServer@mydomain.com';
      recipient_v      varchar2(50) := 'someuser@mydomain.com';
      subj_v            varchar2(100);
      difference      number;
begin
      
            select count(*) into difference from tab1;
            select count(*) - difference  into difference from tab2;

      if difference != 0 then
            utl_mail.send(
                  sender => sender_v,
                  recipients => recipient_v,
                  subject => 'We have a problem.',
                  message => 'Counts are off by ' || to_char(difference)
            );
      end if;
end;

sender_v  is YOU, or whatever address you want the recipient to reply to and automated bounce-back messages to go to

Make it up.  On our systems I need to use our mail domain but I literally use the user name 'databaseserver' so I know where it comes from.

Hi Slight,
Does it mean i can use any name in sender_v. also, when i try to run this this procedure, am getting util_mail must be declared error

yes, you can use any name,

utl_mail is not installed by default

have your dba install   $ORACLE_HOME/rdbms/admin/utlmail.sql

by default there are no privileges on utl_mail either,  so your dba will need to grant execute on it as well

oops, forgot the package body

also need to install  $ORACLE_HOME/rdbms/admin/prvtmail.sql

I was hoping you would have looked at the docs in the link I posted.  It's all in there.

Not sure why but the docs also have you run:
$ORACLE_HOME/rdbms/admin/prvtmail.sql

More on the 'sender', this is also know as spoofing emails.  You can make it look like the email is sent from the president of your country.  Just be aware as sdstuber mentioned, this is also who the replies go to.

Can i try to run this procedure through bash script where i can use mail -x command to send mail. If yes, how can i execute this oracle procedure in bash shell script?

actually the package body is in prvtmail.plb

not prvtmail.sql

you run the sql scripts through sqlplus,  not bash.  but you could certainly invoke sqlplus from a bash prompt or from within a bash script


$ sqlplus / as sysdba  

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

SQL> $ORACLE_HOME/rdbms/admin/prvtmail.plb

>>not prvtmail.sql

pulled from the docs.

>>Can i try to run this procedure through bash script where i can use mail -x command to send mail. If yes, how can i execute this oracle procedure in bash shell script?

using mailx is a different solution entirely.  What issues are you trying to solve here?  

UTL_MAIL bypasses the operating system all together.  If you want the OS to send the mail then you probably don't need a stored procedure at all.

>>>>not prvtmail.sql

>>pulled from the docs.

I agree the docs dail prvtmail.sql   -  that's why I posted it originally too

but

prvtmail.plb  was  pulled from $ORACLE_HOME/rdbms/admin  in both 10g and 11g homes

All,
The req. is bit change.
I need to put the result of "select count(*) from table a"  into bash script say variable a. How can i do this.

that sounds like a different question entirely.

I think
this one "How to send an email through oracle stored procedure" has been answered in two different ways.

If you want to send email through mailx via bash script that is something completely different.

In which group , I need to put this ques?

You can post another question in the Oracle Zones.  Someone there will be able to help you.

for bash, I'd recommend one of the linux groups and possibly shell scripting you will probably want to include Oracle as a 3rd zone since it's the source of the data