[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

UTL_SMTP gives Service Unavailable when using variable

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month19 days, 18 hours left to enroll

873 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