Forgot your password?    
+ Reply to Thread
Results 1 to 5 of 5

Thread: How to send html email alert from oracle database??

  1. #1
    Oracle DBA
    Join Date
    Jul 2012
    Posts
    3

    Question How to send html email alert from oracle database??

    Hi All,

    Does anybody know "How to send html email alert from oracle database?"

    DB version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

    Thanks in advace,
    Regards,
    Saurabh
    Last edited by saurabhchaudhary; 09-28-2012 at 11:23 AM.

  2. #2
    Oracle DBA
    Join Date
    Jul 2012
    Posts
    3
    Did anyone find something on that, please update ?

  3. #3
    Administrator
    Join Date
    Oct 2011
    Posts
    13
    option for sending e-mail using the utl_mail package

    ALTER SYSTEM SET smtp_out_server = 'mailserver.domain.com';

    DECLARE

    vSender VARCHAR2(30) := 'sender@somewhere.com';

    vRecip VARCHAR2(30) := 'your.name@domain.com';

    vSubj VARCHAR2(50) := 'Enter the subject here';

    vMesg VARCHAR2(4000) := 'Enter the body';

    vMType VARCHAR2(30) := 'text/plain; charset=us-ascii';

    BEGIN

    utl_mail.send

    (vSender, vRecip, NULL, NULL, vSubj, vMesg, vMType, NULL);

    END;

    /

    There are several limitations in utl_mail for sending e-mail messages from inside Oracle: The utl_mail package can only handle a RAW datatyp, and hence a maximum value of 32k for a 32k mail message.


    Pradip K. | Sr. Oracle Apps DBA
    www.dbalounge.com
    Contact me: er.pradeepkr erpradeepkr
    A creative man is motivated by the desire to achieve, not by the desire to beat others.
    ---------------------------------------------------------------------------------------
    TAGS: DBA Lounge Provides Oracle DBA Training Services. This includes Online Training, Classroom based Training at Noida (Delhi NCR).
    Students come form Delhi,Gurgaon,Noida,Ghaziabad,Faridabad,Greater Noida.
    Trainings : Oracle Database,10G,11G,RAC,Apps,11i,R12,Fusion Middleware,WebLogic,OID,OBIEE,WebCenter.
    We plan to commence Oracle DBA Training at Chandigarh,Mohali,Panchkula,Bangalore,Jaipur,Pune, Mumbai,Jammu and Hyderabad soon.

  4. #4
    Oracle DBA
    Join Date
    Jul 2012
    Posts
    3
    Thanx pradeep, but it sends the mail in simple text format, not in HTML format.
    My question is "How to send this text format mail in the HTML format"

  5. #5
    Administrator
    Join Date
    Oct 2011
    Posts
    13
    Try this
    ===================
    create or replace procedure html_email(
    p_to in varchar2,
    p_from in varchar2,
    p_subject in varchar2,
    p_text in varchar2 default null,
    p_html in varchar2 default null,
    p_smtp_hostname in varchar2,
    p_smtp_portnum in varchar2)
    is
    l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection utl_smtp.connection;
    l_body_html clob := empty_clob; --This LOB will be the email message
    l_offset number;
    l_ammount number;
    l_temp varchar2(32767) default null;
    begin
    l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
    utl_smtp.helo( l_connection, p_smtp_hostname );
    utl_smtp.mail( l_connection, p_from );
    utl_smtp.rcpt( l_connection, p_to );

    l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
    chr(34) || l_boundary || chr(34) || chr(13) ||
    chr(10);

    ----------------------------------------------------
    -- Write the headers
    dbms_lob.createtemporary( l_body_html, false, 10 );
    dbms_lob.write(l_body_html,length(l_temp),1,l_temp );


    ----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp := '--' || l_boundary || chr(13)||chr(10);
    l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
    chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset ,l_temp);

    ----------------------------------------------------
    -- Write the plain text portion of the email
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_text),l_offset ,p_text);

    ----------------------------------------------------
    -- Write the HTML boundary
    l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
    chr(13) || chr(10);
    l_temp := l_temp || 'content-type: text/html;' ||
    chr(13) || chr(10) || chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset ,l_temp);

    ----------------------------------------------------
    -- Write the HTML portion of the message
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_html),l_offset ,p_html);

    ----------------------------------------------------
    -- Write the final html boundary
    l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset ,l_temp);


    ----------------------------------------------------
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
    utl_smtp.write_data(l_connection,
    dbms_lob.substr(l_body_html,l_ammount,l_offset));
    l_offset := l_offset + l_ammount ;
    l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
    end loop;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit( l_connection );
    dbms_lob.freetemporary(l_body_html);
    end;
    /
    show errors


    Pradip K. | Sr. Oracle Apps DBA
    www.dbalounge.com
    Contact me: er.pradeepkr erpradeepkr
    A creative man is motivated by the desire to achieve, not by the desire to beat others.
    ---------------------------------------------------------------------------------------
    TAGS: DBA Lounge Provides Oracle DBA Training Services. This includes Online Training, Classroom based Training at Noida (Delhi NCR).
    Students come form Delhi,Gurgaon,Noida,Ghaziabad,Faridabad,Greater Noida.
    Trainings : Oracle Database,10G,11G,RAC,Apps,11i,R12,Fusion Middleware,WebLogic,OID,OBIEE,WebCenter.
    We plan to commence Oracle DBA Training at Chandigarh,Mohali,Panchkula,Bangalore,Jaipur,Pune, Mumbai,Jammu and Hyderabad soon.

+ Reply to Thread

Similar Threads

  1. Managing alert for concurrent requests
    By dbaANKIT in forum Concurrent Processing Server / Workflow
    Replies: 3
    Last Post: 08-19-2012, 06:57 PM
  2. Resize Datafiles to avoid alert
    By Amit in forum Database Performance Management,Database Links,Materialized Views
    Replies: 0
    Last Post: 11-02-2011, 03:22 PM
  3. Resize Datafiles to avoid alert
    By Amit in forum Database Scripts
    Replies: 0
    Last Post: 11-02-2011, 03:22 PM
  4. Check any particular Alert Message email has be pending by Mailer
    By Hemant in forum SCRIPTS : Concurrent Manager and Workflow
    Replies: 0
    Last Post: 11-01-2011, 09:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics