Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: pop mail from plsql - cgimail.zip (0/1)

Re: pop mail from plsql - cgimail.zip (0/1)

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 31 Aug 1998 14:39:23 GMT
Message-ID: <35ebaff3.3059569@dcsun4.us.oracle.com> 





On 29 Aug 1998 01:14:18 GMT, SHL9_at_cornell.edu (Steve) wrote:


>I'm looking for a method to send email to a pop server from a Windows NT 
>Workgroup server via a database trigger or plsql procedure. Any ideas out 
>there?
> SHL9_at_cornell.edu 






Another solution from Tom Kyte's Bag O' Tricks....



Hope this helps.



chris.







E-Mail from Oracle7.3 and higher on NT.



Table Of Contents.... 


What is This 


Installing, and Using 


Technically, How it works 






Sending Mail from the DB on NT





This utility, which works with version 7.3.3 of the database and up,
supports the sending of email using SMTP from the Oracle database
using PL/SQL. The sending of email is done in a transactional fashion
(so it even works from triggers and such). When you commit, the mail
is sent, when you rollback the mail is 'unsent' (not sent actually). 
This package is specifically for Windows NT. The one restriction with
this package is that the Oracle database needs to be on the same
machine with a webserver and this webserver must support CGI-BIN
scripting. 



This package makes use of the following Oracle Technologies 



UTL_FILE is used to generate the email into a temporary file 
UTL_HTTP is used to actually send the email 
DBMS_JOB is used to provide 'transactional' support and deferred
execution (the sending of email can be slow, to wait for the email
program could be 1 second or 10 seconds, this removes this wait from
the pl/sql program and puts it in the background) 






Installing and Using this package





This package consists of the following files: 
blat15i.zip -- version 1.5 of the public domain utility BLAT. It will
need to be unzipped into some place and 'installed'. It comes with
directions in the zip file. 


cgimail.exe, cgimail.c, Makefile -- A cgi-bin wrapper for BLAT making
it possible to easily call BLAT from the web to send mail. We use
UTL_HTTP to invoke a URL, this URL will call cgimail, cgimail will
call BLAT. The Makefile and C source code is included for your
convienence, the executable cgimail.exe is all that is really needed. 
cgimail.sql -- the pl/sql code that interfaces to BLAT. It will create
a sequence, a package specification, and a package body. 
index.html -- this file 


Doing the install


It takes the following 6 steps to install this utility. 
Install Blat 


Copy cgimail.exe 


Check some INIT.ORA parameters 


Map a CGI-BIN directory in your WebServer 
Edit cgimail.sql 


Execute cgimail.sql in sql*plus 


Each of these steps is described in detail below 







Install Blat


BLAT, the public domain utility, must be installed and verified
working before proceeding. Please unzip blat into a directory and view
the README file. Please test BLAT before proceeding to ensure you can
send email from your NT server to someone using BLAT.


 



Copy cgimail.exe


To the directory you installed BLAT into.


 



Check some INIT.ORA parameters


This utility uses UTL_FILE and DBMS_JOB. Both of these packages must
be enabled in your database prior to use. Remember, init.ora settings
are read upon database startup, if you change any of these, you must
stop and restart the database before they take effect. The relevant
parameters are: 


utl_file_dir = <some_directory>


For example, I have mine set to: 


   utl_file_dir = c:\temp


This will allow me to use my c:\temp directory to write temporary
email files to. You can create a special directory or whatever to hold
outgoing emails. This directory must be listed in the utl_file_dir
setting. See the Oracle Server Application Developers Guide for more
information on setting this parameter in your init.ora file. 



job_queue_processes = N


This setting controls how many concurrent background jobs may be
executing at once. In most cases a setting of job_queue_processes = 1
will be sufficient. If you are using replication, this parameter is
probably already set for you. N must be greater then zero for this
utility to work. 


job_queue_interval = N


This setting controls how frequently the job queue is looked at for
new jobs. It is specified in seconds. A setting of job_queue_interval
= 60 (every minute) is usually sufficient. 







Map a CGI-BIN directory in your WebServer
This is webserver specific but assuming you are using the Oracle
Webserver, you would simply create a virtual directory (for example
/mail/) to be mapped to the physical directory where BLAT and
cgimail.exe is installed. 


The goal here is to allow the URL http://localhost/mail/cgimail be a
recognized URL. The webserver must be able to find cgimail.exe and
cgimail.exe must be able to find BLAT. To test in a webbrowser if you
have the correct URL, you would just enter 



   http://localhost/mail/cgimail



without the question mark in the location field and hit enter. If a
page comes back that says 'error: query string not set', then you have
done this correctly! To get the ERROR is correct. If you get 404-URL
Not found, then you haven't mapped this correctly.


 



