Comments for Email From Oracle PL/SQL (UTL_SMTP)
|Email From Oracle PL/SQL (UTL_SMTP) - Email from PL/SQL using UTL_SMTP rather than using external procedures or Java.|
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.
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"
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.
Sunil said...Nice article! simple and useful!
Chukwuma Imegwu said...My scenario is this, send an alert using UTL_SMTP.
if [ $# -ne 2 ]; then
echo "Usage: $0 SID threshold"
sqlplus -s <
SET HEAD OFF TERM OFF FEED OFF VERIFY OFF
COL pct_free FORMAT 999
Tim... said...Questions in the forum please!
sanjeev said...in clob attachment mail, why you using l_step?
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.
I have a question; i perfectec works; but i need it with pdf. anyone has any idea.
first of all thank you very much.
The BLOB method should work with any binary document, like a PDF.
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.
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. :(
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..
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.
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...
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..
Open a question in the forum. Discussing a topic here is hard.
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?
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... :)
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.
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.
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!