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.

There is excellent sample code for this at:
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html

All PL/SQL, no Javamail needed, and it includes routines for attaching files, both text and binary. You can add this package to your database with very few changes - mostly to tell it where your SMTP server is.

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

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

Ziv, Take a look to this sample code in Oracle's web site, I think is a good place to start, and use the code in the article and with some few changes it works fine for me, I don't have the modified code at this moment, but if I have time on Monday, I'll put it here. http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html

Ziv,
Best example I used : http://www.akadia.com/services/java_mail_plsql.html
Kind Regards,
Lior Ben Shachar

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.

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

Hi Tom,

Could you please an example to achieve this? I will use that for my future enhancement.

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 flat file and when it is use full i need example plz send this mail ravioracler12@gmail.com

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;
/

    WHEN OTHERS THEN
        Dbms_Output.put_line(SQLERRM);
       
  END ;

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.

i want generate PDF file than send attachment email using store PROCEDURE in oracle 10g.
PDF generate but open time error.
please Help me.

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.