[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

UTL_SMTP gives Service Unavailable when using variable

Posted on 2011-03-09
5
Medium Priority
?
767 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

649 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