?
Solved

UTL_SMTP gives Service Unavailable when using variable

Posted on 2011-03-09
5
Medium Priority
?
758 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 2000 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 74

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

771 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