Solved

Migrating PL/SQL utl_smtp from Solaris to Windows

Posted on 2009-07-15
12
994 Views
Last Modified: 2013-12-19
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.  
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);

 

 

 

[...]

Open in new window

0
Comment
Question by:kalyson1
  • 5
  • 4
  • 3
12 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 350 total points
ID: 24865384
The code expects localhost to be an SMTP mail server. So either setup the new Windows box with an SMTP or Exchange server, or change the code above to use a different address of the remote SMTP server.
0
 

Author Comment

by:kalyson1
ID: 24865456
Thanks for the comment, mrjoltcola.   So you mean instead of "localhost" we should point to the address of the exchange server we are using?   Is that really all we need to do?
0
 

Author Comment

by:kalyson1
ID: 24865473
That would be great - almost seems too easy...
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 350 total points
ID: 24865485
Should be. You'll have to test. Depends on your Exchange settings and whether you are using any additional options like authenticated SMTP, etc. Try and see.
0
 

Author Comment

by:kalyson1
ID: 24865985
I'll check it out -- thanks for the input!
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24867281
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(mailhost, 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;
/

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 24867298
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-global.com',
    subject => 'Table modification attempted outside maintenance!',
    message => msg);
EXCEPTION
WHEN others THEN
   dbms_output.put_line('  Error Encountered.');
END email_alert;
/
0
 

Author Comment

by:kalyson1
ID: 24869286
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...


0
 
LVL 47

Expert Comment

by:schwertner
ID: 24869321
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.
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 150 total points
ID: 24869337
It is OS independent - there is a port and the SMTP protocol, no other soft or hard consideration but the network.
0
 

Author Closing Comment

by:kalyson1
ID: 31604051
Thanks for the assistance!
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24870991
>>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.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now