Re: How to send Email messages from plsql programs?

From: Dan Richards <dan_at_eurographics.co.uk>
Date: Wed, 02 Jun 1999 09:42:11 +0100
Message-ID: <3754EE63.AAE29A04@eurographics.co.uk>

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 - 03:42:11 CDT