how to send email from pl/sql in 806?

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
the database.

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

pl/sql wrapper..........

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)
AS EXTERNAL
LIBRARY SendMailLibrary
NAME "sendMail"
PARAMETERS (p_Subject STRING,
p_Message STRING,
p_From STRING,
p_Recipient STRING);
/

-- Call SendMailC to send a message!
BEGIN
SendMailC('Test email from a C external routine',
'This is the message!',
'The_PL/SQL_Engine',
'YOUR_EMAIL_ADDRESS_HERE');
END;
/
/

/*sendMail.c Prog*/
#include

/* 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"

int sendMail(
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 */
{
FILE *tempFP;
char systemCommand[500];

/* 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
* TEMP_FILE.
*/
tempFP = fopen(TEMP_FILE, "w");
fprintf(tempFP, "Subject: %s\n", subject);
fprintf(tempFP, "From: %s\n", from);
fprintf(tempFP, message);
fclose(tempFP);

/* Now build the mail command. */
sprintf(systemCommand, "%s %s < %s\n",
MAIL_CMD, recipient, TEMP_FILE);

/* And execute it. */
return system(systemCommand);
}

/*SendmailMain.c */
#include

extern int sendMail(char *, char *, char *, char *);

int main()
{
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);
}
regards
hrishy

P.s :you will have to use this make utility to compile sendmail and send mailmain into shared librarires.

[Edited by hrishy on 05-23-2001 at 02:06 AM]