Home » SQL & PL/SQL » SQL & PL/SQL » Error in output sending error (Oracle10g)
Error in output sending error [message #317862] Sun, 04 May 2008 07:39 Go to next message
snake_eyes
Messages: 10
Registered: April 2008
Junior Member
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;
Re: Error in output sending error [message #317867 is a reply to message #317862] Sun, 04 May 2008 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated above

Re: Error in output sending error [message #317869 is a reply to message #317862] Sun, 04 May 2008 08:53 Go to previous messageGo to next message
snake_eyes
Messages: 10
Registered: April 2008
Junior Member
Hello,

I made some search before I ask, but the result was not helpful, this is the reason that I post it.

I apologize for any inconvenience I made to you.

W. Regards
Re: Error in output sending error [message #317870 is a reply to message #317862] Sun, 04 May 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You didn't format but you know you have to do it as you did it in your previous post
2/ Are you sure you got nothing for the error "PL/SQL: numeric or value error"?
3/ Are you sure your are an expert?
4/ Are you sure this is an expert question?

Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

Regards
Michel
Re: Error in output sending error [message #317871 is a reply to message #317862] Sun, 04 May 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And also, you have the anwser in the previous topic you posted 2 weeks ago.

Regards
Michel
Re: Error in output sending error [message #317873 is a reply to message #317862] Sun, 04 May 2008 09:29 Go to previous messageGo to next message
snake_eyes
Messages: 10
Registered: April 2008
Junior Member
Hello,

I thought from the forum title, for the expert in order to help us.

I'm new in the pl/sql development, although my field in the development such as Delphi for desktop application, PHP JSP for web application.

In the latest project there is an email solution using the utl_smtp that is why I post this issue.

Would you like to accept my apology good for me, otherwise you can delete this post.

W. Regards
Re: Error in output sending error [message #317876 is a reply to message #317873] Sun, 04 May 2008 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I thought from the forum title, for the expert in order to help us.

Quoted from the other expert sticky:
Quote:
Newbie forum does not mean only newbies read and answer it, experts also read and answer this forum.
You don't have any benefit to post in expert forum if you are not an expert, on the contrary you likely have NO answer just reprimands.


Quote:
I'm new in the pl/sql development

It is clearly stated in the forum description: "Newbies should not post to this forum!"


Reread what has been said on the same error in your previous topic and tell us why it is not the same thing.

Regards
Michel


[Updated on: Sun, 04 May 2008 09:35]

Report message to a moderator

Re: Error in output sending error [message #317878 is a reply to message #317862] Sun, 04 May 2008 09:53 Go to previous message
snake_eyes
Messages: 10
Registered: April 2008
Junior Member
First of all I didn't receive any notification regarding to my previous topic that is why I re-post it in the expert forums.

http://www.orafaq.com/forum/m/315224/122892/#msg_315224

but I will copy and paste code in my previous topic and you can delete this if you want.

Please note: Since I registered I update my profile to receive the notifications but I surprised not why I donna receive.

Regards

[Updated on: Sun, 04 May 2008 09:54]

Report message to a moderator

Previous Topic: Bulk collect : impact in performance
Next Topic: A Question about this error ORA-06502
Goto Forum:
  


Current Time: Sat Dec 10 08:49:17 CST 2016

Total time taken to generate the page: 0.19786 seconds