Sending EMAIL From Oracle

From: A Gentleman and Scholar <sschultz_at_SNOOPY.46TG.AF.MIL>
Date: Sun, 12 Mar 1995 10:15:39 -0700
Message-Id: <9503121741.AA31475@alice.jcc.com>

RE: Sending EMAIL from Oracle

I read on the list a request to be able to interface with Email. This was a week or so ago. We were in the middle of trying to send Email from the MIS applications to the EMAIL world.

We have done this by saving Email messages in an Oracle Table (description below) and using a PL/SQL script to process the messages. We place an entry in CRON to process the mail every 5 minutes. If the file email_script.lst is NOT found the process continues, if it is found, then you are already processing or you have unprocessed mail. To restart the mail process after checking why it bombed out, just delete the file.

CRON will process it again on the next pass. An Email message will only be sent once no matter how many times the rowid occures in table mis_email_in_progress.

The message table is structured after Oracle Mail in order to maintain compatability.

These scripts are for UNIX, SUN-OS 4.1.3, using sendmail, but the theory can be used on other platforms.

Questions or suggestions can be directed to:

sschultz_at_snoopy.46tg.af.mil or
jrogers_at_snoopy.46tg.af.mil

Note: # must be in 1st columns.

• mis_msgs -----------------------------

#!/bin/csh


# C shell script to be run by cron for unix user mis_mst

#     who has an ops$login Oracle login

#  source ~oracle7/.cshrc_${1} --- read the .cshrc of the

#                                  oracle user.  We use the

#                                  format .cshrc_<<sid>>

# The parameter passed by ${1} is the name of the Oracle sid
  if ! ( -f /tmp/email_script.lst ) then

      source ~oracle7/.cshrc_${1}
      sqlplus / @${HOME}/scripts/system/process_mis_email

  endif

• process_mis_email.sql ---------------------

  REM Oracle Script that creates and runs a unix script to   REM send mail via unix sendmail
  REM The rowids to process are stored in the mis_email_in_progress   REM table.

  VARIABLE EMAIL_COUNT number;
  EXECUTE select count(*) INTO :EMAIL_COUNT from mis_msgs;   DECLARE
  CURSOR email_cursor IS

      SELECT rowid
          FROM mis_msgs
             WHERE :EMAIL_COUNT     > 0;
      row_id varchar2(18);
  BEGIN
      OPEN email_cursor;
      LOOP
          FETCH email_cursor INTO row_id;
          IF email_cursor%NOTFOUND THEN
              EXIT;
          END IF;
          INSERT into mis_email_in_progress VALUES(row_id);
      END LOOP;
      CLOSE email_cursor;

  END;
  /

 commit;
  set head off
  set tab on
  set verify off
  set feedback off
  set termout off
  rem set linesize 255
  spool /tmp/email_script
  PROMPT #!/bin/csh
  SELECT '/usr/lib/sendmail -v '||who_to||' <<EOF'||chr(10)||

         'Subject: '||subject||chr(10)||
         text||chr(10)||'EOF'||chr(10) from mis_msgs
      WHERE rowid IN (select * from mis_email_in_progress);

  spool off;
  host awk -f remove_trailing_blanks.awk </tmp/email_script.lst  >/tmp/email_script;csh /tmp/email_script   delete from mis_msgs

      where rowid IN (select * from mis_email_in_progress);   truncate table mis_mst.mis_email_in_progress;   commit;
  host rm -f /tmp/email_script*
  exit;
 Content-Length: 1679
 X-Lines: 51
 Status: RO

• Table Descriptions ----------------------

  TABLE: mis_msgs

   Name                            Null?    Type
   ------------------------------- -------- ----
   TEXT                            NOT NULL VARCHAR2(2000)
   WHO_TO                          NOT NULL VARCHAR2(180)
   CC                                       VARCHAR2(120)
   BCC                                      VARCHAR2(120)
   FCC                                      VARCHAR2(120)
   WHO_FROM                                 VARCHAR2(30)
   REPLYTO                                  VARCHAR2(30)
   SUBJECT                                  VARCHAR2(120)
   OA_USER                                  VARCHAR2(30)
   PASSWORD                                 VARCHAR2(30)
   HOW_CONNECT                              VARCHAR2(80)
   EXPIRE                                   VARCHAR2(30)
   DEFER                                    VARCHAR2(30)
   VALID                                    CHAR(1)
   ATTACH                                   VARCHAR2(120)
   ATYPE                                    CHAR(1)
   VERBOSE                                  CHAR(1)
   LOGFILE                                  VARCHAR2(80)



  TABLE: mis_email_in_progress
   Name                            Null?    Type
   ------------------------------- -------- ----
   ROW_ID                                   VARCHAR2(18)






 ----------------------- remove_trailing_blanks.awk ----------------------

# Trailing cause Email to include trailing blanks
# on end of each line.
# This awk script wil strip trailing blanks from unix
# script file lines

  {str = $0;
   str_len = length($0);
   while ( substr(str,str_len,1) == " " )

       {
       str = substr(str,1,--str_len);
       }

   print str;
  } Received on Sun Mar 12 1995 - 12:41:21 CST