kalyson1
asked on
Migrating PL/SQL utl_smtp from Solaris to Windows
We are migrating our Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi from Solaris to Windows. We have a pl/sql procedure which is sending email using the utl_smtp utility. Does anyone know what we have to do, if anything, to make sure this will still work on the new platform?
Parts of the code, which is pretty generic, are listed below.
Parts of the code, which is pretty generic, are listed below.
CREATE OR REPLACE PROCEDURE MAIN."SEND_MAIL" ( inRecipient in varchar2 default "someone@somewhere.com",
inSubject in varchar2 default '[no subject specified]',
inBody in varchar2 default '[no message specified]',
inCc in varchar2 default null
) asvConn utl_smtp.connection;
vMailhost varchar2(60) := 'localhost';
vSender varchar2(60) := 'oursender@localhost';
vReply varchar2(80) := 'Reply-To: ourreplyto@somewhere.com;
vErrors varchar2(80) := 'Errors-To: ourerrors@somewhere.com;
[...]
vConn := utl_smtp.open_connection(vMailhost, 25);
utl_smtp.helo(vConn, vMailhost);
utl_smtp.mail(vConn, vSender);
utl_smtp.rcpt(vConn, inRecipient);
vMsg := 'To: '||inRecipient||vCrlf||
'Cc: '||inCc||vCrlf||
vReply||vCrlf||
vErrors||vCrlf||
'Subject: '||vSubject||vCrlf||
inBody||vCrlf||vCrlf||
vFooter||vCrlf;
utl_smtp.data(vConn, vMsg);
utl_smtp.quit(vConn);
[...]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That would be great - almost seems too easy...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll check it out -- thanks for the input!
You have to have somewhere a running SMTP mail server.
It can be on the server machine (not good - traffic, disk space for mails, opened ports) or on other machine.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := 'donkey.xx.iyyyy.zzz'; -- local database MAIL host
BEGIN
c := utl_smtp.open_connection(m ailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);
utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
utl_smtp.quit(c);
raise;
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
utl_smtp.quit(c);
raise;
WHEN others THEN
dbms_output.put_line(' OTHERS Error Encountered.');
utl_smtp.quit(c);
raise;
END;
/
It can be on the server machine (not good - traffic, disk space for mails, opened ports) or on other machine.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := 'donkey.xx.iyyyy.zzz'; -- local database MAIL host
BEGIN
c := utl_smtp.open_connection(m
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);
utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
utl_smtp.quit(c);
raise;
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
utl_smtp.quit(c);
raise;
WHEN others THEN
dbms_output.put_line(' OTHERS Error Encountered.');
utl_smtp.quit(c);
raise;
END;
/
The nowadays way is simplier:
You define the SMTP_OUT_SERVER parameter in the init.ora rdbms initialization file.
However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN
which is guaranteed to be defined to perform appropriately.
ALTER SYSTEM SET smtp_out_server = 'donkey.xx.iyyyy.zzz' SCOPE=BOTH;
CREATE OR REPLACE PROCEDURE email_alert AS
msg varchar2(20000) := 'Realm violation occurred for the ALTER TABLE Command Security Policy rule set. The time is: ';
BEGIN
msg := msg||to_char(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS');
UTL_MAIL.SEND (
sender => 'Oracle@instance.orcl',
recipients => 'josef.schwertner@icw-glob al.com',
subject => 'Table modification attempted outside maintenance!',
message => msg);
EXCEPTION
WHEN others THEN
dbms_output.put_line(' Error Encountered.');
END email_alert;
/
You define the SMTP_OUT_SERVER parameter in the init.ora rdbms initialization file.
However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN
which is guaranteed to be defined to perform appropriately.
ALTER SYSTEM SET smtp_out_server = 'donkey.xx.iyyyy.zzz' SCOPE=BOTH;
CREATE OR REPLACE PROCEDURE email_alert AS
msg varchar2(20000) := 'Realm violation occurred for the ALTER TABLE Command Security Policy rule set. The time is: ';
BEGIN
msg := msg||to_char(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS');
UTL_MAIL.SEND (
sender => 'Oracle@instance.orcl',
recipients => 'josef.schwertner@icw-glob
subject => 'Table modification attempted outside maintenance!',
message => msg);
EXCEPTION
WHEN others THEN
dbms_output.put_line(' Error Encountered.');
END email_alert;
/
ASKER
Thanks, schwertner, for your post.
I guess the thing that is not clear for me is the specifics of moving from one platform to another. Issues that arise may include:
Does exchange server have smtp enabled by default on port 25? Presumably the protocol oracle is using is smtp and requires this.
Will the new windows machine have permission to connect to the exchange mail server?
I think some of these configuration issues go beyond the PL/SQL itself.
I'm trying to anticipate the migration issues for the platform change itself. Maybe I should also post in the Microsoft Exchange area...
I guess the thing that is not clear for me is the specifics of moving from one platform to another. Issues that arise may include:
Does exchange server have smtp enabled by default on port 25? Presumably the protocol oracle is using is smtp and requires this.
Will the new windows machine have permission to connect to the exchange mail server?
I think some of these configuration issues go beyond the PL/SQL itself.
I'm trying to anticipate the migration issues for the platform change itself. Maybe I should also post in the Microsoft Exchange area...
Normally SMTP server uses port 25.
The port should be open for both parties - on the server and on the client.
So firewalls, VPNs etc. should ensure the transmission in both sides.
Firewalls of or ports opened, no antiviruses.
The port should be open for both parties - on the server and on the client.
So firewalls, VPNs etc. should ensure the transmission in both sides.
Firewalls of or ports opened, no antiviruses.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the assistance!
>>I'm trying to anticipate the migration issues for the platform change itself.
The main problem you will find is some SMTP servers use authentication and some do not. If you are using the code above, there is no explicit password / authentication so the SMTP server must allow you to relay, based on your hostname. If it requires authentication, you'll have to modify the code to support the "AUTH LOGIN" SMTP command.
You will find out if you simply test. Good luck.
The main problem you will find is some SMTP servers use authentication and some do not. If you are using the code above, there is no explicit password / authentication so the SMTP server must allow you to relay, based on your hostname. If it requires authentication, you'll have to modify the code to support the "AUTH LOGIN" SMTP command.
You will find out if you simply test. Good luck.
ASKER