Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    9

    Red face Unanswered: Error in output and mail sending...

    Hello,

    I'm getting an error when I run the procedure, the error in this area:

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

    and the error is: Error number: ORA-06502: PL/SQL: numeric or value error.

    please advice

    procedure submit_email(p_to varchar2,
    p_from varchar2,
    p_subject varchar2,
    p_html clob,
    p_smtp_hostname varchar2,
    p_smtp_portnum 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 clob 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, 'oracle10g@site.com');
    utl_smtp.rcpt(l_connection, p_to);

    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 || 'MIME-Version: 1.0' || chr(13) || chr(10);
    l_temp := l_temp || 'X-Priority: 1' || chr(13) || chr(10);
    l_temp := l_temp || 'X-MSMail-Priority: Normal' || 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 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; charset=windows-1256' ||
    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,
    dbms_lob.getlength(p_html),
    l_offset,
    p_html);
    dbms_lob.append(l_body_html, 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);

    exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then
    begin
    utl_smtp.quit(l_connection);
    exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then
    null;
    /* when the smtp server is down or unavailable, we don't
    have a connection to the server. the quit call will
    raise an exception that we can ignore.*/
    end;

    raise_application_error(-20000,
    'Failed to send mail due to the following error: ' ||
    sqlerrm);

    end submit_email;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2008
    Posts
    9

Posting Permissions

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