sending mail

If you are using oracle 8 you have an option of using external procedures to do this. It is also possible for you to use Enterprise manager to do this.

If you use Oracle 8i release2, there's a SMTP component that you can use to send mail.
If you use normal 8i, or even older version, what you need to do is:
1. Use C create a shared lib that send mail.
2. Create an procedure that call this C library. This is called external proceduer in Oracle.

Then you can sendmail through that procedure.

Sorry, that was intended to be a comment!

Search for 'mail' in the archive.
You will find many good answers how to implement mail from PL/Sql.

Here is an example procedure you can call at the end of your procedure running...it uses orasend and the host command...

PROCEDURE Mail_Warning( send_to VARCHAR2) IS
  the_username VARCHAR2(40);
  the_password VARCHAR2(40);
  the_connect  VARCHAR2(40);
  the_command  VARCHAR2(2000);
BEGIN
  /*
  ** Get Username, Password, Connect information
  */
  Get_Connect_Info(the_username,the_password,the_connect);
  /*
  ** Concatenate together the static text and values of
  ** local variables to prepare the operating system command
  ** string.
  */
  the_command := 'orasend '||
      ' to='||send_to||
      ' std_warn.txt '||
      ' subject="## WORK DONE ##"'||
      ' user='||the_username||
      ' password='||the_password||
      ' connect='||the_connect;
 
  Message('Sending Message...', NO_ACKNOWLEDGE);
  Synchronize;
  /*
  ** Execute the command string as an O/S command The
  ** NO_SCREEN option tells forms not to clear the screen
  ** while we do our work at the O/S level "silently".
  */
  Host( the_command, NO_SCREEN );
  /*
  ** Check whether the command succeeded or not
  */
  IF NOT Form_Success THEN
    Message('Error -- Message not sent.');
  ELSE
    Message('Message Sent.');
  END IF;
END;

Jrifts,

I can see that you have posted it directly from developer 6 forms online help for host command. Did you ever try it out??? What is orasend and where do you get it from. (If there is one!)

crsankar

CRSANKAR   :
Do you have a problem with my comment/answer?

It works.  Try it out.

And if you are having problems, perhaps the notes below might help.

A change in your tone (and an apology) would be appreciated.
regards,JT

Pcorreya:
Here are some notes from technet on the subject.  Note the importance of the file you are sending (you asked about it on another thread...).  I hope this helps you.

Orasend: Command Line Mailer
In addition to the procedures discussed in this chapter, Oracle Office has Orasend, a command line interface for sending mail. This utility accesses Oracle Office send functions from the operating system. Orasend provides access to the following Oracle Office features:

attaching binary and text files
sending to a list of recipients
using To, Cc, Bcc, and Fcc fields
including/excluding the recipients list
assigning expiration dates
optionally validating recipients
assigning a specific date for delivery
remote operations
Fields
Users compose the message in a text file, to which they can attach other text and binary files. To run Orasend, users enter orasend along with a number of fields. For the arguments that are set to Yes or No, you can also use Y and N. These settings are not case sensitive. The following list describes these fields.
filename

Required field that indicates the name of the text file containing the message. Unlike the other fields, the filename is entered without a preceding field name. Users simply enter the name of the file.

to=

Required field that indicates the intended recipients. Entry consists of a character string (up to 240 alphanumeric characters). The sender can include public aliases and mail lists and, if the sender is an Oracle Office user, private aliases and mail lists. If there is more than one mail name, each mail name must be separated by a space or comma and the entire string must be enclosed in double quotes.

cc=

Indicates the users who should receive a carbon copy of the message. The guidelines that apply to the to= field also apply to this field.

bcc=

Indicates the users who should receive a blind carbon copy of the message. Recipients listed in this field remain anonymous to other recipients. The guidelines that apply to the to= field also apply to this field.

subject=

Indicates the subject of the message. Users can enter any text string up to 240 characters. If there are spaces in the Subject text, the string must be enclosed in double quotes.

