Re: How to send Email messages from plsql programs?

I am assuming the real question is that if there is some SNMP trap that you could link directly with the DB server that could 'push' mail out, rather than some process 'pull'-ing it out via pipes.

Is there any such way?

-M

Dan Richards wrote:

> Hi - I've posted this before, but it seems to be a popular question!
>
> I've done this on our site using pipes: it's a bit of a hack, but we're
> upgrading to Ora8 so I'm going to try and code it as a proper external
> procedure soon...
>
> I've created a package called PIPE_MAIL with the following procedures:
>
> PROCEDURE send_pipe (message IN varchar2, subject IN varchar2, mail_to
> IN varchar2)
> IS
> retval number;
> BEGIN
> dbms_pipe.pack_message(message);
> dbms_pipe.pack_message(subject);
> dbms_pipe.pack_message(mail_to);
> retval := dbms_pipe.send_message('trig_pipe');
> IF retval != 0 THEN
> raise_application_error(-20099, 'Can''t write to trig_pipe -
> return value='||to_char(retval));
> END IF;
> END;
>
> PROCEDURE read_pipe (message IN OUT varchar2, subject IN OUT varchar2,
> mail_to IN OUT varchar2)
> IS
> retval integer;
> BEGIN
> retval:=dbms_pipe.receive_message('trig_pipe',0);
> if retval>1 then
> raise_application_error(-20099, 'Can''t read from trig_pipe -
> return value='||to_char(retval));
> elsif retval = 1 THEN
> message:='trig_pipe is empty';
> else
> dbms_pipe.unpack_message(message);
> dbms_pipe.unpack_message(subject);
> dbms_pipe.unpack_message(mail_to);
> end if;
>
> END;
>
> PROCEDURE purge_pipe
> is
> begin
> DBMS_PIPE.PURGE('trig_pipe');
> end;
>
> END;
>
> I can send a message from triggers/stored procedures using:
> pipe_mail.send_pipe('Body of mail','Subject','name_at_unixbox');
>
> .and this is picked up by a cron script run every 10 minutes:
> :
> ### define your Oracle environment here so that it can run from
> cron, including TERM
>
> empty="Run it once"
>
> while [ "$empty" != "(standard input)" ] ; do
> sqlplus -s username/pwd @read_pipe.sql
> read_pipe.mail_it
> empty=`cat read_pipe.message|grep -l "trig_pipe is empty"`
> done
>
> read_pipe.sql is:
>
> set timing off
> set time off
> set feedback off
> set heading off
> set echo off
> ttitle off
> btitle off
>
> -- below prevents wrapping
> set linesize 999
>
> variable message varchar2(1000);
> variable subject varchar2(100);
> variable mail_to varchar2(200);
>
> execute bv.pipe_mail.read_pipe(:message,:subject,:mail_to);
>
> spool read_pipe.message
> select rtrim(:message) from dual;
> spool off;
>
> spool read_pipe.mail_it
> select decode(:message,'trig_pipe is empty','echo Pipe is empty'
> ,'cat read_pipe.message|mail -s"'||:subject||'"
> "'||:mail_to||'"')
> from dual;
> spool off;
>
> exit
>
> 'hope this makes sense!
>
> Dan
>
> deb wrote:
>
> > I want to send an email containing error messages in the exception
> > handler...how can I do that....
> >
> > Thanks/deb
Received on Wed Jun 02 1999 - 18:45:52 CDT