Send mail from oracle sql?

[coleex]

Is there a method for sending general email that will alert admins of certain events in the database such as a job status?
Iam using oracle 7.3.4

Thanks
Eric

[marist89]

This is from 8i. http://otn.oracle.com/docs/products/...tp.htm#1000695

Don't know if there is similar functionality in 7.x

[santoshym]

Hi

Jeff is right, the UTL_SMTP functionality is available for oracle 8.1.6 database or a higher version.

If You want to implement the UTL_SMTP functionality you have to execute the following as user SYS

initjvm.sql
initplsj.sql

and then you have to create a procedure to use UTL_SMTP to send a mail.

Regards,

[sambavan]

For Oracle 7, you would have to write a pro*c script that would invoke a mailer demon and send the mail. Then you would have to schedule some joj to execute it. I'm not sure whether you can use the OEM, with 7, if that is possible its simple, to scedule the event on the OEM and send an email/page to your admin(s).

Sam

[pipo]

from my archives (maybe I got it from here) :


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);
}