replyto=

Indicates the users who will receive a reply if different from the sender. The parameters that apply to the to= field also apply to this field.

attach=

Indicates that there is an attached file. Users can enter any valid filename in this field. The default file type is text. If the file is not text, you must use the atype= field.

atype=

Indicates the type of file that is attached. Valid entries are B for binary and T for text. If no type is entered, the system assumes that it is a text file.

connect=

Indicates the SQL*Net database connect string that identifies the appropriate database for a remote login. You must use the password= field with this option.

For example, a TCP/IP connect string could be:


T:impala:OFFICE
user=

Indicates the username corresponding to the mailbox being accessed. Used only if the account belongs to someone other than the user accessing it, or if the user is performing a remote login. You must use the password= field with this option.

password=

Indicates the Oracle Office remote password for the mailbox being accessed. This field is required with the user= and connect= fields.

defer=

Indicates that the message will not be sent until a specified date and time. If the user does not include this field, the message is sent immediately. See the date format later in this section for information about how to indicate a date.

expire=

Indicates that the message will expire on the specified date. If the user does not include this field, the message will not have an expiration date. See the date format later in this section for information about how to indicate a date.

validate= [Yes/No]

Indicates whether the user wants to validate recipients when sending the message. The default entry for this argument is No.

verbose= [Yes/No]

When set to No, this argument suppresses the messages that are normally displayed to inform you whether the file has been sent. This argument becomes useful when you access Orasend from an application such as Oracle Forms. The default setting is Yes.

help= [Yes/No]

When set to Yes, this argument displays helpful information about using Orasend. The default setting is No.


Requirements
The only required fields are to= and the filename. There is no required order of fields and the system is not case sensitive. Whenever a date is required in a field, it must appear in the following format:

"DD-MON-YY[YY]  24HH:MI:SS"      
For example:


"28-MAR-92  14:00:00"  
Only the month and day are required to send the message; if the other components of the date are not entered, the system uses their default values (current year, 12:00:00). Hours should be indicated using the 24 hour format. If two digits are used to indicate the year, the system assumes the current century. For instance, 88 is assumed to indicate 1988. If you include the time, you must enclose the entire string in quotes.


Examples
You would enter the following information at the command line to send the file test.unx to CKING, JCLARK, and SADAMS, along with the attached text file read.me:

orasend test.unx to="cking jclark sadams" ATTACH=read.me
You would enter the following information at the command line to send the file INFO to CKING with an expiration date, a deferred delivery date, and a list of recipients:


orasend to=cking expire=01-Jan-89 include=y defer=01-dec INFO
You would enter the following information at the command line to send the file Meet to JCLARK with a deferred delivery date, no list of recipients, and no validation of recipients.


                  orasend to=jclark Meet include=N DEFer="01-DEC-1990 9:15:00" validate=n
When the message is sent, Orasend prints a message to that effect.

Hope this helps,

JTrifts

hmmm...I apologise for my tone CR

I thought I cancelled my first posting...but apparently it went through.  

I reacted a bit too quickly to your remark and should have ignored it.

At any rate...there is a utility, it works and PC can use it.

Regards,

JT

JT,

Sorry for my comments. I apologize. But do you agree that your soultion will work only if you have oracle office. Anyways, dont take it personally. I am really sorry for the comment I made.

Rgds,
crsankar

JTrifts

Please note that I am not ignorant about this problem. I have been trying to do this for some time. I have partly succeeded using an external procedure call to MapiSendMail in mapi32.dll. But I do not know what parameters to pass to this function. Maybe you or somebody else can throw some light on this subject.

Thanks & Rgds,
crsankar

An alternative would be to use OLE to bring up a word session, say using a template doc, and then use VB/MAPI to send the message.

If you have trouble with VB code, one way around it is to record a macro in Word doing the type of thing you expect you need, and then look at the VB code in the macro.  Add your own processing to it and away you go.

You could add a WBP trigger that processes as follows:

