Re: Email enable Oracle WGS 7.3.3.4

From: Dan Richards <dan_at_eurographics.co.uk>
Date: Tue, 01 Jun 1999 15:21:17 +0100
Message-ID: <3753EC5C.637103B3@eurographics.co.uk>

Hi

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

"Fred J. Leskowitz" wrote:

> I need to create email messages
> with PL/SQL code (no big deal there) and
> send them via email.
> I have Netscape MailServer at my site.
> I have read about Oracle "Glue" - offers
> API support to EXECMAIL - anyone with experience
> with that, or suggestions on other
> methods available?
> Thanks!
>
> --
> Fred J. Leskowitz <fred_at_micropat.com> Voice: +1.203.466.5055
> Oracle Database Programmer, MicroPatent Fax: +1.203.466.5054
> http://www.micropat.com
Received on Tue Jun 01 1999 - 09:21:17 CDT