UTL_MAIL vs UTL_SMTP sending CLOB body [message #680407] |
Wed, 13 May 2020 09:37 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I am confused from the available sources/cases on the internet and want to ask a few questions about sending large size emails (email body exceeding UTL_MAIL attachment/body size).
I have my server setup and already sending emails using UTL_MAIL.SEND. When I changed my mail body variable from VARCHAR2 to CLOB I started getting ORA-06502: PL/SQL: numeric or value error on UTL_MAIL.SEND.
I searched using UTL_SMTP and have the following questions:
- What I need to do is to send a large email body (not a large email attachment), do I still need to shift form UTL_MAIL to UTL_SMTP?
- I am using UNIX server and I already use emails using UTL_MAIL, is there any extra setup I have to do on the server?
Thanks,
Ferro
|
|
|
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680408 is a reply to message #680407] |
Wed, 13 May 2020 10:26 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You may change your variable data type but you don't (and can't) change the type of the SEND procedure type which is VARCHAR2 (and so limited to 32K). This is also true for the SEND_ATTACH_VARCHAR2 procedure.
So you have to switch to UTL_SMTP but hopefully this has already be done and you just have to copy the code, see send_mail from ORACLE-BASE.
[Updated on: Wed, 13 May 2020 10:28] Report message to a moderator
|
|
|
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680433 is a reply to message #680408] |
Thu, 14 May 2020 02:12 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Thanks Michel for your reply,
I created the SEND_EMAIL SP and here is what I am currently stuck at:
CREATE OR REPLACE PROCEDURE AFESD.MySMTP_mail
(
S_APP_USER IN VARCHAR2,
S_MODULE_NAME IN VARCHAR2
)
/*This Stored Procedure is designed to get
Author:
Date:
*/
IS
T_SENDER VARCHAR2(30) := 'IT_SYSTEM_ALERT@My_org.com';
T_CC VARCHAR2(30) := 'Name@domain.com';
T_MESG CLOB;
T_MTYPE VARCHAR2(40) := 'text/html; charset=AL16UTF16';
BEGIN
T_MESG:= 'My message';
UTL_MAIL.SEND(T_SENDER,'myname@domain.org', T_CC, NULL, 'Title', T_MESG, T_MType, NULL);
SEND_MAIL(T_SENDER,'Title', T_MESG, NULL, NULL, NULL, '165.78.22.66', 444); -- 165.78.22.66 is the local IP for the server, and 444 is the outgoing SMTP Port.
END;
The SP works fine with UTL_MAIL.SEND, but when I add the SEND_MAIL call I get:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 29
ORA-06512: at "SYS.UTL_SMTP", line 108
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at "AFESD.SEND_MAIL", line 15
ORA-06512: at "AFESD.MYSMTP_MAIL", line 21
ORA-06512: at line 2
Appreciate you advice,
Ferro
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680639 is a reply to message #680482] |
Wed, 27 May 2020 16:53 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
a quick way to test the connection to a smtp mail server is to first ping the ip address of the mail server to see if you can access it. Then use telnet to see if you can get it to respond.
This should be done from the oracle database server
telnet smpt.domain.com 25
The 25 tells telnet to use port 25. do it like this
$ telnet
telnet> open mail.xxxxx.com 25
trying 10.0.1.69...
Connected to mail.xxxxx.com.
escape character is '^]'.
20 RF-MAIL-PR01.xxxxx.local Microsoft ESMTP MAIL Service
27 May 2020 17:49:29 -0400
EHLO
50-RF-MAIL-PR01.xxxxx.local Hello [10.0.1.132]
50-SIZE 31457280
50-PIPELINING
50-DSN
50-ENHANCEDSTATUSCODES
50-8BITMIME
50-BINARYMIME
50 CHUNKING
test
.
quit
connection to mail.xxxxx.com closed by foreign host.
$
[Updated on: Wed, 27 May 2020 16:56] Report message to a moderator
|
|
|