Home » SQL & PL/SQL » SQL & PL/SQL » send email with attachement (merged 3)
send email with attachement (merged 3) [message #419178] Thu, 20 August 2009 05:12 Go to next message
raywong9i
Messages: 40
Registered: April 2008
Member
I am try to send an email with attachemnt using UTL_SMTP package.
However, we cannot use the UTL_MAIL, because our database is still Oracle 9i version.
The attached file is an Excel worksheet.

I have try to search from many place, I can just found the Oracle Mail demo , and some example
for sending text file.

I have found the following procedure from the web, but I am not sure how to call it ><"

PROCEDURE ATTACH_BASE64
(P_CONN IN OUT NOCOPY UTL_SMTP.connection
,P_DATA IN BLOB
,P_MIME_TYPE IN VARCHAR2 := 'application/octet'
,P_INLINE IN BOOLEAN := TRUE
,P_FILENAME IN VARCHAR2 := NULL
,P_LAST IN BOOLEAN := FALSE
)
IS

i PLS_INTEGER;
len PLS_INTEGER;
BEGIN
BEGIN_ATTACHMENT(p_conn, p_mime_type, p_inline, p_filename, 'base64');

-- Split the Base64-encoded attachment into multiple lines
i := 1;
len := DBMS_LOB.getLength(p_data);

WHILE (i < len) LOOP
IF(i + MAX_BASE64_LINE_WIDTH < len)THEN
UTL_SMTP.Write_Raw_Data (p_conn
, UTL_ENCODE.Base64_Encode(
DBMS_LOB.Substr(p_data, MAX_BASE64_LINE_WIDTH, i)
)
);
ELSE
UTL_SMTP.Write_Raw_Data (p_conn
, UTL_ENCODE.Base64_Encode(
DBMS_LOB.Substr(p_data, (len - i), i)
)
);
END IF;

UTL_SMTP.Write_Data(p_conn, UTL_TCP.CRLF);
i := i + MAX_BASE64_LINE_WIDTH;
END LOOP;

END_ATTACHMENT(p_conn, p_last);
END;

And my excel file is in the filesystem (C:\Reports), but not stored in database

Does any one have some example for send excel file as attachment ?



Thanks a lot ?
Re: send email with attachment [message #419182 is a reply to message #419178] Thu, 20 August 2009 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does any one have some example for send excel file as attachment ?

Is this not what the procedure does?
The file must be on the database server.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Thu, 20 August 2009 05:23]

Report message to a moderator

Re: send email with attachement [message #419284 is a reply to message #419178] Thu, 20 August 2009 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

http://www.orafaq.com/forum/?SQ=8e307f7fe71d1bbe678c387865a448f1&t=search&srch=mail+attachment&btn_submit=Search&fiel d=all&forum_limiter=&search_logic=AND&sort_order=DESC&author=

>However, we cannot use the UTL_MAIL, because our database is still Oracle 9i version.
Lame excuse.
My Production DB was at V7.3.4.5 when V8.1.6 was released which was 1st version containing UTL_SMPT.
I installed V8.1.6 so I could call a procedure in it & send out the desire message.
So you could install V10 or V11 & use new instance and UTL_MAIL
Re: send email with attachement (merged 3) [message #419299 is a reply to message #419178] Fri, 21 August 2009 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you think reposting the same thing (twice) the next day and still not formatted will change the answer?
Will you repost it 4 times tomorrow?

Regards
Michel

[Updated on: Fri, 21 August 2009 01:13]

Report message to a moderator

Re: send email with attachement (merged 3) [message #419426 is a reply to message #419299] Sat, 22 August 2009 02:17 Go to previous messageGo to next message
raywong9i
Messages: 40
Registered: April 2008
Member
Michael,

I am not trying to post it several time, when I post the new topic , after I pressed the Create post button, there is no response to my 'screen'. The page just keep loading and loading, until the timeout error shown. So, I repress the button .

sorry about that
Re: send email with attachement (merged 3) [message #419451 is a reply to message #419178] Sat, 22 August 2009 09:16 Go to previous message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>So, I repress the button .
Exactly what prevents you from actually checking this forum before "repress button"?

PEBKAC!
Previous Topic: Create sp
Next Topic: ora 12034 view log is younger than last refresh
Goto Forum:
  


Current Time: Wed Dec 07 14:27:20 CST 2016

Total time taken to generate the page: 0.10466 seconds