Edit cgimail.sql


This file contains all of the code needed to send mail from the
database. You need to set 2 variables before compiling this package.
These variables default to NULL and if not set, this pacakge will not
work. They are: 


g_tmp_dir must be set to the directory you specified in the
utl_file_dir setting above. It should match in case and spelling
exactly. For example, if you set 


    utl_file_dir = c:\temp


then you should set 


    g_tmp_dir constant varchar2(25) default 'c:\temp';
You'll just be changing NULL to 'c:\temp' in this case. 



g_mailto_url must be set to the URL that can run the cgimail program.
For example, it might simply be: 


   g_mailto_url ... default 'http://localhost/mail/cgimail?'
This URL should end in a QUESTION MARK. To test in a webbrowser if you
have the correct URL, you would just enter 


   http://localhost/mail/cgimail



without the question mark in the location field and hit enter. If a
page comes back that says 'error: query string not set', then you have
the right URL. 







Execute cgimail.sql in sql*plus


or svrmgr or whatever tool you want. It will create a sequence used to
name temporary files with and a package 'mailpkg'. 
You should be set to go at this point. Assuming the above was done and
the job queues are running, you should be able to issue: 



SQL> exec mailpkg.send( 'user_at_domain', 'Hello World' )
SQL> commit;


This will send an email message to user_at_domain with the contents
'Hello World'. The commit is needed, the mail will not be sent until
you commit. 


To see that the job is queued, you can issue: 



SQL> exec mailpkg.send( 'someone_at_somewhere', 'Hello World' );



PL/SQL procedure successfully completed.



SQL> commit;



Commit complete.



SQL> column what format a40 word_wrapped
SQL> select job, last_date, failures, what from user_jobs;



       JOB LAST_DATE   FAILURES WHAT



---------- --------- ----------




         9


mailpkg.background('c:\temp\M0000184.txt

                                -t "tkyte_at_us.oracle.com"');





This shows you that the email is queued (and you can see the filename
of the email as well). The last_date column, since it is null, shows
the email has never been attempted to be sent and the job has never
failed. 


This query should be used occasionally to see if emails are 'stuck'.
If the webserver is unavailable or the mail can't be sent, we'll see
errors in here. Additional error messages will be found in the Oracle
ALRT file in the trace directory as well (when failures > 0, then
errors are written in the {ORACLE_SID}ALRT.LOG file in the trace
directory). 







HOW it works:





The main routine you are interested in is in the mailpkg 'send'
procedure. It has the following specification: 

    procedure send( p_to       in varchar2,
                    p_body     in long,
                    p_subj     in varchar2 default NULL,
                    p_from     in varchar2 default NULL,
                    p_cc       in varchar2 default NULL,
                    p_bcc      in varchar2 default NULL );


Where the inputs are as follows: 



Input Meaning 

p_to Comma separated list of who the email is to 
p_body Up to 32k of email text. 
p_subj OPTIONAL subject of the email 
p_from OPTIONAL what appears in the FROM string in the email 
p_cc OPTIONAL carbon copy, list of emails separated by commas  
p_bcc OPTIONAL blind carbon copy, list of emails separated by commas  





This procedure, send, works by 



Generating a unique filename using a sequence 
Opening that file and writing p_body to it 
Building the command line for BLAT using the other inputs 
Calling dbms_job.submit() to place the request to email the file in
the job queue. This request is not officially in the queue until you
commit. 


Most all of the errors returned from mailpkg.send will center around
the UTL_FILE package and creating the file. I have mapped the UTL_FILE
exceptions to informative error messages that will be returned by this
procedure. 



After mailpkg.send returns, the mail has not yet actually been sent.
In fact, the email will not be sent until you commit your transaction.
Shortly after you commit (as dictated by the job_queue_interval
setting), the routine 'background' in the mailpkg will be executed.
This routine will utilize the UTL_HTTP routines to actually have BLAT
run. We are using the webserver to run blat for us. This routine will
fail for one of 2 reasons typically: 



The webserver is down. You will see 'REQUEST_FAILED' error messages in
your Oracle ALRT file. Also, the failures column in the user_jobs
table will be greater then zero. 


Blat is unable to contact you SMTP server. You will see error messages
from BLAT in your ALRT file as well. Also, the failures column will be
incremented. 


Thats it really. We use UTL_FILE to write the email out. We use
DBMS_JOB to defer execution of the sending of email to a later date
(sending mail can be slow...). We use UTL_HTTP to send the mail using
BLAT.



 



Received on Mon Aug 31 1998 - 09:39:23 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US