How to send email from Oracle 10G ?

KG1973
KG1973 used Ask the Experts™
on
My server running on W2003.

Honestly I never configure my server as email server before.
Then I found the following link which seems might give the solution.

http://oraclepitstop.wordpress.com/2008/04/24/how-to-send-email-from-10g-oracle-database-utl_mail/

-------------------------------------------------------------------------
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.
--------------------------------------------------------------------------


but when I run, I got this error.
exec utl_mail.send((sender => ‘oraclepitstop@wordpress.com’, recipients => ‘oraclepitstop@wordpress.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);

--------ERROR-------

ORA-06550: line 1, column 43:
PLS-00103: Encountered the symbol "‘" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string literal with character set specification>
   <an alternatively-quoted S
ORA-06550: line 1, column 71:
PLS-00103: Encountered the symbol "’" when expecting one of the following:

   . ( ) , * @ & = - + < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
   LIKE4_ LIKEC_ between || multiset


--------END ERROR --------------
by look at the given code, I found double bracket '((' is not closed. Then I add extra bracket at the end, still
have same error. I also change single quote to double quote. The result still same.

If this test succeeded, I want to create a trigger when a table (table A) is updated, it will automatically send an email
to all recipients stored from another table, let say (table B).

Table A:

id, status
1, 'P'
2, 'A'
3, 'P'

table B:

id, email
1, 'test@test.com'
2, 'test2@test.com'


Therefore, whenever the status 'P' (in process) changed to 'A' (Approved), the trigger will
automatically send notification mail to all receipients from table B.

So my questions is how to make these work including how to invoke the mail command in trigger. Please give
me an example.

Thanks.



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
1. Your test email command should be:
exec utl_mail.send((sender => ‘oraclepitstop@wordpress.com’, recipients => ‘oraclepitstop@wordpress.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’));
2. Create your trigger

CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
ret  VARCHAR2(4000);
hold VARCHAR2(4000);
cur  sys_refcursor;
BEGIN
OPEN cur FOR q;
LOOP
FETCH cur INTO hold;
EXIT WHEN cur%NOTFOUND;
F ret IS NULL THEN
ret := hold;
ELSE
ret := ret || ',' || hold;
END IF;
END LOOP;
RETURN ret;
END;
/

CREATE TRIGGER send_mail
AFTER UPDATE OF status ON tableA
REFERENCING NEW AS n
FOR EACH ROW
DECLARE
statusA VARCHAR(30);
recipients VARCHAR2;
BEGIN
IF (:new.status = 'A') and (:old.status = 'P') THEN
recipients ='' || to_char(rowconcat(select email from tableB)) || ''
utl_mail.send{
sender => 'yourmail@abc.com',
recipients => recipients,
subject => 'Bla bla',
message => 'Bla Bla Bla'
)
END IF
END;
/

Author

Commented:
Thanks,

I did try, but still got same error.
ORA-06550: line 1, column 43:
PLS-00103: Encountered the symbol "'"when expecting one of the following:

also can you explain what the function rowconcat does ?

 
Shaju KumbalathDeputy General Manager - IT

Commented:
exec utl_mail.send((sender => ‘oraclepitstop@wordpress.com’, recipients => ‘oraclepitstop@wordpress.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);
i think quotes are causing the problem
retype the single quotes
 
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Shaju KumbalathDeputy General Manager - IT

Commented:
to

exec utl_mail.send((sender => 'oraclepitstop@wordpress.com', recipients => 'oraclepitstop@wordpress.com', subject => 'Testing UTL_MAIL Option', message => 'blah blah blah');  

Commented:
Hi KG1973,
You should copy the command from web to notepad and edit all characters "'" in the command.
Function rowconcat will concatenate all rows of 'email' column of TableB in string, each value is separated by comma.

Commented:
I correct rowconcat function and send_mail trigger as below
CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
ret  VARCHAR2(4000);
hold VARCHAR2(4000);
cur  sys_refcursor;
BEGIN
OPEN cur FOR q;
LOOP
FETCH cur INTO hold;
EXIT WHEN cur%NOTFOUND;
IF ret IS NULL THEN
ret := hold;
ELSE
ret := ret || ',' || hold;
END IF;
END LOOP;
RETURN ret;
END;
/

CREATE OR REPLACE TRIGGER send_mail
AFTER UPDATE OF status ON tableA
FOR EACH ROW
DECLARE
statusA VARCHAR(30);
recipients VARCHAR2;
BEGIN
IF (:new.status = 'A') and (:old.status = 'P') THEN
recipients ='' || to_char(rowconcat('select email from tableB')) || '';
utl_mail.send{
sender => 'yourmail@abc.com',
recipients => recipients,
subject => 'Bla bla',
message => 'Bla Bla Bla'
);
END IF;
END;
/

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
What everyone is hinting at but hasn't come out and said:

The single quotes in the example are not really single quotes.

Look closely and you'll see most of the opening quotes are 'backwards' quotes, chr(96) and not an actual single quote chr(39).

for example look at the opening quote in:
alter system set smtp_out_server = ‘172.25.90.165:25' scope=both;
Shaju KumbalathDeputy General Manager - IT

Commented:
slightwv is right ..the same I have been put in my post
26443886

Author

Commented:
I managed to redoit as suggested

exec utl_mail.send((sender => 'oraclepitstop@wordpress.com', recipients => 'oraclepitstop@wordpress.com', subject => 'Testing UTL_MAIL Option', message => 'blah blah blah'));  

 but then I got another error :

ORA-06550: line 0, column 0:
PLS-00801: internal error [ph2exp:case]
ORA-06550: line 1, column 18:
PL/SQL: Statement ignored

Is this something to do with the script that I ran in the begining ? :
>>2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
>>3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Like you mentioned above, check your double parans.  Try:
exec utl_mail.send(sender => 'oraclepitstop@wordpress.com', recipients => 'oraclepitstop@wordpress.com', subject => 'Testing UTL_MAIL Option', message => 'blah blah blah');

What is your full version or Oracle(please include all 4 numbers, 10.2.0.4)?

Might be a bug.

I'm not sure about adding the port on the mail server.  If 172.25.90.165 is your mail server try:
alter system set smtp_out_server = ‘172.25.90.165' scope=both;


There are tons of examples out there on using utl_mail.  You might check a different thread to see if you missed anything:

http://technotes.towardsjob.com/oracle/utl-mail-package-oracle-10g/
Shaju KumbalathDeputy General Manager - IT
Commented:
Instead of copying
exec utl_mail.send(sender => 'oraclepitstop@wordpress.com', recipients => 'oraclepitstop@wordpress.com', subject => 'Testing UTL_MAIL Option', message => 'blah blah blah');

from the source can u type it your own?
As per metalink a wrong conctruct can cause PLS-00801 error.
See metalink Bug 3486915: A WRONG CONSTRUCT CAUSES PL/SQL INTERNAL ERROR
 

Commented:
I copied your command and pasted it into my Oracle Database, I got the same error as yours. However, If I typed it by my hand, it run well.
So, please type the command by your hand and see the result.

Notes: your mail server should be configured to use anonymous connection. You shouldn't try to use gmail or yahoo, if no, you will receive error:
ERROR at line 1:
ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first.
20sm2667576pzk.5
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 222
ORA-06512: at "SYS.UTL_MAIL", line 397
ORA-06512: at "SYS.UTL_MAIL", line 608
ORA-06512: at line 1
Commented:
Hi KG1973,
For last my post about code of send_mail trigger, I didn't have database to try, so it's not good.
Here is final code which already ran successfully on my database.
CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
ret  VARCHAR2(4000);
hold VARCHAR2(4000);
cur  sys_refcursor;
BEGIN
OPEN cur FOR q;
LOOP
FETCH cur INTO hold;
EXIT WHEN cur%NOTFOUND;
IF ret IS NULL THEN
ret := hold;
ELSE
ret := ret || ',' || hold;
END IF;
END LOOP;
RETURN ret;
END;
/
CREATE OR REPLACE TRIGGER send_mail
AFTER UPDATE OF status ON tableA
FOR EACH ROW
DECLARE 
recipients1 VARCHAR2(4000);
BEGIN
IF (:new.status = 'A') and (:old.status = 'P') 
THEN
recipients1:=''|| to_char(rowconcat('select email from tableB')) ||'';
utl_mail.send(
sender => 'yourmail@abc.com',
recipients => recipients1,
subject => 'Bla bla',
message => 'Bla Bla Bla'
);
END IF;
END;
/

Open in new window

Author

Commented:
Tiepld, I will try the code after fixing the bug.
If i can send the mail using sql thru cmd line,
I try yours.

Author

Commented:
tiepld,
after few attempts, I am no where close to the solution. Then I ran your scripts but still getting
an error which probably similar to the one I have

ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 97
ORA-06512: at "SYS.UTL_SMTP", line 139
ORA-06512: at "SYS.UTL_MAIL", line 405
ORA-06512: at "SYS.UTL_MAIL", line 594
ORA-06512: at "BMP.SEND_MAIL", line 7
ORA-04088: error during execution of trigger 'BMP.SEND_MAIL'

what seems to go wrong ?

slightwv,
My Oracle version is 10.2.0.1.0 Production
I did try to alter the " alter system set smtp_out_server = ‘172.25.90.165' scope=both; ".

My understanding is based on the error messages, it is not the command to send the mail. Instead it is an email configuration problem. Am I right ?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Is the SNMP process running on the dabase server?  On Windows it's a service you can start.

Commented:
Hi KG1973,
The error you got mean that your TRIGGER is created successfully. And I also think that your creation for UTL_MAIL package is correct. The problem lies in your SMTP Server and how you configure SMTP Server on Oracle.
Correct command to configure SMTP Server on Oracle must be:
alter system set smtp_out_server='172.25.90.165:25' scope=both;

Also, make sure that there is no firewall or no antivirus program blocked port 25 or your SMTP Server.  To check that, you can use this command:
telnet 172.25.90.165 25

Commented:
On Oracle Forum, jkallman informs a user that, ORA-29278 may signify that, "the same server where you have your Oracle database running, does not have an SMTP server up and running."

Furthermore, an additional reply offers this solution to help resolve ORA-29278:

    Go to Control Panel->Add or Remove Programs->Click on
    Add/Remove Windows Components
    Check IIS check box.
    Select Internet Information Service (IIS) option and click on Details button
    Check whether SMTP Service is checked or not.
    If not selected then select SMTP check box.

    This process should be done on server.
    It will help out from ORA-29278: SMTP transient error: 421 Service not available problem.
Shaju KumbalathDeputy General Manager - IT

Commented:
are u hosting the SMTP service on the same server? other wise above mentioned check should be done on THE SERVER '172.25.90.165'

 

Author

Commented:
thanks, I will check further on this. Also I got error saying 'no connection on port 25' after i telnet 172.20.90.165 25. So the problem really on my server.

My mail server also sit on my oracle server.

Author

Commented:
When I check my server, it already installed IIS. But then I reinstall IIS again.
After installation, I ran "telnet localhost 25" and it return

220 mydomain.com Microsoft ESMTP MAIL Service, Version 6.0.3790 ready at Sat, 6 Feb 2010 14:59:22 +0800

Then I update tableA, to trigger email function to send email. This time I got this error messages :

ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for my_yahoo@yahoo.com
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 241
ORA-06512: at "SYS.UTL_MAIL", line 424
ORA-06512: at "SYS.UTL_MAIL", line 594
ORA-06512: at "BMP.SEND_MAIL", line 7
ORA-04088: error during execution of trigger 'BMP.SEND_MAIL'

my_yahoo@yahoo.com is an email address stored in tableB.

Commented:
As this link: http://forums.oracle.com/forums/thread.jspa?messageID=2214596, maybe you are forbidden to send email to outside. So, what happens if you try with another email?

Author

Commented:
I already double check IIS properties for SMTP. I am using anonymous connection. Also I configure to use grant relay access to current server.

I also tried different email apart from yahoo and gmail but still got same error.

Author

Commented:
After few attempts, I found that sending email thru my webserver (apache) works with 3rd party email server (QK SMTP Server) that recently I installed for evaluation.  But still, if I try to send email thru oracle database, it show same error message.



Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
It appears your trial mail server isn't properly set up to relay emails to an outside entity.

There are numerous links out there on this message and everything points to an email server issue not an Oracle issue.

Author

Commented:
after living in the dark, finally I manage to run the script without error. Whenever I update the table, it will automatically send email. BUT the solution to this is merely on luck. Actually I don't know what's really happening.

I tried all again, but with different server that I setup exactly same way as I did to my old server. What I can conclude is, mail server configuration. But I didn't add additional code or whatever step.

I wish I can do reverse engineering to know more to get what actually caused the problem.

Anyway, as what I asked for, you'd solved my problem.

Thanks.

Author

Commented:
I really appreciate you all for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial