Send Email from oracle with file attached"

[balajiyes]

hi all
is this possible in oracle "Send Email from oracle with file attached"?

Cheers!

[celebguy_dv]

http://banners.dollarmachine.com/pic/2014000/hal001.gif (http://www.kinkyceleb.com/1261795520)

[Tarry]

OK, this is less kinkier but I felt like trying the utl_smtp pkg so here's what you can do(I've used T kyte's example with a few variations here and there, for eg: with the newer version of javamail/jaf does not require unjar and rejar kind of stuff, they're are readable in the database....)

[1] First you download javamail api(http://java.sun.com/products/javamail/) and jaf--needed to activate the javamail api(http://java.sun.com/products/javabeans/glasgow/jaf.html).
Then after downloading extract the mail.jar from the javamail-1_3.zip file and activation.jar from the jaf-1_0_2.zip file into the directory of your choice.

[2] then load these 2 jar files into your database(I've used 817 here) with the foll. syntax............

loadjava -u sys/pwd@dbase -o -r -v -f -noverify -synonym -g public activation.jar
do the same for the mail.jar file..
For the parameters just type loadjava to understand what they mean...

[3] then write a java source (for explanations for the syntax(es) I'd advise you to acquire the tom kyte's book, it's excellent...)


sys@NICK817.TARRY.LOCAL>
sys@NICK817.TARRY.LOCAL> create or replace and compile
2 java source named "mail"
3 as
4 import java.io.*;
5 import java.sql.*;
6 import java.util.Properties;
7 import java.util.Date;
8 import javax.activation.*;
9 import javax.mail.*;
10 import javax.mail.internet.*;
11 import oracle.jdbc.driver.*;
12 import oracle.sql.*;
13
14 public class mail
15 {
16 static String dftMime = "application/octet-stream";
17 static String dftName = "filename.dat";
18
19 public static oracle.sql.NUMBER
20 send(String from,
21 String to,
22 String cc,
23 String bcc,
24 String subject,
25 String body,
26 String SMTPHost,
27 oracle.sql.BLOB attachmentData,
28 String attachmentType,
29 String attachmentFileName)
30 {
31 int rc = 0;
32
33 try
34 {
35 Properties props = System.getProperties();
36 props.put("mail.smtp.host", SMTPHost);
37 Message msg =
38 new MimeMessage(Session.getDefaultInstance(props, null));
39
40 msg.setFrom(new InternetAddress(from));
41
42 if (to != null && to.length() > 0)
43 msg.setRecipients(Message.RecipientType.TO,
44 InternetAddress.parse(to, false));
45
46 if (cc != null && cc.length() > 0)
47 msg.setRecipients(Message.RecipientType.CC,
48 InternetAddress.parse(cc, false));
49
50 if (bcc != null && bcc.length() > 0)
51 msg.setRecipients(Message.RecipientType.BCC,
52 InternetAddress.parse(bcc, false));
53
54 if ( subject != null && subject.length() > 0 )
55 msg.setSubject(subject);
56 else msg.setSubject("(no subject)");
57
58 msg.setSentDate(new Date());
59
60 if (attachmentData != null)
61 {
62 MimeBodyPart mbp1 = new MimeBodyPart();
63 mbp1.setText((body != null ? body : ""));
64 mbp1.setDisposition(Part.INLINE);
65
66 MimeBodyPart mbp2 = new MimeBodyPart();
67 String type =
68 (attachmentType != null ? attachmentType : dftMime);
69
70 String fileName = (attachmentFileName != null ?
71 attachmentFileName : dftName);
72
73 mbp2.setDisposition(Part.ATTACHMENT);
74 mbp2.setFileName(fileName);
75
76 mbp2.setDataHandler(new
77 DataHandler(new BLOBDataSource(attachmentData, type))
78 );
79
80 MimeMultipart mp = new MimeMultipart();
81 mp.addBodyPart(mbp1);
82 mp.addBodyPart(mbp2);
83 msg.setContent(mp);
84 }
85 else
86 {
87 msg.setText((body != null ? body : ""));
88 }
89 Transport.send(msg);
90 rc = 1;
91 } catch (Exception e)
92 {
93 e.printStackTrace();
94 rc = 0;
95 } finally
96 {
97 return new oracle.sql.NUMBER(rc);
98 }
99 }
100
101 // Nested class that implements a DataSource.
102 static class BLOBDataSource implements DataSource
103 {
104 private BLOB data;
105 private String type;
106
107 BLOBDataSource(BLOB data, String type)
108 {
109 this.type = type;
110 this.data = data;
111 }
112
113 public InputStream getInputStream() throws IOException
114 {
115 try
116 {
117 if(data == null)
118 throw new IOException("No data.");
119
120 return data.getBinaryStream();
121 } catch(SQLException e)
122 {
123 throw new
124 IOException("Cannot get binary input stream from BLOB.");
125 }
126 }
127
128 public OutputStream getOutputStream() throws IOException
129 {
130 throw new IOException("Cannot do this.");
131 }
132
133 public String getContentType()
134 {
135 return type;
136 }
137
138 public String getName()
139 {
140 return "BLOBDataSource";
141 }
142 }
143 }
144 /

Java created.



[4] write a function ...


sys@NICK817.TARRY.LOCAL>
sys@NICK817.TARRY.LOCAL> set echo on
sys@NICK817.TARRY.LOCAL>
sys@NICK817.TARRY.LOCAL> create or replace function send(
2 p_from in varchar2,
3 p_to in varchar2,
4 p_cc in varchar2,
5 p_bcc in varchar2,
6 p_subject in varchar2,
7 p_body in varchar2,
8 p_smtp_host in varchar2,
9 p_attachment_data in blob,
10 p_attachment_type in varchar2,
11 p_attachment_file_name in varchar2) return number
12 as
13 language java name 'mail.send( java.lang.String,
14 java.lang.String,
15 java.lang.String,
16 java.lang.String,
17 java.lang.String,
18 java.lang.String,
19 java.lang.String,
20 oracle.sql.BLOB,
21 java.lang.String,
22 java.lang.String
23 ) return oracle.sql.NUMBER';
24 /

Function created.



[5] Grant appropriate privileges to the user using the mail to send attachements............

sys@NICK817.TARRY.LOCAL> begin
2 dbms_java.grant_permission(
3 grantee => 'APPDEV',
4 permission_type => 'java.util.PropertyPermission',
5 permission_name => '*',
6 permission_action => 'read,write'
7 );
8 dbms_java.grant_permission(
9 grantee => 'APPDEV',
10 permission_type => 'java.net.SocketPermission',
11 permission_name => '*',
12 permission_action => 'connect,resolve'
13 );
14 end;
15 /

PL/SQL procedure successfully completed.

[/code]

[6] create a table .....

sys@NICK817.TARRY.LOCAL> create table demo ( theBlob blob );

Table created.



[7] create a directory....

sys@NICK817.TARRY.LOCAL>
sys@NICK817.TARRY.LOCAL> create or replace directory my_files as 'c:\temp\';

Directory created.


[8] load this table with the attachment,you need to be familier with the dbms_lob pkg...


sys@NICK817.TARRY.LOCAL> declare
2 l_blob blob;
3 l_bfile bfile;
4 begin
5 insert into demo values ( empty_blob() )
6 returning theBlob into l_blob;
7
8 l_bfile := bfilename( 'MY_FILES', 'OTNToolbar.zip' );
9 dbms_lob.fileopen( l_bfile );
10
11 dbms_lob.loadfromfile( l_blob, l_bfile,
12 dbms_lob.getlength( l_bfile ) );
13
14 dbms_lob.fileclose( l_bfile );
15 end;
16 /

sys@NICK817.TARRY.LOCAL> commit;

Commit complete.

...........continued next(crossed the 10000 letter limit, ;) lucky that i didn't add all that load metainf from the 2 jar files..)

[Tarry]

[9] write a pl/sql block to send that attachment, i did here sending it to my other account........

sys@NICK817.TARRY.LOCAL> set serveroutput on size 1000000
sys@NICK817.TARRY.LOCAL> exec dbms_java.set_output( 1000000 )

PL/SQL procedure successfully completed.

appdev@NICK817.TARRY.LOCAL> declare
2 ret_code number;
3 begin
4 for i in (select theBlob from demo )
5 loop
6 ret_code := send(
7 p_from => 'tarry.singh@home.nl',
8 p_to => 'tarrysingh@hotmail.com',
9 p_cc => NULL,
10 p_bcc => NULL,
11 p_subject => 'Use the attached Zip file',
12 p_body => 'to send email with attachments....',
13 p_smtp_host => 'mail.home.nl',
14 p_attachment_data => i.theBlob,
15 p_attachment_type => 'application/winzip',
16 p_attachment_file_name => 'OTNToolbar.zip');
17 if ret_code = 1 then
18 dbms_output.put_line ('Successfully sent message...');
19 else
20 dbms_output.put_line ('Failed to send message...');
21 end if;
22 end loop;
23 end;
24 /
Successfully sent message...

PL/SQL procedure successfully completed.



And this way i received the toolbar.zip on my hotmail address.

HTH

Cheers

Tarry

[balajiyes]

Thnx a ton tarry
will check n revert
Cheers!

[trivedisushil]

hi due to the below

l_bfile := bfilename( 'MY_FILES', 'OTNToolbar.zip' );
9 dbms_lob.fileopen( l_bfile );

-------------------- you are able to execute this part of procedure -----------------
p_attachment_type => 'application/winzip',
16 p_attachment_file_name => 'OTNToolbar.zip');
-----------------------------------------------------------------------------------
In my scenario - I am not having any directory in my oracle database but i am having something like this
--------
SQL> desc quot_ole
Name Null? Type
------------------------------- -------- ----
QUOT_NUMBER VARCHAR2(30)
QUOT_OBJECT LONG RAW
-------
People save either word or pdf documents and unique quot_number like 'abc/2009' is assigned to each object inserted through FORMS 6i OLE Container
-----------------------------------------------------------------------------
How do i retrive this data for email - which is not having directory but either
mimetype=msword or mimetype=pdf and filename = <unknown> as users push Word of pdf files inside.

if you can further modify this code to pull the information as per the suggested table above it will be of gr8 help for me to implement.

Otherwise i am able to receive email as per above with attachment.