 |
|
Sending e-mail from APEX
Oracle Tips by Burleson Consulting |
Oracle APEX provides the htmldb_mail
PL/SQL package to facilitate the sending of email messages directly
from online Oracle applications. OTN has a description of
using htmldb_mail
here.
The htmldb_mail packages does not
interface directly with your server sendmail utility.
Instead, the htmldb_mail package (a synonym for the
wwv_flow_mail package) stores the outbound e-mail
in a message queue that is managed by dbms_scheduler.
Some developers prefer using the
www_flow_mail package directly.
Prerequisites:
You will need the APEX application, Oracle
10g database, and you must enable the utl_smtp software.
You install the utl_smtp package and
Oracle Jserver by running the
following DBA scripts as SYSDBA (SYS user):
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql
The htmldb_mail package
The htmldb_mail package as a
send procedure that accepts the following arguments:
PROCEDURE SEND;
Argument Name Type
-------------------------
P_TO
VARCHAR2
P_FROM VARCHAR2
P_BODY VARCHAR2
P_BODY_HTML VARCHAR2
P_SUBJ VARCHAR2
P_CC
VARCHAR2
P_BCC
VARCHAR2
A sample Oracle email session from APEX
Sending an e-mail from APEX is trivial.
All you need to do is define a process (called, say "send_html_db_email")
with the proper arguments to htmldb_mail.send:
HTMLDB_MAIL.SEND(
P_TO => 'john@remote-dba.net',
P_FROM => 'info@remote-dba.net',
P_BODY =>
'Request for '||
:P1_CALL_TYPE||
' received by '||
:P1_CALLER_FULL_NAME||
' at '||
:P1_CALL_TIME||
chr(10)||
'Company name: '||
:P1_CALLER_COMPANY_NAME||
chr(10)||
'Caller Phone Number: '||
:P1_SUPP_PHONE_NBR||
chr(10)||
'Caller Keywords: '||
:P1_KEYWORDS||
chr(10)||
:P1_MESSAGE_TEXT
,
P_SUBJ =>
'Request for '||
:p1_call_type||' received.'
);
-- push the e-mail queue for immediate delivery:
begin
wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => '192.111.99.99',
P_SMTP_PORTNO => '25'
);
end;
SEE
CODE DEPOT FOR FULL SCRIPTS
In the example above, the message_text column
is defined as a varchar2(1000) and APEX will do the text wrapping
in the e-mail message.
To send multiple e-mails from a multiple select
LOV (list of values drop down),
click here.
Click here for details on
managing APEX Mail
Messages.
APEX developer support:
 |
For APEX development support just call to gat an
Oracle Certified professional for all APEX development
projects. |
For more information on this topic, see the
book "Easy Oracle HTML-DB Application Express
", the best book anywhere on expert APEX
development:
 |
Easy Oracle HTML-DB Application Express
By Michael Cunningham & Kent Crotty
Only $27.95
Buy it now and get the code depot download
|
|
