UTL_SMTP gives Service Unavailable when using variable

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
AddOnsIncAsked:
Who is Participating?
 
Helena MarkováConnect With a Mentor programmer-analystCommented:
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
 
sdstuberCommented:
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
 
AddOnsIncAuthor Commented:
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
 
AddOnsIncAuthor Commented:
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
 
AddOnsIncAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.