- start the OLE application (i.e. open a word doc
- handle on applications documents
- create a new doc based on a selected template
- handle on document variables collection
- add set variables for recipient, subject, message
- run a standard macro to do something with the id
- release the OLE objects

Sorry, but I cannot give you the code for this, but there is something similar in one of the VB in a nutshell books you might want to reference.

Please note that you will need the OLE libraries installed with Oracle for the complete use of OLE.

JT

CRSANKAR,

Thanks for your comment:

Is this the param list you seek?

Declare Function MapiSendMail Lib "MAPI.DLL" Alias "BMAPISendMail" (ByVal Session&, ByVal UIParam&, Message As MapiMessage, Recipient As MapiRecip, File As MapiFile, ByVal Flags&, ByVal reserved&) As Long

Using the MAPI function MapiSendMail. This function takes seven arguments. The first argument is the session handle from the MapiLogon function. The second argument is the handle of the parent window (I passed a 0 which causes the editing dialog, if displayed, to be application modal). The third argument is the MapiMessage structure that was filled in the wrapper function. The fourth argument is the array of MapiRecip structures that was filled in the  wrapper function. The fifth argument is the array of MapiFile structures that was filled in step two of the wrapper function. The fifth argument contains flags which I do not use. The last argument is reserved for future use.


e.g.

llMapiRtn = MapiSendMail(llMapiSession, 0, luMapiMsg,
lauMapiRecipDesc(0), lauMapiFileDesc(0), 0&, 0&)
    If llMapiRtn <> SUCCESS Then
       gsExclaim "Mail send error: " & llMapiRtn
    Else
        gMapiSend = True
    End If
   

Please see http://www.imginfo.com/devonly/devnews/summer1995/burns3.htm
for a complete step by step example...

JT

JT

It appears that I will need Oracle Office, where do I get this from. Is it something I install off the Oracle CD.

Thanks
Pat

Here is a procedure for sending mail in unix
 

Overview
-------

This article describes how to generate e-mail messages within PL/SQL
routines.  The example code is intended for UNIX systems with the
sendmail command.
 
Caution:  This application cannot be called directly from SQL since the
          packaged functions do not have the appropriate PRAGMAs.  You
          must use an anonymous PL/SQL block.


Preparation
-----------

Perform the steps below to setup the necessary files.

     Note: When testing on your machine, the code generates simple
           e-mail messages for demonstration purposes.

1.  Read the main comment block of maildaemon.pc and make appropriate
    changes.
   
2.  Read the main comment block of maildaemon.sql and make appropriate
    changes.

3.  Connect to SQL*Plus. If not already setup, GRANT EXECUTE ON DBMS_PIPE TO
    where userid is public or the schema owner of the package.

4.  Run maildaemon.sql to setup dependencies.

5.  Copy proc.mk (demo_proc.mk on v8 databases) into directory and issue
    the following make line to build the executable for maildaemon:

        make -f proc.mk build EXE=maildaemon OBJS=maildaemon.o
        PROCFLAGS="sqlcheck=semantics parse=full userid=scott/tiger"

    Note: Replace userid=scott/tiger with the schema to build under.

6.  Run the maildaemon executable.

7.  To test, modify the code below and run from SQL*Plus:

     declare
       dummy number;
     begin
       maildaemon.setauditon;
       dummy:= maildaemon.email_msg1( 'yourid@yourcompany.com' );
       dummy:= maildaemon.email_msg2( 'yourid@yourcompany.com', 'scott',
               to_char( sysdate, 'dd-Mon-yyyy hh:mi:ss' ));
       maildaemon.setauditoff;
       maildaemon.stop;
     end;
     /


-------------------------------------------------------------------------
maildaemon.sql
-------------------------------------------------------------------------

rem file: maildaemon.sql
rem last modified: 10/15/97
rem
rem This source file generates dependencies for the maildaemon executable
rem such as logging as well as the PL/SQL package interface to communicate
rem with the maildaemon Pro*C application.
rem
rem Please note: this is just a sample.  You will need to modify/replace the
rem email_msg1() and email_msg2() functions in the maildaemon package.
rem Both functions have been provided as simple demonstrations on how to
rem write an interface.  Consult the Application Developers Guide for more
rem information on using the DBMS_PIPE package.

rem table: emailaudit
rem purpose: contain auditing messages from the maildaemon Pro*C application
create table emailaudit
(
  msgid   number constraint msgid_pk primary key,
  msgtype varchar2( 20 ),
  msgstat varchar2( 100 )
);

rem sequence: emailmsgseq
rem purpose: to allow maildaemon Pro*C application to generate unique message
rem   identifiers for opening temporary files and auditing.
create sequence emailmsgseq;

rem package: maildaemon
rem purpose: provide a PL/SQL interface to generate e-mail messages
create or replace package maildaemon as
  /* setauditon( )
   * procedure
   * parameters:  timeout: timeout factor for informing the maildaemon exe
   * exceptions:  -20030: error sending message to maildaemon exe
   * description: turn on auditing in the maildaemon exe
   */
  procedure setauditon( timeout number default 10 );

  /* setauditoff( )
   * procedure
   * parameters:  timeout: timeout factor for informing the maildaemon exe
   * exceptions:  -20030: error sending message to maildaemon exe
   * description: turn off auditing in the maildaemon exe
   */
  procedure setauditoff( timeout number default 10 );

  /* stop( )
   * procedure
   * parameters:  timeout: timeout factor for informing the maildaemon exe
   * exceptions:  -20030: error sending message to maildaemon exe
   * description: shutdown the maildaemon exe
   */
  procedure stop( timeout number default 10 );

  /* email_msg1( )
   * function
   * parameters:  emailaddr: email address to send email to
   *              timeout: timeout factor for informing the maildaemon exe
   * returns:     return code from mail daemon call
   * exceptions:  -20010: maildaemon had an error during sending email
   *              -20011: error during sending message to maildaemon exe
   *              -20012: message returned from maildaemon other than done
   *              -20013: maildaemon returned an error code other than 0
   * description: generic sample to demonstrate a simple interface to the
   *   maildaemon exe
   */
  function email_msg1( emailaddr in varchar2, timeout number default 10 ) return number;

  /* email_msg2( )
   * function
   * parameters:  emailaddr: email address to send email to
   *              userid: userid to place in the mail text
   *              timestamp: timestamp to place in the mail text
   *              timeout: timeout factor for informing the maildaemon exe
   * returns:     return code from mail daemon call
   * exceptions:  -20010: maildaemon had an error during sending email
   *              -20011: error during sending message to maildaemon exe
   *              -20012: message returned from maildaemon other than done
   *              -20013: maildaemon returned an error code other than 0
   * description: generic sample to demonstrate a simple interface to the
   *   maildaemon exe by passing parameters
   */
  function email_msg2( emailaddr in varchar2, userid in varchar2, timestamp in varchar2,
      timeout number default 10 ) return number;
end maildaemon;
/

create or replace package body maildaemon as
  procedure setauditon( timeout number default 10 ) is
    retval number;
  begin  

    dbms_pipe.pack_message( 'AUDIT' );
    retval := dbms_pipe.send_message( 'maildaemon', timeout );
    if retval <> 0 then
      raise_application_error( -20030,
        'maildaemon: error sending audit command.  Status = ' || retval );
    end if;
  end setauditon;

  procedure setauditoff( timeout number default 10 ) is
    retval number;
  begin  

    dbms_pipe.pack_message( 'NOAUDIT' );
    retval := dbms_pipe.send_message( 'maildaemon', timeout );
    if retval <> 0 then
      raise_application_error( -20030,
        'maildaemon: error sending noaudit command.  Status = ' || retval );
    end if;
  end setauditoff;

  procedure stop( timeout number default 10 ) is
    retval number;
  begin  

    dbms_pipe.pack_message( 'STOP' );
    retval := dbms_pipe.send_message( 'maildaemon', timeout );
    if retval <> 0 then
      raise_application_error( -20030,
        'maildaemon: error sending stop command.  Status = ' || retval );
    end if;
  end stop;

  function email_msg1( emailaddr in varchar2, timeout number default 10 ) return number is
    retval  number;
    result  varchar2(20);
    cmdcode number;
    pipenm  varchar2(30);
  begin  
    pipenm := dbms_pipe.unique_session_name;
    dbms_pipe.pack_message( 'MSG1' );
    dbms_pipe.pack_message( pipenm );
    dbms_pipe.pack_message( emailaddr );
    retval := dbms_pipe.send_message( 'maildaemon', timeout );
    if retval <> 0 then
      raise_application_error( -20010,
        'maildaemon: error while sending email.  Status = ' || retval );
    end if;

    retval := dbms_pipe.receive_message( pipenm, timeout );
    if retval <> 0 then
      raise_application_error( -20011,  
        'maildaemon: error while receiving daemon response.  Status = ' || retval );
    end if;
 
    dbms_pipe.unpack_message( result );
    if result <> 'done' then
      raise_application_error( -20012,
        'maildaemon: error code returned from daemon other than done' );
    end if;

    dbms_pipe.unpack_message( cmdcode );
    if cmdcode <> 0 then
      raise_application_error( -20013,
        'maildaemon: error code returned from daemon ' || cmdcode );
    end if;

    return cmdcode;
  end email_msg1;

  function email_msg2( emailaddr in varchar2, userid in varchar2, timestamp in varchar2,
      timeout number default 10 ) return number is
    retval  number;
    result  varchar2(20);
    cmdcode number;
    pipenm  varchar2(30);
  begin  
    pipenm := dbms_pipe.unique_session_name;
    dbms_pipe.pack_message( 'MSG2' );
    dbms_pipe.pack_message( pipenm );
    dbms_pipe.pack_message( emailaddr );
    dbms_pipe.pack_message( userid );
    dbms_pipe.pack_message( timestamp );
    retval := dbms_pipe.send_message( 'maildaemon', timeout );
    if retval <> 0 then
      raise_application_error( -20010,
        'maildaemon: error while sending email.  Status = ' || retval );
    end if;

    retval := dbms_pipe.receive_message( pipenm, timeout );
    if retval <> 0 then
      raise_application_error( -20011,  
        'maildaemon: error while receiving daemon response.  Status = ' || retval );
    end if;
 
    dbms_pipe.unpack_message( result );
    if result <> 'done' then
      raise_application_error( -20012,
        'maildaemon: error code returned from daemon other than done' );
    end if;

    dbms_pipe.unpack_message( cmdcode );
    if cmdcode <> 0 then
      raise_application_error( -20013,
        'maildaemon: error code returned from daemon ' || cmdcode );
    end if;

    return cmdcode;
  end email_msg2;

end maildaemon;
/

-------------------------------------------------------------------------
maildaemon.pc
-------------------------------------------------------------------------

/************************************************************************
 * file: maildaemon.pc
 * last modified: 10/15/98
 *
 * This source code is written for the UNIX environment to allow PL/SQL
 * to generate e-mail.  Please note, the following code might not work on
 * your system due to configurations of the operating system or your
 * environment.  Please consult your systems administrator for more
 * information on specifics.
 *
 * Variables to be set prior to building:
 * mailhost: the mail application to generate email queuing.  Default is
 *    "/usr/lib/sendmail".
 * mailswitch: the mail application switches to pass to $mailhost.  Default
 *    is "-t".
 * userpass: the username/password to connect to the database.  Default is
 *    "scott/tiger"
 * logfile: the logfile to write system messages to.
 *
 * Functions to be modified:
 * main( ): will need to modify the message handling portion to handle the
 *    messages from the maildaemon package (PL/SQL).  The changes that need
 *    to be made are in the else if( ... ) portion with handling MSG1 and
 *    MSG2.
 * msg1( ): this is just a stub sample.  Replace this with appropriate code
 *    and change the call in main( ).
 * msg2( ): this is just a stub sample.  Replace this with appropriate code
 *    and change the call in main( ).
 ***********************************************************************/

/************************************************************************
 * System include files
 ***********************************************************************/
#include  <stdio.h>  
#include  <string.h>
EXEC SQL INCLUDE sqlca;    
   
/************************************************************************
 * Global variable declaration
 ***********************************************************************/
EXEC SQL BEGIN DECLARE SECTION;  
  char   *mailhost = "/usr/lib/sendmail";
            /* the mail host application to gen email requests */
  char   *mailswitch = "-t";
            /* switches to pass to $mailhost */
  char   *userpass = "scott/tiger";
            /* userid/password to connect to the database as */
  char   *logfile = "maildaemon.log";
            /* log file to write messages to */
  FILE   *loghnd = NULL;
            /* file pointer to log file */
  int     retval;
            /* return value for DBMS_PIPE send */
  int     calval;
            /* return value set from DBMS_PIPE receive */
  varchar pipeid[ 30 ];
            /* return pipe identifier */
  char    filename[ 128 ];
            /* filename to use for email */
  varchar command[ 20 ];
            /* system command received from DBMS_PIPE receive */
  char    syscommand[ 2000 ];
            /* hold system command for generating email request */
  varchar emailaddr[ 256 ];
            /* hold the email address for sending message to */
  int     auditing= 0;
            /* set whether auditing is to be done */
  varchar string1[ 256 ];
            /* hold string 1 passed from server */
  varchar string2[ 256 ];
            /* hold string 2 passed from server */
EXEC SQL END DECLARE SECTION;    
 
/************************************************************************
 * Function definition
 ***********************************************************************/

/*
 * conerr( )
 *  handle connection error
 */
void conerr( )
{
  char msgbuf[ 512 ]; /* message buffer */
  int  msglen; /* message buffer space used */
  int  maxmsglen; /* maximum message length */

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  sqlglm( msgbuf, &maxmsglen, &msglen );
  fprintf( loghnd, "maildaemon: error during connect to database\n" );
  fprintf( loghnd, "error reported: %.*s\n", msglen, msgbuf );
  fprintf( loghnd, "maildaemon: aborting...\n" );
  exit( 1 );
} /* end conerr( ) */

/*
 * sqlerr( )
 *  handle general SQL error
 *  does not cause maildaemon to abort
 */
void sqlerr( )
{
  char msgbuf[ 512 ]; /* message buffer */
  int  msglen; /* message buffer space used */
  int  maxmsglen; /* maximum message length */

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  sqlglm( msgbuf, &maxmsglen, &msglen );
  fprintf( loghnd, "maildaemon: error during processing\n" );
  fprintf( loghnd, "error reported: %.*s\n", msglen, msgbuf );
  fprintf( loghnd, "maildaemon: continuing...\n" );
} /* end sqlerr( ) */

/*
 * msg1( )
 *  stub function example 1 for sending an email.
 */
int msg1( )
{
  EXEC SQL BEGIN DECLARE SECTION;
  int   retcode = 0; /* return code */
  long  msgid; /* unique message id */
  FILE *msghnd = NULL; /* file handle to write email file */
  EXEC SQL END DECLARE SECTION;

  if( emailaddr.len == 0 )
  { /* null address passed */
    fprintf( loghnd, "maildaemon: null address specified to msg1( )\n" );
    retcode= 999;
    return( retcode );
  } /* end if */

  /* get the next sequence number for uniqueness */
  EXEC SQL WHENEVER SQLERROR GOTO sqlerror1;
  EXEC SQL SELECT emailmsgseq.nextval INTO :msgid FROM dual;

  /* generate the filename so it is unique and open the file */
  sprintf( filename, "emailmsg.txt.%ld", msgid );
  msghnd= fopen( filename, "w" );

  if( msghnd == NULL )
  { /* there was an error opening the output file */
    retcode= 1;
    if( auditing )
    { /* set audit trail */
      EXEC SQL INSERT INTO emailaudit VALUES( :msgid, 'msg1', 'maildaemon: status code of: ' || :retcode );
      EXEC SQL COMMIT;
    } /* end if */
    return( retcode );
  } /* end if */

  /* generate email */
  fprintf( msghnd, "To: %s\n", emailaddr.arr );
  fprintf( msghnd, "Subject: msg1 message type\n\n" );
  fprintf( msghnd, "\tmsg1 message type was called for emailing\n" );
  fprintf( msghnd, "\ngenerated by maildaemon\n" );

  /* close the file */
  fclose( msghnd );

  /* create the command line and send the message */
  sprintf( syscommand, "%s %s < %s", mailhost, mailswitch, filename );
  retcode= system( syscommand );

  /* remove the temporary file */
  unlink( filename );

  if( auditing )
  { /* set audit trail */
    EXEC SQL INSERT INTO emailaudit VALUES( :msgid, 'msg1', 'maildaemon: status code of: ' || :retcode );
    EXEC SQL COMMIT;
  } /* end if */

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  return( retcode );

  sqlerror1:
  retcode= 1;
  sqlerr( );
  return( retcode );
} /* end msg1( ) */

/*
 * msg2( )
 *  stub function example 2 for sending an email.
 */
int msg2( )
{
  EXEC SQL BEGIN DECLARE SECTION;
  int   retcode = 0; /* return code */
  long  msgid; /* unique message id */
  FILE *msghnd = NULL; /* file handle to write email file */
  EXEC SQL END DECLARE SECTION;

  if( emailaddr.len == 0 )
  { /* null address passed */
    fprintf( loghnd, "maildaemon: null address specified to msg2( )\n" );
    retcode= 999;
    return( retcode );
  } /* end if */

  /* get the next sequence number for uniqueness */
  EXEC SQL WHENEVER SQLERROR GOTO sqlerror2;
  EXEC SQL SELECT emailmsgseq.nextval INTO :msgid FROM dual;

  /* generate the filename so it is unique and open the file */
  sprintf( filename, "emailmsg.txt.%ld", msgid );
  msghnd= fopen( filename, "w" );

  if( msghnd == NULL )
  { /* there was an error opening the output file */
    retcode= 1;
    if( auditing )
    { /* set audit trail */
      EXEC SQL INSERT INTO emailaudit VALUES( :msgid, 'msg2', 'maildaemon: status code of: ' || :retcode );
      EXEC SQL COMMIT;
    } /* end if */
    return( retcode );
  } /* end if */

  /* generate email */
  fprintf( msghnd, "To: %s\n", emailaddr.arr );
  fprintf( msghnd, "Subject: msg2 message type\n\n" );
  fprintf( msghnd, "\tmsg2 message type was called for emailing\n" );
  fprintf( msghnd, "Userid of user: %s\n", string1.arr );
  fprintf( msghnd, "Timestamp of transaction: %s\n", string2.arr );
  fprintf( msghnd, "\ngenerated by maildaemon\n" );

  /* close the file */
  fclose( msghnd );

  /* create the command line and send the message */
  sprintf( syscommand, "%s %s < %s", mailhost, mailswitch, filename );
  retcode= system( syscommand );

  /* remove the temporary file */
  unlink( filename );

  if( auditing )
  { /* set audit trail */
    EXEC SQL INSERT INTO emailaudit VALUES( :msgid, 'msg2', 'maildaemon: status code of: ' || :retcode );
    EXEC SQL COMMIT;
  } /* end if */

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  return( retcode );

  sqlerror2:
  retcode= 1;
  sqlerr( );
  return( retcode );
} /* end msg2( ) */

void main( )
{
  /* open file and verify logging */
  loghnd = fopen( logfile, "a" );
  if( loghnd == NULL )
  { /* the logfile was unable to be opened */
    printf( "maildaemon: error opening logfile (%s)\n", logfile );
    exit( 1 );
  } /* end if */

  /* connect to the database */
  EXEC SQL WHENEVER SQLERROR DO conerr( );
  EXEC SQL CONNECT :userpass;
  fprintf( loghnd, "maildaemon: connected.\n" );

  /* loop until stop command given */
  EXEC SQL WHENEVER SQLERROR DO sqlerr();
  while( 1 == 1 )
  { /* inifinite loop */

    /* reset values */
    emailaddr.len = 0;
    string1.len= 0;
    string2.len = 0;

    /* get type of message from 'server' */
    EXEC SQL EXECUTE
    begin
      :calval := dbms_pipe.receive_message( 'maildaemon' );

      if :calval = 0 then
        dbms_pipe.unpack_message( :command );
      end if;
    end;
    END-EXEC;

    if( calval == 0 )
    { /* message received.  determine the command */

      command.arr[ command.len ]= '\0';

      if( !strcmp(( char * ) command.arr, "STOP" ))
      { /* 'server' specified to stop */
        fprintf( loghnd, "maildaemon: shutdown in progress...\n" );
        break;
      } /* end if */
      else if( !strcmp(( char * ) command.arr, "AUDIT" ))
      { /* set auditing on */
        fprintf( loghnd, "maildaemon: enable auditing...\n" );
        auditing= 1;
      } /* end else if */
      else if( !strcmp(( char * ) command.arr, "NOAUDIT" ))
      { /* set auditing off */
        fprintf( loghnd, "maildaemon: disable auditing...\n" );
        auditing= 0;
      } /* end else if */
      else if( !strcmp(( char * ) command.arr, "MSG1" ))
      { /* call for message 1 */
        /* retrieve the message */
        EXEC SQL EXECUTE
        begin
          dbms_pipe.unpack_message( :pipeid );
          dbms_pipe.unpack_message( :emailaddr );
        end;
        END-EXEC;

        /* copy into host variable */
        emailaddr.arr[ emailaddr.len ]= '\0';

        /* generate the email */
        retval= msg1( );

        /* reply with response */
        EXEC SQL EXECUTE
        begin
          dbms_pipe.pack_message( 'done' );
          dbms_pipe.pack_message( :retval );
          :retval := dbms_pipe.send_message( :pipeid );
        end;
        END-EXEC;
      } /* end else if */
      else if( !strcmp(( char * ) command.arr, "MSG2" ))
      { /* call for message 2 */
        /* retrieve the message */
        EXEC SQL EXECUTE
        begin
          dbms_pipe.unpack_message( :pipeid );
          dbms_pipe.unpack_message( :emailaddr );
          dbms_pipe.unpack_message( :string1 );
          dbms_pipe.unpack_message( :string2 );
        end;
        END-EXEC;

        /* copy into host variable */
        emailaddr.arr[ emailaddr.len ]= '\0';
        string1.arr[ string1.len ]= '\0';
        string2.arr[ string2.len ]= '\0';

        /* generate the email */
        retval= msg2( );

        /* reply with response */
        EXEC SQL EXECUTE
        begin
          dbms_pipe.pack_message( 'done' );
          dbms_pipe.pack_message( :retval );
          :retval := dbms_pipe.send_message( :pipeid );
        end;
        END-EXEC;
      } /* end else if */
      else
      { /* invalid command received */
        fprintf( loghnd, "maildaemon: illegal command... ignoring request.\n" );
      } /* end else */
    } /* end if */
    else
    { /* time out error occured */
      fprintf( loghnd, "maildaemon: timeout or other error while waiting for signal request.\n" );
    } /* end else */
  } /* end while */

  /* clean up and exit */
  EXEC SQL COMMIT WORK RELEASE;
  fprintf( loghnd, "maildaemon: shutdown.\n" );
  fclose( loghnd );
} /* end main( ) */
..

 

This is available in metalink