Re: Using a trigger to send an e-mail

From: <mgogala_at_rocketmail.com>
Date: Mon, 07 Dec 1998 21:36:36 GMT
Message-ID: <74hhp4$9s2$1@nnrp1.dejanews.com>

In article <74gt0p$iiu$1_at_sunrise.pg.gda.pl>,   "Piotr Kolodziej" <pkol_at_otago.gda.pl> wrote:
> David Mortenson wrote in message =
> <36672368.84149829_at_news.grnvle1.sc.home.com>...
> > So far, I have created a trigger that uses UTL_FILE to create an
> >e-mail formatted text file and have a shell script that pipes all of
> >the text files through sendmail.
> > This works o.k., but I would like something more robust and portable
> >to NT. I am thinking of writing a Pro*C daemon to poll the database
> >table directly, to avoid using UTL_FILE and adding error checking and
> >e-mail status updates. Other than responsiveness (immidate action
> >rather than polling), can you think of other reasons to consider
> >DBMS_PIPE?
> >
>
> First, I prefer to place code which performs OS-related actions in few=20
> well defined locations. It helps porting apps on different platforms.=20
> UTL_FILE interacts with OS, howerver with some transparency.
> Next, it is better to avoid handling OS-related exceptions /privileges,
> disk space etc./ at the trigger level. So I prefer dbms_pipe.
>
> --=20
> Piotr Kolodziej pkol_at_otago.gda.pl
> Just my private opinion.
>
>

Here is a small program that can be used for sending email from Oracle8 database:

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <unistd.h>
#include <errno.h>
#include <oci.h>
#define BUFFSIZE 0x10000
#define MAILER "/usr/bin/mail"

int

email (char *address, int addrlen, short addrind,
       char *subject, int subjlen, short subjind,
       char *message, int msglen, short msgind)
{
  char *email[3] =              /* mail command to exec */

  {"mail", NULL, NULL};

  int pfd[2];
  pid_t pid = 0;
  int status = 0;
  int wrtn = 0;

  char buffer[BUFFSIZE];        /* Message buffer to format the message. This
                                   avoids multiple calls to 'write' system
                                   service. This is always a performance
                                   improvement, at the expense of somewhat
                                   larger address space.
                                */

/*

   Check the arguments and fix them if they are NULL. */
  if ((addrind == OCI_IND_NOTNULL) && (addrlen > 0))     email[1] = address;
  else
    return (-1);
  if ((subjind == OCI_IND_NULL) || (subjlen <= 0))     subject = " ";
  if ((msgind == OCI_IND_NULL) || (msglen <= 0))     message = " ";

/* Format the message */

  sprintf (buffer, "Subject:%s\n%s", subject, message);

/*

   Open a pipe and redirect system input to the pipe. */
  pipe (pfd);
  close (0);
  dup (pfd[0]);

  if ((pid = fork ()))
    {
/*

   Parent process writes the message to the pipe,closes the pipe    and waits for the child process to complete. */

      wrtn = write (pfd[1], buffer, strlen (buffer) + 1);
      close (pfd[1]);
      if (wrtn == -1)
        return (errno);
      waitpid (pid, &status);
      return (status);

    }
  else
    {
/*

   Child process executes mailer and exits. */

      status = execvp (MAILER, email);
      if (status < 0)
        return (errno);

    }

}

And belonging sql:
create or replace function email(address in varchar2,

                                 subject in varchar2 default NULL,
                                 message in varchar default NULL)

return binary_integer
as

   external
   library testlib1
   name "email"
   language c
   parameters (
   address by reference string,
   address length sb4,
   address indicator sb2,
   subject by reference string,
   subject length sb4,
   subject indicator sb2,
   message by reference string,
   message length sb4,
   message indicator sb2,
   return sb4);

You should link it into a shared library (*.so on Solaris 2.6), map it into Oracle by using "create library" command and then create function by using the SQL command shown above. It works on my system. May the force be with you!
--
Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Dec 07 1998 - 15:36:36 CST