Home » SQL & PL/SQL » SQL & PL/SQL » EMAIL WITH ATTACHMENT FROM ORACLE 9i (merged 2)
icon6.gif  EMAIL WITH ATTACHMENT FROM ORACLE 9i (merged 2) [message #406414] Wed, 03 June 2009 23:24 Go to next message
GKANAND
Messages: 17
Registered: June 2009
Junior Member
I have written a pl/sql script to send an email from oracle to lotus notes (mail program). I am able to attach pdf and excel format. How to attach pps and jpeg format in the mail. If possible, set a background for mail template also.
Re: EMAIL WITH ATTACHMENT FROM ORACLE [message #406415 is a reply to message #406414] Wed, 03 June 2009 23:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am able to attach pdf and excel format.
>How to attach pps and jpeg format in the mail.
Why does your code treat files differently?
Re: EMAIL WITH ATTACHMENT FROM ORACLE [message #406422 is a reply to message #406414] Wed, 03 June 2009 23:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/146002/136107/

SLOW LEARNER?
Re: EMAIL WITH ATTACHMENT FROM ORACLE [message #406436 is a reply to message #406415] Thu, 04 June 2009 00:53 Go to previous messageGo to next message
GKANAND
Messages: 17
Registered: June 2009
Junior Member
The script is not attaching image files.
so tell me how to attach jpeg file from windows directory to mail.

Re: EMAIL WITH ATTACHMENT FROM ORACLE [message #406481 is a reply to message #406436] Thu, 04 June 2009 03:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As far as Oracle is concerned, one binary file is exactly the same as the next.there is no possibility that it is discriminating between image files and documents.

When you say 'The script is not attaching image files. ' what exactly do you mean?

Is the file not being attached to the email?
Is the file being attached but corrupted?
Re: EMAIL WITH ATTACHMENT FROM ORACLE [message #406505 is a reply to message #406481] Thu, 04 June 2009 05:29 Go to previous messageGo to next message
GKANAND
Messages: 17
Registered: June 2009
Junior Member
hi,

IT Means the file is attaching but we cannot open it in mail(yes it is corrupted). Cool
Re: EMAIL WITH ATTACHMENT FROM ORACLE [message #406506 is a reply to message #406505] Thu, 04 June 2009 05:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looking at your code, I'm not suprised it's corrupted. It's a miracle you could get anything out of your PDFs.

You're using UTL_FILE.GET_LINE to read the text in - you need to be using UTL_FILE.GET_RAW

Re: EMAIL WITH ATTACHMENT FROM ORACLE [message #406516 is a reply to message #406506] Thu, 04 June 2009 06:47 Go to previous messageGo to next message
GKANAND
Messages: 17
Registered: June 2009
Junior Member
HI JRowbottom

Thanks for your suggestion.

But still the file is like corrupted only. I hve attached the modified script also.

Re: EMAIL WITH ATTACHMENT FROM ORACLE [message #406521 is a reply to message #406516] Thu, 04 June 2009 06:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Having read your raw data in, you need to use utl_encode.base64_encode to convert the binary data to a 64 bit encoding, and then use UTL_SMTP.write_raw_data to send this data to the SMTP server.

Oracle have a nice example here

Or you could put UTL_SMTP ATTACHMENT into Google and get half a dozen solutions on the first page,

Or you could just use Utl_Mail, which does all this for you.
Re: EMAIL WITH ATTACHMENT FROM ORACLE 9i (merged 2) [message #406527 is a reply to message #406414] Thu, 04 June 2009 07:24 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Dear GKANAND,your procedure is not able to attach excel file but is working fine for text document,when attaching excel file it is throwing an error
DECLARE
*
ERROR at line 1:
ORA-20014: ORA-29284: file read error
ORA-06512: at line 210

so if you are not facing such problem then sort me out.

Re: EMAIL WITH ATTACHMENT FROM ORACLE 9i (merged 2) [message #406696 is a reply to message #406527] Fri, 05 June 2009 07:11 Go to previous messageGo to next message
GKANAND
Messages: 17
Registered: June 2009
Junior Member
The excel file is saved as type 'Text (Tab elimited)' as per our requirement.
Re: EMAIL WITH ATTACHMENT FROM ORACLE 9i (merged 2) [message #406701 is a reply to message #406696] Fri, 05 June 2009 07:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is not an Excel file, but a tab-delimited textfile. The fact that you created it using Excel is not relevant.
Previous Topic: ORA-01001
Next Topic: Retrieve row exclusively - performance
Goto Forum:
  


Current Time: Fri Dec 09 15:37:51 CST 2016

Total time taken to generate the page: 0.14657 seconds