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]
