Re: Send mail by Stored Procedure

I snatched this off of this news group: (credits/bitches to author, not me.)
Frederic DEBRUS <frederic.debrus_at_ces-cdr.be> wrote in message news:<01bf2b98$d8221980$3e35a99e_at_gal159a>...

> TWO STEPS :

>

> 1) create an Oracle package

>

> CREATE OR REPLACE PACKAGE mail as

> procedure send(dest in varchar2,subject in varchar2,mesg in varchar2);

> procedure get(dest out varchar2,subject out varchar2,mesg out varchar2);

> END;

>

> CREATE OR REPLACE PACKAGE BODY mail as

> returnpipe varchar2(30);

> procedure send(dest in varchar2, subject in varchar2,mesg in varchar2) is

> call_status integer;

> begin

> dbms_pipe.pack_message(dest);

> dbms_pipe.pack_message(subject);

> dbms_pipe.pack_message(mesg);

> call_status := dbms_pipe.send_message('mail service');

> end;

> procedure get(dest out varchar2,subject out varchar2,mesg out varchar2)

> is

> call_status integer;

> begin

> call_status := dbms_pipe.receive_message('mail service');

> dbms_pipe.unpack_message(dest);

> dbms_pipe.unpack_message(subject);

> dbms_pipe.unpack_message(mesg);

> end;

> end;

>

> 2) create a pro*c program

>

> #include <stdio.h>

>

> EXEC SQL INCLUDE sqlca;

> EXEC SQL BEGIN DECLARE SECTION;

> char sub[50];

> char dst[100];

> char msg[900];

> char user[20];

> char password[20];

> char SCmdLine[400];

> EXEC SQL END DECLARE SECTION;

> long SQLCODE;

>

> main()

> {

> strcpy(user, "SCOTT");

> strcpy(password, "TIGER");

> EXEC SQL CONNECT :user IDENTIFIED BY :password;

> EXEC SQL WHENEVER SQLERROR DO break;

>

> while(1) {

> EXEC SQL EXECUTE

> BEGIN

> mail.get(:dst,:sub,:msg);

> END;

> END-EXEC;

> msg[900]='\0';

> sprintf(SCmdLine,"mail -s%s -e%s -t%s",sub,dst,msg);

> system(SCmdLine);

> }

> }

>

> Change the OS call so that it reflect your email installation

> sprintf(SCmdLine,"<EMAIL PROGRAM> -s%s -e%s -t%s",sub,dst,msg);

>

> now you can send email from Oracle using :

>

> SQL> exec mail.send('xxx.yyy_at_aaa.com','subject','message body');

>

> Have fun

> Frederic

Anonymous <nobody_at_newsfeeds.com> wrote in message news:38295a59_3_at_anonymous.newsfeeds.com...
> Hi,
>
> Does anyone know if we can send an e-mail in a stored procedure ?
> I'm using Oracle 8.05 under NT Server 4.0.
>
> Thank !
>
> san_at_a2c.fr
>
>
>
> --------== Posted Anonymously via Newsfeeds.Com ==-------
> Featuring the worlds only Anonymous Usenet Server
> -----------== http://www.newsfeeds.com ==----------
Received on Thu Nov 11 1999 - 16:26:05 CST