Interesting that you use the word need
Lars Sjöström, July 27, 2009 - 4:39 am UTC
Hi,
I searched for the Java Mail API on the internet, took the first link since I am lucky :-), and got this
"The JavaMail 1.4.2 release contains several bug fixes, performance improvements, and enhancements. Note: Unless you're using Java SE 6, you will also need the JavaBeans Activation Framework (JAF) extension that provides the javax.activation package. "
So, following along the Java Mail API path to get the file sent will, as I see it, *need* some thingys, and you need to think twice. To me, Java is new. Time is a factor, education as well. For me as well as the people that will implement this Java Mail API method of sending attached files in an e-mail.
We did solve this using traditional Linux sendmail and things that was around in the Linux environment. It was complicated, but it runs now in production. However, that might not be the feasable solution to the questionee.
It might be so that the Java Mail API is a more reliable, effective solution, but it is also sometimes in some companies a new tool of the trade. So, the developers are starting to sing in chorus "We ain't got no education", even to the tune of Pink Floyd's The Wall...
Back to the word need. You are right, Tom, of course, that the business need is to - or is it? - send an attachment to a receiver. I would actually ask business what their really need is. What actually are they doing?
It could be solved in a different way. Not even sending a mail. The users are used to get this information thru mail, maybe that process should change. To a better way for the business.
So...ask the users what they really really want to do. Them, if they are stuck on mail after some yellow stickers brainstorming session, then, abeit give them mail.
If you do not know Java, do the mailing using sendmail and some (to you known) shell script.
Sample code for e-mail from the database
John Flack, July 27, 2009 - 9:23 am UTC
Alternative wrapper package
Gary, July 27, 2009 - 6:59 pm UTC
The PL Code Brew package also has a more friendly API/wrapper for SMTP, and provides for connecting to an SMTP server that requires a username/password
http://sourceforge.net/projects/cleveridea4data/ Personally, I remember the days when you would have to manually uuencode stuff to attach to an email or usenet posting. Kids today don't know how lucky they are....
July 27, 2009 - 8:45 pm UTC
I remember large usenet postings - tarballed code and stuff - that we had to 'split' in order to post as the dialup line was too fragile to do it in a single go. So you would have to then download the 1/4, 2/4, 3/4, 4/4 pieces and put them back together to get the original tar.Z file and finally work on it.
So, not only did you have to uuencode on the way up, you usually had to split it and post multiple entries too :)
ahh, the good old days - when a 56k modem felt like heaven...
mail
A reader, July 28, 2009 - 10:35 pm UTC
External File as email attachment
dev, August 13, 2009 - 8:37 pm UTC
Simple requirement but people answered as so complex.
Any PL/SQL guru can please please reply here?
My prog generates a csv file and which I have kept at UTL_FILE derectory like /usr/kode/test_data.csv
Now I want to attach this file simplest way and send as email attachment.
I have used UTL_SMTP and UTL_TCP ,those are working fine for sending email,I have passed so many variables pipes with message string ,it works fine.
Thnx
dev
External File as email attachment
tzompantli, August 15, 2009 - 11:54 pm UTC
Lior Ben Shachar, August 24, 2009 - 3:33 pm UTC
Shivom, December 31, 2010 - 12:52 am UTC
Hi Ziv,
Did you get the solution? I have the same issue too.
Unfortunately none of the responses are useful.
Your help wil be much appreaciated.
Email Template
A reader, April 26, 2011 - 6:10 am UTC
Hi Tom,
Is it possible to have an email template that is stored in Oracle 10G? Lets say the date and the name are the place holders and the message body is fixed. I also want to embed an image file in message body part taken from database randomly e.g.
To,
<name as Placeholder: Replaced from database table>
Date: <Date as Placeholder: Replaced from database table>
......
Message body fixed
<Embed an image file randomly from database>
......
From,
<name as Placeholder: Replaced from database table>
Please do let me know if this is possible..
April 26, 2011 - 7:20 am UTC
You can store anything you like in the database, yes. An email template would just be "text" to us, we don't care what you call it or what you do with it...
and then you write code against it.
do we have a builtin mail merge program for you? No, not really. We have the building blocks you can use to build anything...
Thanks
A reader, April 26, 2011 - 8:19 am UTC
Hi Tom,
Could you please an example to achieve this? I will use that for my future enhancement.
April 26, 2011 - 2:30 pm UTC
I know nothing of your client application, nor your real requirements.
If your 'tags' in the 'template' are unique enough, a simple REPLACE call on the data with the elements you want to replace with should be sufficient.
To: A reader - Re: Email templates
Kim Berg Hansen, April 27, 2011 - 5:57 am UTC
Hi, A reader
It may be overkill for your needs (I can't tell :-), but you could consider creating your templates as XML stylesheets. Then you can use Oracle builtin XML functionality to select your data as XML and applying the stylesheet to it also within SQL or PL/SQL.
If you haven't already decided upon your template format, try to read the Oracle docs on XML development. If you plan on fairly complex templates it may be the easiest way. If you plan for very simple templates, then the simple REPLACE may be enough for you.
who create the flate file when transforing date into base table in inbound interface
ravi, January 07, 2012 - 5:34 am UTC
who create the flat file and when it is use full i need example plz send this mail ravioracler12@gmail.com
January 10, 2012 - 9:45 pm UTC
who ever wants to create the flat file I guess.
this makes no sense whatsoever.
Emailing large files as attachment.
shyam, March 20, 2012 - 1:19 am UTC
I'm trying to email large files as attachment.Since its huge i'm using clob as already advised.Its a pipe delimited file.After the file is created , i open the file and read it and append it to the clob variable.so after writing each line it should go the next line to write the newly read line.But for some reason few lines are getting ommitted.There is no pattern for this ommission.ex: out of 200kb only 104 kb is getting written into the clob and getting emailed.
BEGIN
vInHandle_read := utl_file.fopen(n_test_dir, N_FILE_NAME, 'R');
LOOP
utl_file.get_line(vInHandle_read,vNewLine);
l_clob:=l_clob||chr(10)||vnewline;
end loop;
utl_file.fclose(vInHandle_read);
EXCEPTION
WHEN NO_DATA_FOUND then
utl_file.fclose(vInHandle_read);
Dbms_Output.put_line(SQLERRM);
WHEN OTHERS THEN
Dbms_Output.put_line(SQLERRM);
END ;
/** Send_mail_atta is a normal proc to email attachment which i have provided below**/
send_mail_atta(p_to => 'abcd@gghk.com',
p_from => 'abcd@gghk.com',
p_subject => 'Test Message',
p_text_msg => 'This is a test message.',
p_attach_name => n_file_name||'.txt',
--p_attach_mime => 'text/plain',
p_attach_clob => l_clob,
p_smtp_host => 'mail_gdc.xxxx.com');
END;
/****************************************************/
CREATE OR REPLACE PROCEDURE send_mail_atta (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_clob IN CLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step PLS_INTEGER := 30000;
BEGIN
l_mail_conn := UTL_SMTP.open_connection('mail_gdc.xxyyn.com', '25');
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
END LOOP;
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
/
March 20, 2012 - 9:33 am UTC
WHEN OTHERS THEN
Dbms_Output.put_line(SQLERRM);
END ;
I hate your code - search this site for "i hate your code" in quotes to read why I hate you code. Why do you do that???? why why WHY do you do that? What is your purpose in doing that, what is your goal?
All you did was break the databases transactional features and hide the line number from someone trying to debug this! why???
http://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47asktom-092692.html that is five years old, this is newer:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61asktom-512015.html see the section "Why You Really Want to Let Exceptions Propagate" in that one.
to code your block 'correctly', you would want to:
ops$tkyte%ORA11GR2> !ls -l test.dat
-rw-rw-r-- 1 tkyte tkyte 150478 Mar 20 10:24 test.dat
ops$tkyte%ORA11GR2> declare
2 l_clob clob;
3 vInhandle_read utl_file.file_type;
4 vNewLine long;
5 BEGIN
6 vInHandle_read := utl_file.fopen('TEST_DIR', 'test.dat', 'R');
7 LOOP
8 begin
9 utl_file.get_line(vInHandle_read,vNewLine);
10 exception
11 when no_data_found
12 then
13 EXIT;
14 end;
15 l_clob:=l_clob|| chr(10) || vnewline;
16 end loop;
17 utl_file.fclose(vInHandle_read);
18 dbms_output.put_line( 'the clob is ' || length(l_clob) || ' bytes' );
19 exception
20 when others
21 then
22 if (utl_file.is_open(vInHandle_read))
23 then
24 utl_file.fclose(vInHandle_read);
25 end if;
26 RAISE;
27 END;
28 /
the clob is 150478 bytes
PL/SQL procedure successfully completed.
you want your exception block to catch that EXPECTED exception to be surrounding only the relevant code (that is the only time "no data found" is OK - it would not be OK on any other line of code - so only catch it around the code is it OK for!!!!).
You can use the when others to release resources - but follow it by re-raising the exception.
However, that all said, this code should not exist. You need not write it into a clob at all, just use a BFILE!!!! Use dbms_lob.fileopen and in one line of code - you'll have everything you need right there in a bfile (a bfile works just like a clob or blob - but you don't have to read the entire file into memory and then write it out again - just bfile it)
Create PDF
vikas heda, June 21, 2013 - 10:05 am UTC
i want generate PDF file than send attachment email using store PROCEDURE in oracle 10g.
PDF generate but open time error.
please Help me.
Error while opening the PDF generated via email attachment using plsql
kevin, October 11, 2013 - 4:34 am UTC
Hi Toms,
I tried geenarting a pdf attachment using UTL_SMTP, but I am not able to open the pdf . it says it was sent as email attachment and not correctly decoded.
Please help me.