Re: Send mail by Stored Procedure

This way you need to have a daemon running on the system (outside oracle) to check for any messages in the oracle pipe that PL/SQL procedure has created. This is less reliable as daemon may not have been running.

if there is way in oracle to invoke an external procedure ( an exe) with a (mail) message being passed to it, we can avoid the daemon process.

experts on external procedures must have an answer to this!!

Nanda

In article <80ffrh$3jp$1_at_plo.sierra.com>,   "Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote:
> 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 ==----------
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 12 1999 - 12:21:03 CST