Pages

▼

Tuesday, June 5, 2012

Email Notification in Oracle APEX


Email Process in Oracle APEX.

1)       We need to configure the email  setting at the administration level of apex .
  • Log in to Oracle Application Express Administration Services. Application Express Administration Services".
  • Click Manage Instance.
  • Under Instance Settings, click Instance Settings.
  • Under Email, enter the following:
  • SMTP Host Address - Defines the server address of the SMTP server. By default on installation, this is set to local host. If you are using another server as an SMTP relay, change this parameter to that server's address.
  • SMTP Host Port - Defines the port the SMTP server listens to for mail requests. The default setting is 25.
  • Administration Email Address - Defines the from address for administrative tasks that generate email, such as approving a provision request or resetting a password.
  • Notification Email Address - Enter the email address to receive notification email messages for new workspace requests and change workspace requests. If not specified, no workspace request notification email messages are sent.
  • Click Apply Changes.
2)       Use the Email API provided by apex for sending the mails in the dev environment .
  • APEX_MAIL is the Apex API which can be used for sending the email notification in apex once the instance is configured.
  • This package is built on the UTL_SMTP package; hence UTL_SMTP must be installed and functioning in order to use APEX_MAIL.
  • There are three sub programs which are used in the package.
    • ADD_ATTACHMENT Procedure
    • PUSH_QUEUE Procedure
    • SEND Procedure

 ADD_ATTACHMENT Procedure
  • This procedure sends an outbound email message from an application as an attachment. To add                  multiple attachments to a single email, APEX_MAIL.ADD_ATTACHMENT can be called repeatedly for a single email message.
        APEX_MAIL.ADD_ATTACHMENT(p_mail_id                  IN    NUMBER,
                                                                    p_attachment                IN    BLOB,
               p_filename                  IN    VARCHAR2,
                      p_mime_type                 IN    VARCHAR2);
  • Parameter            
  • p_mail_id
The numeric ID associated with the email. This is the numeric identifier returned from the call to APEX_MAIL.SEND to compose the email body.
  • p_attachment
A BLOB variable containing the binary content to be attached to the email message.
  • p_filename
The filename associated with the email attachment.
  • p_mime_type
A valid MIME type (or Internet media type) to associate with the email attachment.





SEND Procedure


APEX_MAIL.SEND(
    p_to                        IN    VARCHAR2,
    p_from                      IN    VARCHAR2,
    p_body                      IN  [ VARCHAR2 | CLOB ],
    p_body_html                 IN  [ VARCHAR2 | CLOB ] DEFAULT NULL,
    p_subj                      IN    VARCHAR2 DEFAULT NULL,
    p_cc                        IN    VARCHAR2 DEFAULT NULL,
    p_bcc                       IN    VARCHAR2 DEFAULT NULL,
    p_replyto                   IN    VARCHAR2
);
Parameter            
  • p_to
Valid email address to which the email will be sent (required). For multiple email addresses, use a comma-separated list
  • p_from
Email address from which the email will be sent (required). This email address must be a valid address. Otherwise, the message will not be sent
  • p_body
Body of the email in plain text, not HTML (required). If a value is passed to p_body_html, then this is the only text the recipient sees. If a value is not passed to p_body_html, then this text only displays for email clients that do not support HTML or have HTML disabled. A carriage return or line feed (CRLF) must be included every 1000 characters.
  • p_body_html
Body of the email in HTML format. This must be a full HTML document including the <html> and <body> tags. A single line cannot exceed 1000 characters without a carriage return or line feed (CRLF)
  • p_subj

Subject of the email
  • p_cc
Valid email addresses to which the email is copied. For multiple email addresses, use a comma-separated list
  • p_bcc
Valid email addresses to which the email is blind copied. For multiple email addresses, use a comma-separated list
  • p_replyto
Address of the Reply-To mail header. You can use this parameter as follows:
  • If you omit the p_replyto parameter, the Reply-To mail header is set to the value specified in the p_from parameter
  • If you include the p_replyto parameter, but provide a NULL value, the Reply-To mail header is set to NULL. This results in the suppression of automatic email replies
If you include p_replyto parameter, but provide a non-null value (for example, a valid email address), you will send these messages, but the automatic replies will go to the value specified (for example, the email address).

Scheduling the Email.

In order to schedule the email outside of the application express  for example using the DMBS_SCHEDULER pakcgae or the DBMS_JOB package  .
We need to access in the following format .
for c1 in (
   select workspace_id
     from apex_applications
    where application_id = p_app_id )
loop
   apex_util.set_security_group_id(p_security_group_id =>
c1.workspace_id);
end loop;
Sample example which we used in oracle1 env to test the email functionality is :-
DECLARE
l_id NUMBER;
BEGIN
l_id := APEX_MAIL.SEND(
p_to => 'thakurdurgeshnandini@gmail.com',
p_from => ‘nandini.thakur@jnettechnologies.com',
p_subj => 'APEX_MAIL with attachment',
p_body => 'Please review the attachment.',
p_body_html => '<b>Please</b> review the attachment');
dbms_output.put_line(l_id);
END;

at

No comments:

Post a Comment