8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Email From Oracle PL/SQL (UTL_SMTP)


Yogesh Kamath said...

Thanks you very much, it helped me a lot.

Is there any way to send text in MAIL BODY, this example describes about sending data/message in subject line.

Gr8 work..

Cheers
Yogesh

Jonathan Sanchez said...

I have a problem. in the line " UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));" return this error : "ORA-06502: PL/SQL: error : longitud de variable raw demasiado grande numérico o de valor"

Thanks

Tim... said...

Hi.

I'm guessing you are using a multibyte character set so the buffer is too small to hold the raw data. If this is the case, you will need to reduce the step size.

If you have any more problems, please ask questions in the forum.

Cheers

Tim...

Sunil said...

Nice article! simple and useful!

Chukwuma Imegwu said...

My scenario is this, send an alert using UTL_SMTP.

#!/bin/bash

if [ $# -ne 2 ]; then
echo "Usage: $0 SID threshold"
exit 1
fi
ORACLE_SID=SITA
ORACLE_HOME=/u01/app/oracle/SITA/db/tech_st/11.2.0
PATH=$ORACLE_HOME/bin:$PATH
crit_var=$(
sqlplus -s < apps/usneg01d14
SET HEAD OFF TERM OFF FEED OFF VERIFY OFF
COL pct_free FORMAT 999

Tim... said...

Questions in the forum please!

Cheers

Tim...

sanjeev said...

in clob attachment mail, why you using l_step?

Tim... said...

Hi.

The WRITE_DATA procedure only accepts VARCHAR2, so we have to process the CLOB in chunks. The step is used in the division of the CLOB into suitable chunks.

If WRITE_DATA accepted CLOBs, or your CLOBs were always under 32K in size, this would not be necessary.

Cheers

Tim...

luva said...

Hi.

I have a question; i perfectec works; but i need it with pdf. anyone has any idea.

first of all thank you very much.

Tim... said...

Hi.

The BLOB method should work with any binary document, like a PDF.

Cheers

Tim...

luva said...

Hi.

i wish is that i have text that is attached as a pdf; do you know how i can make an example;

i said that change this
p_attach_mime => 'application/pdf'

send mail correctly; but when i open attachment get error.

D said...

Hi Tim,
Yuur eg is lovely. I am able to send attachements. But I have aquestion, If I submitted a report in forms6i and I would like to attach it. The pdf files are stored in directory c. Could you please help.

Tim... said...

Hi.

These methods only work if the attachment is on the DB server. If the attachment is on your PC, this is not the method for you. :(

Cheers

Tim...

tami said...

any good way to handle bad email? I have it set to display in output in the exception clause but I usually send a string of emails and none of them will send if one is bad..any way to set it up to send all the good ones even if one is bad?

this is how i'm sending it now..

'myemail@dice.org,youremail@k.org,goodemail@gmail.com'


Tim... said...

Hi.

No. You are at the mercy of your mail server. If in doubt, send them one at a time with an exception handler round them.

Cheers

Tim...

tami said...

can you give me a quick example for one at a time?
I tried that and its not working

Tim... said...

Do a separate call to the procedure for each recipient, rather than one call for multiple recipients...

Cheers

Tim...

tami said...

I created this procedure to take in the string and parse it and call each individual email to the procedure that handle the email part but when it call the procedure to process it, it still stop at exception and not process the last valid email in the string..

Tim... said...

Hi.

Open a question in the forum. Discussing a topic here is hard.

Cheers

Tim...

Herman said...

If the p_text_msg and the p_html_msg both are NOT NULL, only the p_html_msg is shown in the email.
If only the p_text_msg is NOT NULL, the p_text_msg is shown in the email.
I'm not able to show both the html as the text message in the same email.

What can be the reason for this behaviour.
Has anyone tried this?

Tim... said...

Hi.

When you send a HTML email, if the client is capable of showing it, *only* the HTML message will be shown. If the client is not capable of showing the HTML message, *only* the test message will be shown.

The text message is a fallback in case the client prevents HTML output. You would *never* expect to see both messages... :)

Cheers

Tim...

Daniel said...

Hello Tom,

Please pardon my ignorance. You have mentioned that the attachment needs to be located on the DB server... Can you please point to the part of the code where I could specify the path where the file is located? I guess it will be named directory.

Thank you,

Daniel

Tim... said...

Hi.

This article sends BLOBs and CLOBs from PL/SQL. It doesn't say where they are sourced from. It could be a DB table, a PL/SQL variable, or read in from the file system into a PL/SQL variable. For example.

https://oracle-base.com/articles/8i/import-blob.php

Cheers

Tim...

Arun said...

What is the purpose of l_boundary variable in CLOB attachment procedure???

sumanth said...

what is purpose of l_boundary variable in clob_attachment procedure?

Tim... said...

Hi.

The boundary is a separator between the different pieces of the email. It's part of the basic structure of an email.

Cheers

Tim...

Arun said...

While am trying to send email with attachment(Image already stored in table) getting below..
Received email without attachment and then email body has below..
Qk3iOAAAAAAAADYAAAAoAAAAewAAACcAAAABABgAAAAAAAAAAADEDgAAxA4AAAAA AAAAAAAArIVV .
...............
HRzgIRvfIhjkHBvjGRvjHRnmIxTrIxTtHxXrHxrjHh3cHhzeHhnlHRfoHR jlHRjlGhPnRD7oAAAAAA==
Any one can help on this????

Thanks in advance.

Tim... said...

Hi.

I would suggest you code a simple example, like the table used in this example and try that first. If you have a test case like this, which can reproduce the issue, email it to me and I'll take a look.

Cheers

Tim...

Tim... said...

Hi.

I've deleted your code. Please email it to me. You know my name "tim". You know the website name "oracle-base.com". You can guess my email address.

Cheers

Tim...

Arun said...

By using example code I have received email with attachment. But attachment opening without image. it shows as "photo viewer doesn't support this file format". Can u help on this???

Thanks.

Tim... said...

Hi.

Like I said, email your test case to me.

Cheers

Tim...

Chandra sekhar said...

How to add one to_mail and multiple bcc mail id in utl_smtp

Tim... said...

Hi.

The multiple recipient example does this.

Cheers

Tim...

PRIYARANJAN VERMA said...

i am getting below error message while trying to use the code for multiple receipient
ORA-29279: Permanent SMTP error: 503 5.5.2 Need mail command

Tim... said...

Hi.

You will have to investigate with your mail administrators. Either you don't have access to the mail server from the DB, or the mail server is rejecting the messages for dome reason.

Cheers

Tim...

raguila said...

When server is installed in other language other than english, the correct date format to send email is:

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSTIMESTAMP, 'Dy, DD Mon YYYY HH24:MI:SS TZHTZM', 'NLS_DATE_LANGUAGE=ENGLISH') || UTL_TCP.crlf);

