Home » SQL & PL/SQL » SQL & PL/SQL » Invalid zipped file after using UTL_FILE and UTL_SMTP
Invalid zipped file after using UTL_FILE and UTL_SMTP [message #387939] Sun, 22 February 2009 01:09 Go to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Hi all
I'm using UTL_SMTP to send mail. Using UTL_FILE I'm creating a text file then zipping it with tar utility
After that, using procedure that send mail, send it as attachment. But there's a problem with zipped file. Its structure becomes invalid. Looking to my mail procedure, I find out that I open the file using

UTL_FILE.fopen ('DIRECTORY', file, 'r');


Then by opening LOOP, I'm getting this file line by line using this code
UTL_FILE.get_line (v_f_handle, v_line);

Then to send it as attachment, I write it to UTL_SMTP package using this code
UTL_SMTP.write_data (conn, v_line||clrf);


I'm getting e-mail with attachment, but my tar file's structure is invalid
How can I fix this problem?
Thanks
Re: File operation(UTL_FILE) [message #387940 is a reply to message #387939] Sun, 22 February 2009 01:14 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>I'm getting e-mail with attachment, but my tar file's structure is invalid
So, please explain again what YOUR email problem has to do with Oracle.
SMTP is totally external to Oracle, so please take it up with your postmaster.
Re: File operation(UTL_FILE) [message #387942 is a reply to message #387939] Sun, 22 February 2009 01:56 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

I don't get any error while sending normal text file, I'm getting error while sending zipped tar file via UTL_SMTP
Re: Invalid zipped file after using UTL_FILE and UTL_SMTP [message #388060 is a reply to message #387939] Mon, 23 February 2009 01:34 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Kamran Agayev wrote on Sun, 22 February 2009 08:09
Hi all
I'm using UTL_SMTP to send mail. Using UTL_FILE I'm creating a text file then zipping it with tar utility
After that, using procedure that send mail, send it as attachment. But there's a problem with zipped file. Its structure becomes invalid. Looking to my mail procedure, I find out that I open the file using

UTL_FILE.fopen ('DIRECTORY', file, 'r');


You spotted it right. The next step is consulting the PL/SQL Packages and Types Reference documentation (as it contains UTL_FILE description), available e.g. online on http://tahiti.oracle.com/.

For 10gR2, the link for UTL_FIEL.FOPEN is here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003526
Quote:
Parameter open_mode
Specifies how the file is opened. Modes include:
r -- read text
w -- write text
a -- append text
rb -- read byte mode
wb -- write byte mode
ab -- append byte mode
If you try to open a file specifying 'a' or 'ab' for open_mode but the file does not exist, the file is created in write mode.

"Normal text file" contains only text. Zipped TAR file is binary.
Re: File operation(UTL_FILE) [message #388065 is a reply to message #387940] Mon, 23 February 2009 01:43 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
BlackSwan wrote on Sun, 22 February 2009 08:14
>I'm getting e-mail with attachment, but my tar file's structure is invalid
So, please explain again what YOUR email problem has to do with Oracle.
SMTP is totally external to Oracle, so please take it up with your postmaster.


Would you kindly apologize towards Kamran as it seems that your assumption is wrong, and the error lies within - the proper use of - Oracle as flyboy is pointing out Wink

SOLVED Re: Invalid zipped file after using UTL_FILE and UTL_SMTP [message #388086 is a reply to message #387939] Mon, 23 February 2009 02:49 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Thanks for response, I did it using rb option, but result was same
But I solved it using another procedure from this address, the last post

http://glufke.net/oracle/viewtopic.php?t=207
Re: SOLVED Re: Invalid zipped file after using UTL_FILE and UTL_SMTP [message #388094 is a reply to message #388086] Mon, 23 February 2009 03:27 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Kamran Agayev wrote on Mon, 23 February 2009 09:49
Thanks for response, I did it using rb option, but result was same
But I solved it using another procedure from this address, the last post

http://glufke.net/oracle/viewtopic.php?t=207

Thank you for feedback.

I spotted only the first code problem I found.
The whole approach (reading lines and putting them into message body) makes no sense for a binary file. As this approach is used in the example you posted ("line" containing up to 1000 characters), I am curious whether the "line truncating" (exception MESG_TOO_LONG) does not come into place (as it corrupts the content). It would be worth trying using the file containing more than 1000 characters in one "line" (text or binary).

Maybe it would be easier to send a file as an attachment using UTL_MAIL package. I think there were some posts about it in this forum.
Previous Topic: Help in Pivot Query...
Next Topic: Query regarding order by??
Goto Forum:
  


Current Time: Sat Dec 03 14:16:18 CST 2016

Total time taken to generate the page: 0.04402 seconds