In Oracle7 there used to be an implementation of dbms_pipes that did the job. It was called "plex" and was available freely from Oracle site. But I bet nowadays somebody has a better solution... dbms_pipes is still the way, I think, but there should be a newer implementation than plex.

I would like to automatically send an email  triggered by a certain
change in the database (UNIX and/or NT server operating system).

Ans for question2:

For sending email from Oracle8, you need to write an external procedure
and then use dbms_alert to trigger it automatically. Here is a version of an
external procedure that I've developed on Intel Solaris 2.6:
*****************************************************************************
** #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);
    }

}

*******************************************************************************

Mladen Gogala

Ans2 for question2:

Please be careful with this. dbms_alert may get you in all sorts of unwanted
locking trouble. I think the use of  dbms_pipe is preferrable in this case
to get info from one DB session into another. This package does not have any
locking problems.

Regards, Gerrit-Jan Linker