| Using Oracle UTL_MAIL to send email through Kerio email server [message #659948] |
Mon, 06 February 2017 04:00  |
 |
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Hi All,
I want to use UTL_MAIL package to send emails from Oracle using email account from a Kerio Connect 9 mail server.
The first step, according to my reading, is to add an initialization parameter in initORACLE_SID.ora.
I am using a development machine that connects to Oracle using instant client 12 and I need guidance.
Many thanks,
Ferro
|
|
|
|
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #659952 is a reply to message #659950] |
Mon, 06 February 2017 04:58  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
OraFerro wrote on Mon, 06 February 2017 11:38...
Actually I dont have utlmail.sql and when I run sqlplus as sys from the development machine that uses instant client 12:
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SP2-0310: unable to open file "$ORACLE_HOME/rdbms/admin/utlmail.sql"
The correct call is either to expand the $ORACLE_HOME variable (and not using it in this way) or replace "$ORACLE_HOME" by "?" (without the quotes).
And, as John said, don't forget to also call PRVTMAIL.PLB.
[Updated on: Mon, 06 February 2017 04:59] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #659972 is a reply to message #659952] |
Mon, 06 February 2017 22:58 |
|
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Thanks to John and Michel,
Yes, I am trying the actual path instead of ORACLE_HOME but it give me the error I have reported.
For example:
SQL> @/oracle/app/oracle/product/11.2.1/dbhome_12/rdbms/admin/utlmail.sql
SP2-0310: unable to open file "/oracle/app/oracle/product/11.2.1/dbhome_12/rdbms/admin/utlmail.sql"
I meant I am using SQLPLUS from my machine (sorry for the misleading expression "oracle clinet").
How can I solve this problem? I can access the package files from the server, is it possible to use them from my local machine?
Thanks,
[Updated on: Mon, 06 February 2017 23:29] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #659984 is a reply to message #659982] |
Tue, 07 February 2017 01:41 |
|
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
@John, it is the same DB.
What do you mean by:
Quote:
your Oracle Home is incorrect
What I have is:
SQL> var oracle_home clob;
SQL> exec dbms_system.get_env('ORACLE_HOME', :oracle_home);
PL/SQL procedure successfully completed.
SQL> print oracle_home
ORACLE_HOME
--------------------------------------------------------------------------------
/oracle/app/oracle/product/11.2.0/dbhome_1
SQL> @/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlmail.sql
SP2-0310: unable to open file "/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlmail.sql"
SQL>
and this is the path I copied now from my FTP client which includes utlmail.sql:
/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin
I am sure I have something wrong but needs guidance to know what it is and fix it.
*Please note that the path in the original post is different as I manually changed it (as an example), now I am posting the actual path
Thanks,
Ferro
[Updated on: Tue, 07 February 2017 01:42] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #659989 is a reply to message #659988] |
Tue, 07 February 2017 02:05 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Nice of you to know how I think. I think you need to start again, and not obfuscate reality which wastes time and reduces the likelihood (and inclination) of anyone assisting.
Here is an action plan:
Log on to your database server, and show that it is your database server.
Set and export your ORACLE_HOME and PATH and ORACLE_SID variables. Demonstrate that they are correct.
Use the ls command to show that the utlmail.sql script doe or does not exist in $ORACLE_HOME/rdbms/admin directory.
Do not obfuscate anything.
|
|
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #659994 is a reply to message #659992] |
Tue, 07 February 2017 03:33 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you using the full path and file name for the @ command in sqlplus and getting that error then 1 of the following must be true:
1) you're using the wrong path
2) you're using the wrong filename
3) the oracle osuser can't access the file
4) the file is corrupted (maybe, though this may give a different error).
So check each of those possibilities in turn and sort it out.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #660006 is a reply to message #659999] |
Tue, 07 February 2017 04:39 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Sigh - The @ command looks for a file on the machine that sqlplus is running on. It does not look for a file on the machine the DB is running on.
You need to connect to the DB server and run sqlplus from the DB server.
Or you need to copy the file to your local machine and then point sqlplus to that copy.
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #660018 is a reply to message #660006] |
Tue, 07 February 2017 07:18 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
However if you are installing utl_mail for everyone to use you must install it as the SYS user. Also, do you realize that utl_mail is just a front end for the DBMS_SMTP package and you don't even need utl_mail installed to send email. There are a lot of pl/sql packages that are available for download that do a very nice job of sending email or you can write your own. If you want to have UTL_MAIL installed then you need to contact your DBA staff that supports the oracle database server and ask that it be installed. Since you don't have the passwords to log into the database server, then you are NOT the correct person to be installing this package.
[Updated on: Tue, 07 February 2017 07:21] Report message to a moderator |
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #660020 is a reply to message #659999] |
Tue, 07 February 2017 07:34 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
OraFerro wrote on Tue, 07 February 2017 10:13@John, sorry for disappointing you and thanks for your help and the many times you've helped before.
However this is not a new piece of info, it was my original post and my first message to you yesterday:
Quote:
I am using a development machine that connects to Oracle using instant client 12 and I need guidance.
Quote:
Actually I dont have utlmail.sql and when I run sqlplus as sys from the development machine that uses instant client 12:
Thanks,
Ferro
We pointed out that you needed to run it from the server and further down you implied that's what you were now doing:
OraFerro wrote on Tue, 07 February 2017 07:01
I wish I could, but I failed after running the command that I shared in my previous post! I want to run it correctly from the server and seek you help to know why I cant.
I guess you misunderstood what run it from the server means.
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #660048 is a reply to message #660047] |
Tue, 07 February 2017 22:54 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
The Kerio connect 9 software should work fine as long as you have the smtp server component configured. Also if you are going to be sending to email addresses outside of your domain you will also have to make sure that relay is turned on but set it to only allow smtp submissions from internal ip addresses or even only the specific ip of the Oracle server. You might want to decide if you want to send a plain text email or an html formatted one. The html email looks much nicer, but up to you. Utl_mail will work fine for what you want to do. As has been stated before you just have to connect as the sys user as the Oracle owning user on the database server to install the package
[Updated on: Tue, 07 February 2017 22:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #660093 is a reply to message #660064] |
Wed, 08 February 2017 14:44 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
cookiemonster wrote on Wed, 08 February 2017 04:34The thing about sqlplus is it runs on the client and sends SQL and PL/SQL to the DB and waits for a response. Any sqlplus specific commands are processed locally.
In fact that's true for all DB client tools.
True, but the OP said that the email would be generated by a Database Scheduled job so the smtp email would come from the database server, not from the client. it all depends how locked down you want the SMTP relay.
|
|
|
|
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #660201 is a reply to message #659948] |
Sun, 12 February 2017 03:01 |
|
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Dear All,
After learning where to execute the script from, I created the package and its body and also configured the mail server (enabled SMTP component and added the Oracle DB server IP to the SMTP senders list)
I failed to send email due to ORA-29278: SMTP transient error: 421 Service not available.
I read about it in the internet and tried to re-check my mail server configuration, my SMTP_OUT_SERVER parameter, and also the mail service in Oracle DB AIX server, they all seem fine to me!
Kindly direct me to what might be wrong:
* Below is the exact server prompt with the exception of changing domain name
SQL> alter system set smtp_out_server = 'mail.MYDOMAIN.com';
System altered.
SQL> alter system set smtp_out_server = 'mail.MYDOMAIN.com:25' scope=both;
System altered.
SQL> exec UTL_MAIL.SEND (sender=>'mfarouk@MYDOMAIN.com', recipients=>'itadmin@MYDOMAIN.com', subject=>'Test Message', Message=>'test');
BEGIN UTL_MAIL.SEND (sender=>'mfarouk@MYDOMAIN.com', recipients=>'itadmin@MYDOMAIN.com', subject=>'Test Message', Message=>'test'); END;
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 1
SQL> show parameter smtp_out_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server string mail.MYDOMAIN.com:25
SQL> ^Cexit
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
/oracle # ps -ef|grep sendmail
root 270494 217224 0 Dec 31 - 23:52 sendmail: accepting connections
Thanks,
Ferro
|
|
|
|
|
|
| Re: Using Oracle UTL_MAIL to send email through Kerio email server [message #660203 is a reply to message #660201] |
Sun, 12 February 2017 03:59 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Is the mail domain available in DNS? On the database server at the unix prompt type
ping mail.mydomain.com if it can't find it, it won't work.
In the smtp_out_server you can also specify the IP address instead of the URL. You also don't need to specify the port, it knows to use port 25
If the DNS works then see if you can get to the sntp server by typing the following
telnet mail.mydomain.com 25
If smtp wants to talk to you it will respond
[Updated on: Sun, 12 February 2017 04:00] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
