Here's an example procedure that will send mail based on the recipient, message and subject that is passed. Simply call this procedure to send mail.
CREATE OR REPLACE
( recipient IN varchar2,
message IN varchar2,
subject IN varchar2 DEFAULT 'Oracle Status Message')
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
c := utl_smtp.open_connection('YOUR.MAIL.SERVER.COM');
utl_smtp.helo(c, 'YOUR MACHINE NAME');
utl_smtp.mail(c, 'YOUR INSTANCE NAME');
send_header('From', '"DBA" <email@example.com>');
utl_smtp.write_data(c, utl_tcp.CRLF || message);
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
'Failed to send mail due to the following error: ' || sqlerrm);
I tried to send email with stored procedure.
It run correctly just I connect with system/manager.
If I change user/password don't run my procedure and I see this error:
ERROR at line 1:
ORA-29540: class oracle/plsql/net/TCPConnection does not exist
ORA-06512: at "SYS.UTL_TCP", line 533
ORA-06512: at "SYS.UTL_TCP", line 199
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 121
ORA-06512: at "AFM.SPEDISCI_MAIL", line 17
ORA-06512: at line 1
If you don't want to use the Java option in your db my understanding is you can not use this feature. So ... I would propose the you use a External Procedure from Oracle instead. All you have to do is create a small C app that invokes mail or mailx and sends off the mail to the approproriate people. External Procedures work similarly to the Java procs. The advantage is that you do not have to use Java. There is info on this on technet.oracle.com but I have working examples if you would like to see.
Ok. Look at this doc I'm attaching. This has a pretty decent example of creating and using an external proc. Be warned there is a security warning out there for Oracle about using external procs. I have not seen alot of documentation that supports what the hack is. Despite this this feature works well. The way you might change this example to fit your needs is to put a system call in the C program that calls mailx or mail with the approrpriate parameters.