Sending emain using Oracle? 3

[soans]

Is it possible to run SQLPlus commands from a PL/SQL package/procedure? I need to send mail using unix mail utility. I intend to do this by using the SQLPlus' host' command.
OR
Is there any other oracle utility to send mail? I have a vanilla installation of Oracle 8.1.6.0.0.
Thanks
[sig][/sig]

 

[carp]

Soans -
There was a thread on this some time ago, and it appears you can run host commands via the DBMS_PIPE package.

However, in 8.1 there is a package called utl_smtp that allows you to send email from within the database. In order to keep life simple, I wrote a procedure that accepts
the recipient's address, a subject line, and the body of the message itself. I have included the code below.

Unfortunately, utl_smtp does not handle basic things like an actual subject line or attachments. I can only assume that (a) whoever developed this has never actually used email or (b) this had to be released before it was actually finished. My procedure actually sticks the subject line at the top of the email message. Also note that I have hardcoded the sender's email address and mail server. You could make this more generic by accepting one or both of these as an argument rather than hardcoding.

PROCEDURE email(p_target IN VARCHAR2,
p_subj IN VARCHAR2,
p_message IN VARCHAR2) IS

--PURPOSE: SEND p_message TO p_target VIA EMAIL.

v_eol VARCHAR2(2) := chr(13)||chr(10); -- EOL CHARACTERS
v_sender VARCHAR2(50) := 'YOUR_EMAIL_ADDRESS_HERE';
mailhost VARCHAR2(35) := 'YOUR_MAIL_SERVER_HERE';
mail_connection utl_smtp.connection;

BEGIN
-- ESTABLISH CONNECTION AND PERFORM HANDSHAKING
mail_connection := utl_smtp.open_connection(mailhost,25);
-- BUILD THE MAIL
utl_smtp.helo(mail_connection,mailhost);
--
utl_smtp.mail(mail_connection,v_sender);
--
utl_smtp.rcpt(mail_connection,p_target);
--
utl_smtp.data(mail_connection,'Subj: '||p_subj||v_eol||v_eol||p_message||v_eol);
utl_smtp.quit(mail_connection);
EXCEPTION
WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20002,'ERROR IN EMAIL => '||SQLCODE||': '||SQLERRM);
END;

[sig][/sig]

 

[soans]

carp,
Thanks for your response. I tried using utl_smtp package and I was getting this error message

ORA-29540: class oracle/plsql/net/TCPConnection does not exist

Here is the code
procedure send_mail(
p_sender IN VARCHAR2 DEFAULT 'my address',
p_recipient IN VARCHAR2 DEFAULT 'my address',
p_message IN VARCHAR2 DEFAULT 'TEST')
is
mail_host varchar2(30):='my smtp server';
mail_conn utl_smtp.connection;
begin
mail_conn:=utl_smtp.open_connection(mail_host,25);
utl_smtp.helo(mail_conn,mail_host);
utl_smtp.mail(mail_conn,p_sender);
utl_smtp.rcpt(mail_conn,p_recipient);
utl_smtp.data(mail_conn,p_message);
utl_smtp.quit(mail_conn);
exception
when others then
--error handler
end;/

Does this mean I have the wront smtp server or there is something mssing with utl_smtp package
Thanks Again [sig][/sig]

 

[carp]

As the error message states, you do not have the TCPConnection class in the subdirectory where Oracle expects it. You need to find that class and place it where Oracle is looking for it. [sig][/sig]

 

[MikeLacey]

Carp,

gotta be a FAQ that one.... [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[carp]

Mike -
You're probably right! It is done. [sig][/sig]

 

[tessan11]

What did FAQ tell you. Cause I have the same problem and cant find a solution...

 

[carp]

If you go to FAQ you can read it yourself...that's it's purpose.
If you still have a problem, then start a new thread that describes the exact problem that you're having and we'll try to help.