Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

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.

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...

DO NOT ask technical questions here, that's what my forum is for!

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)