This content has been marked as final. Show 16 replies
This new package delivers the familiar functionality of zip and unzip utilities in a PL/SQL environment. It lets you compress and uncompress a RAW bytestream, and guarantees the exact original bytestream after the round trip. This package also handles lobs, and has features to add and later extract several pieces into a single archive.
If you use Oracle Forms , then you can achieve this with DDE forms built-in package or HOST procedure...
Refer to Forms documentation to find out the syntax and examples...
I have a similar requirement, i.e. to extract the data from a file inside a zip file. As far as I understand it UTL_COMPRESS does not support extracting a compressed file from a zip file. Zip files created with a utility like WinZip performs two tasks: Compression and Archiving. UTL_COMPRESS does the compression part but not the archiving. Do I understand this correctly?
Is there another PL/SQL API that I can use to extract files from a Zip archive?
Did you try UTL_COMPRESS.lz_uncompress?
Yes I did try UTL_COMPRESS.lz_uncompress. So I got an invalid data error. But apart from that, the UTL_COMPRESS API does not support listing or extracting files from an archive. It does support a piecewise extract but this did not work either and there is no way to specify a specific file to extract. Or please tell me I am missing something?
Did you use UTL_COMPRESS to compress the file that you're trying to extract? I tried the example in this link and it works for me.
No, as I said before I am trying to unzip a file that was created with a program like Winzip, i.e. a file like aaa.zip.
I use a home-made java code in database for zipping. But I don't unzip...
What about zip in Oracle 9i? Is that UTL package avilable in Oracle 9i?
Schedule a vb-script or .bat to uncompress the file at a given time, then use the UTL_COMPRESS to compress it and then you can decompress it with oracle when you want.
The above link is a HOWTO entry and not a "known bug".
I may be missing something but I still cannot see how the UTL_COMPRESS package in principle can handle zip archives. In order to do so would require a function to list the contents of the archive, i.e. the individual files within the zip file.
The above link is a HOWTO entry and not a "known bug".You are right: maybe it is more of a restriction than a bug.
Anyway, I tried the following which works per se, but the output is not a valid zip file, eventhough the data is compressed.
For compression I did:
For uncompression I did:
DECLARE f UTL_FILE.file_type; compressed BLOB; uncompressed BLOB := UTL_RAW.cast_to_raw ('HelloWorld'); BEGIN f := UTL_FILE.fopen ('TEMP', 'HelloWorld.zip', 'wb'); DBMS_LOB.createtemporary (compressed, FALSE); UTL_COMPRESS.lz_compress (uncompressed, compressed, 6); UTL_FILE.put_raw (f, compressed); UTL_FILE.fclose (f); DBMS_LOB.freetemporary (compressed); END;
As said, you can read and write from the "zip" file, but no file listing (as you noted yourself) is available. But maybe, if one elaborates more on this, one can modify the bytestream to add some zip headers to make the file zip-compatible ...
DECLARE f UTL_FILE.file_type; compressed BLOB; uncompressed BLOB; fexists BOOLEAN; file_length NUMBER; block_size BINARY_INTEGER; BEGIN UTL_FILE.fgetattr ('TEMP', 'HelloWorld.zip', fexists, file_length, block_size ); f := UTL_FILE.fopen ('TEMP', 'HelloWorld.zip', 'rb'); UTL_FILE.get_raw (f, compressed, file_length); uncompressed := UTL_COMPRESS.lz_uncompress (compressed); DBMS_OUTPUT.put_line (UTL_RAW.cast_to_varchar2 (uncompressed)); UTL_FILE.fclose (f); END;
I'm aware of but haven't used utl_compress, but it makes sense that it wont handle archives.
With that limitation in mind, I would go down the path of calling an OS utility from the database. There are several methods for making OS calls from the database (note that this is the OS for the database server) and several ways of doing this from the client (ie Oracle Forms) but I don't see the point. Just zip it locally.