Home » SQL & PL/SQL » SQL & PL/SQL » UTL_MAIL vs UTL_SMTP sending CLOB body (11.2.0.1.0)
UTL_MAIL vs UTL_SMTP sending CLOB body [message #680407] Wed, 13 May 2020 09:37 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
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 #680434 is a reply to message #680433] Thu, 14 May 2020 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you set "smtp_out_server" instance parameter?

Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680437 is a reply to message #680434] Thu, 14 May 2020 03:14 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
@Michel,

Yes, and re-did it with the sys dba account:
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;
Is there any other setup missing?

Thanks,
Ferro
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680439 is a reply to message #680437] Thu, 14 May 2020 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you restart after that command? otherwise you have to re-execute it with SCOPE=MEMORY for the current instance to take it into account.

Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680444 is a reply to message #680439] Thu, 14 May 2020 10:03 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Unfortunately the same result!
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680445 is a reply to message #680444] Thu, 14 May 2020 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The other option is that the smtp server is not up or you can't reach it from the db server.

Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680446 is a reply to message #680445] Thu, 14 May 2020 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Some, many, most SMTP Servers now days are configured with anti-spam measures so that the SMTP server ignores requests from "unknown" hosts to avoid acting as a spam relay. It can require explicit configuration changes by SMTP Postmaster to allow DB Server to send email messages through SMTP Server.
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680451 is a reply to message #680446] Fri, 15 May 2020 04:32 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
@Michel,
Quote:

The other option is that the smtp server is not up or you can't reach it from the db server.
Can this be the case although UTL_MAIL.SEND is working fine? If yes, how can I check that?

@BlackSwan
Quote:

Some, many, most SMTP Servers now days are configured with anti-spam measures so that the SMTP server ignores requests from "unknown" hosts to avoid acting as a spam relay. It can require explicit configuration changes by SMTP Postmaster to allow DB Server to send email messages through SMTP Server.
I have full access on the administration tool and appreciate any guidance in this regard. Please tell me any information you need that can help identify is this is the problem.

Thanks,
Ferro
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680467 is a reply to message #680451] Fri, 15 May 2020 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OraFerro wrote on Fri, 15 May 2020 02:32


@BlackSwan
Quote:

Some, many, most SMTP Servers now days are configured with anti-spam measures so that the SMTP server ignores requests from "unknown" hosts to avoid acting as a spam relay. It can require explicit configuration changes by SMTP Postmaster to allow DB Server to send email messages through SMTP Server.
I have full access on the administration tool and appreciate any guidance in this regard. Please tell me any information you need that can help identify is this is the problem.

Thanks,
Ferro

It has been decades since I last diddled with last configuring SMTP server so I don't recall specifics
You should check the actual SMTP logfile content. If it is dropping/ignoring incoming requests from DB Server system,
a log entry will exists documenting its action.
If a change is needed, you must simply Read The Fine Manual to see what is required to allow message forwarding by SMPT Server software.
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680481 is a reply to message #680467] Sat, 16 May 2020 03:19 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
@BalckSwan
Thanks for your reply.

Quote:

You should check the actual SMTP logfile content. If it is dropping/ignoring incoming requests from DB Server system,
a log entry will exists documenting its action.
I am looking into this, nothing suspicious so far (nothing related to denying DB server requests). I will look deeper but first I need to make sure that this can take place although UTL_MAIL.SEND works fine from the same server. Kindly confirm.

Thanks,
Ferro
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680482 is a reply to message #680481] Sat, 16 May 2020 07:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORA-29278: SMTP transient error: 421 Service not available
Is SMTP Server on same subnet as DB Server?
Does ant FireWall exist on or between DB Server & SMTP Server?
You need to packet sniff to determine if SMTP packets are getting to delivered to SMTP Server from DB Server & response packets get back to DB Server.
Re: UTL_MAIL vs UTL_SMTP sending CLOB body [message #680639 is a reply to message #680482] Wed, 27 May 2020 16:53 Go to previous message
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

Previous Topic: Audit Database Problem
Next Topic: how to display the values with prefix increment?
Goto Forum:
  


Current Time: Thu Mar 28 13:48:25 CDT 2024