Discussion on:

Sending email from an Oracle database with utl_smtp

Great job with the abstraction Oracle! The only thing they didn't do was have you open a raw TCP/IP socket yourself, outside of that, it is just like writing a very basic MTA, right down to the "helo" command. And I thought having to do a file open on "| sendmail -t" was a pain in the neck, back in the old Unix days...

J.Ja

They did introduce with 10g a more modern utl_mail package. Haven't had to crack it open personally yet, but from what I've read it is a much higher-level abstraction. None of the low-level HELO and all that junk.

But you're right, even back with 8i they should have been able to give a better API for email. It is consistent with how they do other things, like if you've ever used their dbms_ldap to query an ldap server. It is downright painful how much code you have to write to make it work. They just seem to really favor thin API's in their helper packages like this. Very low-level and close to the raw code.

SMTP is not one of those situations where there can be an upside to primitive access. It is not like pointers in C vs. unmanaged code, where the pain of points is offset by the potential for efficiency. Low level access to SMTP accomplishes precisely nothing. I just view this as another example of the overarching laziness and lack of attention to detail on Oracle's part. Especially since, as your sample code demonstrates, it only takes a bit of work to abstract out that utl_smtp package to something actually useful.

J.Ja

Hey Justin,
Its a good topic that u have taken for discussion.. Actually i was looking for an article on this matter.. For me the scenario is: i have Oracle 10g installed on my laptop and i wish to develop an application capable of sending emails automatically depending on some conditions. What are things that needs to be installed.... and how can i move forward in this direction... please suggest

regards,
Prashant

I am in the same boat as you. We just upgraded our database to 10g and I don't have time to change my email program. However, I have found that if my email is longer than 1000 characters the procedure fails with the following error message: "ORA-29278: SMTP transient error: 421 Service not available" at the line of code: "utl_smtp.close_data(l_connection);". As long as I keep my email under a 1000 charcaters I am OK. I am sending out an html email. I have a varaible defined as a clob and I load it with everything I need. I then try and write it out in chunks of a 100.

How can you set the smtp server to work propably with above code ?

hope the article http://technotes.towardsjob.com/oracle/utl-mail-package-oracle-10g explains well about sending Email from Oracle 10g using UTL_MAIL Package

Hi everyone,

I have created the stores procedured, and configure them, but when I execute, I got the next error:

Error report:
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 374
ORA-06512: at "CEOADMIN.SP_ENVIARCORREOALERTASVENCIDAS", line 28
ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. 13sm4958418gxk.5
ORA-06512: at line 14
29278. 00000 - "SMTP transient error: %s"
*Cause: A SMTP transient error occurred.
*Action: Correct the error and retry the SMTP operation.