in 8.0.6 and earlier versions you will have to use an external program to send a mail .prferabbly a c programmme.you can do it using pl/sql & c prog.........
I. sendMail C example
The files for the C example consist of the following:
sendMail.c: File containing the actual C routine
sendMailMain.c: C driver program which can be used to call sendMail()
directly, without going through the database. This can
be used to test sendMail.
SendMail.sql: Contains the SQL commands necessary to create the library
object and PL/SQL wrapper.
ch10.mk: This is a sample Makefile (which may have to be modified for your
system) that will compile the files.
In order to run the sendMail program as an external routine, you need to do
the following steps:
1) Compile sendMail.c into a shared library. compile both sendMail.c and sendMailMain.c into an executable sendMail,
and to create a shared library sendMail.so from sendMail.c. The
executable can be used to test sendMail directly, without going through
2) Create the library using the CREATE LIBRARY command. See SendMail.sql
for an example. You will need the CREATE LIBRARY system privilege GRANTed
to you first.
3) Create the SendMailC PL/SQL wrapper. See SendMail.sql for the example.
4) Now you should be able to call sendMailC from an anonymous PL/SQL block
to send your message, similar to the example in SendMail.sql. Be sure to
change p_From and p_Recipient as neeed.
heres the code
save this as sendmail.sql
CREATE OR REPLACE PROCEDURE SendMailC (
p_Subject IN VARCHAR2,
p_Message IN VARCHAR2,
p_From IN VARCHAR2,
p_Recipient IN VARCHAR2)
PARAMETERS (p_Subject STRING,
-- Call SendMailC to send a message!
SendMailC('Test email from a C external routine',
'This is the message!',
/* Set TEMP_FILE to a filename in a directory writable by the
* Oracle operating system user.
#define TEMP_FILE "/tmp/mail.out"
/* Set MAIL_CMD to the full path of the system mail program. This
* is often /bin/mail or /bin/rmail.
#define MAIL_CMD "/bin/mail"
char *subject, /* Subject of the message */
char *message, /* Message to be sent */
char *from, /* Email id of the sender */
char *recipient) /* Email id of the recipient */
/* We will send an email message using the Unix 'mail' command.
* This requires us to put the message into a file first, then
* we can use it as input to mail.
/* First write the subject and message to the file specified in
tempFP = fopen(TEMP_FILE, "w");
fprintf(tempFP, "Subject: %s\n", subject);
fprintf(tempFP, "From: %s\n", from);
/* Now build the mail command. */
sprintf(systemCommand, "%s %s < %s\n",
MAIL_CMD, recipient, TEMP_FILE);
/* And execute it. */
extern int sendMail(char *, char *, char *, char *);
char *subject = "Test email from a C routine";
char *message = "This is a test message from C.\nHello!\n";
char *from = "The_Operating_System";
char *to = "YOUR_EMAIL_ADDRESS_HERE";
printf("Sending C message to %s!\n", to);
sendMail(subject, message, from, to);
P.s :you will have to use this make utility to compile sendmail and send mailmain into shared librarires.