Solved

UTL_SMTP gives Service Unavailable when using variable

Posted on 2011-03-09
5
735 Views
Last Modified: 2012-05-11
Having a problem with a PL/SQL script for sending email and I can't work out what I'm doing wrong.

This code works just fine.  I get an email with a subject and the correct body.

declare
  c utl_smtp.connection;
begin
  c := utl_smtp.open_connection('smtpus',25);
  UTL_SMTP.helo(c, 'company.com');
  UTL_SMTP.mail(c, 'user@company.com');
  UTL_SMTP.rcpt(c, 'me@mycompany.com');

  UTL_SMTP.open_data(c);
  UTL_SMTP.write_data(c, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'From: ' || 'user@company.com' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'To: ' || 'me@mycompany.com' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'Subject: ' || 'Test message subject' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'This is a test message. Line 1' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'This is a test message. Line 2' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, utl_tcp.CRLF);
  UTL_SMTP.close_data(c);

  UTL_SMTP.quit(c);
end;
/

Open in new window


The problem is that this is a generic piece of code that I want to be able to transport to different places.

Some of the fields I want to be stored in a config table and inserted into the code so I can re use the code without having to hard code SMTP servers and the like.

So I went to this:

declare
  c utl_smtp.connection;
  l_smtp char(50);
  err char(1);
begin
  err = 'N';
  begin
    select trim(config_value) into l_smtp from configtab where config_key = 'SMTP-SERVER';
  exception
    when others then
      err := 'Y';
  end;


  c := utl_smtp.open_connection(l_smtp,25);
  UTL_SMTP.helo(c, 'company.com');
  UTL_SMTP.mail(c, 'user@company.com');
  UTL_SMTP.rcpt(c, 'me@mycompany.com');

  UTL_SMTP.open_data(c);
  UTL_SMTP.write_data(c, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'From: ' || 'user@company.com' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'To: ' || 'me@mycompany.com' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'Subject: ' || 'Test message subject' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'This is a test message. Line 1' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, 'This is a test message. Line 2' || utl_tcp.CRLF);
  UTL_SMTP.write_data(c, utl_tcp.CRLF);
  UTL_SMTP.close_data(c);

  UTL_SMTP.quit(c);
end;
/

Open in new window


When I run that:

SQL> @mailtest5
declare
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at line 32


SQL>


There's something in that variable substitution that I'm missing.

Any ideas ?

Thanks
0
Comment
Question by:AddOnsInc
  • 3
5 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 500 total points
ID: 35093268
I think that this variable
  l_smtp char(50);

ought to be declared as VARCHAR2(50)
  l_smtp VARCHAR2(50);

Also there is no test of variable err in your code:

IF err='Y' THEN
    c := utl_smtp.open_connection(l_smtp,25);
...
   UTL_SMTP.quit(c);
ELSE
  dbms_output.put_line('error');
END;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35093924
check the "c" value returned from open_connection.

Your open is failing

you can test your smtp configuration with a simple telnet

telnet yourserver 25


if you connect then the smtp server is up and running, if not, then it's not and utl_smtp can't help with that
0
 

Author Comment

by:AddOnsInc
ID: 35095103
sdstuber: the code itself works and the smtp server is responsive because when I hard code the SMTP server into the code (see the first part of the code) it works just fine.   It's when I try to use a variable to dynamically configure the call instead of hard coding it that it fails.

Henka:  The full program has the test of the err variable, I was just yanking out a snippet of the code, sorry about that.  Tried changing the variable definition to varchar2 but there is no change.

Any other help appreciated.

Thanks
0
 

Author Comment

by:AddOnsInc
ID: 35095134
Actually, scratch that, when I was changing the char to varchar2 I mistyped and added an extra character.   I think that might have worked.   Will continue testing this morning (I still need to add variables for the from address and to address but I think that might be the answer.
0
 

Author Closing Comment

by:AddOnsInc
ID: 35095320
Thanks that was it.   Use VARCHAR2 not CHAR and it works (as long as I use trim() around the variable to remove all excess spaces since SMTP doesn't seem to like extra spaces.
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

910 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

19 Experts available now in Live!

Get 1:1 Help Now