Re: CODE to send mail from Oracle

Hello Fredic,

I've run the first package statement and it's ok but for the "body" statement, i received the error.
MGR-00072: Warning: PACKAGE BODY MAIL created with compilation errors.

What is wrong? I, actually want to create a trigger to send an email out upon insertion of a record. Is it fine to call that mail.exe? My configuration Oracle NT 8.0.5. Mailserver - Lotus Notes.

thanks
marcus

In article <01bf2b98$d8221980$3e35a99e_at_gal159a>,   "Frederic DEBRUS" <frederic.debrus_at_ces-cdr.be> wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 11 1999 - 01:30:49 CST