Otherwise it may take erroneous sent date.

Amey Inamdar said...

Thanks a lot!

It helped in in mail attachment with BLOB. Requirement was to have an attachment of PDF to oracle generated mail. It was possible only with the example on this post. Thanking you once again.

Anonymous said...

This configuration works in Oracle 18c Xe ?

Tim... said...

Hi.

I would expect it to, but I've never tried it.

Cheers

Tim...

BG said...

I took the code for sending an HTML message.

The problem is that an extra space is inserted at 1000 position.

?!??!!!??!?!?

Is this a bug or a feature???

BG said...

Correction -- a space is inserted after every 1000 characters.

Tim... said...

Hi.

You can see from the code it writes the HTML as a single action, so there is nothing that can be adding the extra characters. No loops etc. My guess would be the HTML you are passing in has the extra spaces.

Can't say I've ever seen a problem with it myself.

Cheers

Tim...

Sasha said...

Well, that code UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
makes nervous SPAM assasin: -2.019 BASE64_LENGTH_79_INF base64 encoded email part uses line length greater than 79 characters

As it can be seen that code really create several strings over 79 characters in result.
I'd appreciate You for any idea how to figure out that.

Tim... said...

Hi.

I don't know anything about SPAM assassin, but my guess would be if you made l_step := 78, it might make the lines below 79, whilst still being a multiple of 3.

If that doesn't work, you'll need to read the SMTP spec and figure out how to format the data to match your requirement.

Cheers

Tim...

Sasha said...

Tim, the matter is that Oracle 12+ factually break RFC 2045 so Your code leads to incorrect mail body.
In a word the solution is in little correction (|| UTL_TCP.crlf)):
UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))) || UTL_TCP.crlf);
Thank You.

Tim... said...

Hi.

I'll have to test how that affects the attachment. I'm not sure how the receiving program will interpret the CRLF in the middle of a blob. If this were a straight "write to a file", that would corrupt it.

Cheers

Tim...

Sasha said...

Tim, You are in little misunderstanding with "CRLF in the middle of a blob"
UTL_SMTP.write_data
( l_mail_conn,
UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))
) || UTL_TCP.crlf
)
You can ensure with RFC on https://www.mail-tester.com/

Tim... said...

Hi.

I don't think I am misunderstanding. The code writes chunks of the blob out in a loop. You are saying add a CRLF on each write. That means several CRLF throughout the blob. If this were writing to a file, this would definitely corrupt the file. I need to check the impact on the resulting attachment before making the change.

Cheers

Tim...

Sasha said...

Yes, You are.
Look, UTL_RAW.cast_to_varchar2( returns a block of VARCHAR2
But in Oracle 12+ UTL_ENCODE.base64_encode doesn't end a block with CRLF. So we got after several UTL_RAW.cast_to_varchar2 a strings longer than RFC require.
So we need to ensure every VARCHAR2 block that will be written to l_mail_conn ends with CRLF.

Tim... said...

Which is exactly what I said. My point is, if you were writing to a file and putting extra characters in it, you are reliant on the reading program to understand. I've not tried this with an email. I've not compared the difference between versions. I'm not going to change without testing because you say so. Look at my second response. I say I've got to test it!

Tim.. said...

Also, the double CRLF after the loop would probably get switched to a single CRLF. The step size still needs to be reduced to stay under the limit.

When I've got time I'll run through some tests on different versions and see what happens.

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.