RSS start | index | login or register | find | edit
Online for 6936 days.




sticky snips:



no backlinks


5 active users:

unknown (5)

Recent edits:

HTML eMails with Oracle stick
by unknown, a long time ago

Writing an Oracle PL/Sql Procedure to send an HTML-formatted eMail.
  • emails in Oracle

    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;

    (@askTom)


    No attachments for this snip.
    Upload / manage attachments!
  •   c'est un vanilla site