Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Creating zip files in pl/sql.

846961Mar 13 2011 — edited May 19 2013
I'm using code posted on this forum to create zip file in pl/sql. Works greate for combing multiple files into one archive. That's until I have to add a zip file.
Here's my test:

drop table t1;

create table t1 (file_name varchar2(100), file_blob blob);

declare
v_new_blob blob;
v_file_name varchar2(100);
v_buffer_raw raw(1000);
v_length integer;
b_zipped_blob BLOB;
zip_files zz_zip.file_list;
t_file blob;

begin
dbms_lob.createtemporary(v_new_blob,true);

v_buffer_raw := UTL_RAW.cast_to_raw('AAAA');
v_length := UTL_RAW.length(v_buffer_raw);
dbms_lob.writeappend(v_new_blob, v_length, v_buffer_raw);

insert into t1 values ('tmp\FileA.txt',v_new_blob);

dbms_lob.freetemporary(v_new_blob);
--
dbms_lob.createtemporary(v_new_blob,true);

v_buffer_raw := UTL_RAW.cast_to_raw('BBBBBBBBB');
v_length := UTL_RAW.length(v_buffer_raw);
dbms_lob.writeappend(v_new_blob, v_length, v_buffer_raw);

insert into t1 values ('tmp\FileB.txt',v_new_blob);

dbms_lob.freetemporary(v_new_blob);
--
dbms_lob.createtemporary(v_new_blob,true);

v_buffer_raw := UTL_RAW.cast_to_raw('CCCCCCCCCCCCC');
v_length := UTL_RAW.length(v_buffer_raw);
dbms_lob.writeappend(v_new_blob, v_length, v_buffer_raw);

insert into t1 values ('tmp\FileC.txt',v_new_blob);

dbms_lob.freetemporary(v_new_blob);
--
dbms_lob.createtemporary(v_new_blob,true);

v_buffer_raw := UTL_RAW.cast_to_raw('DDDDDDDDDDDDDDDDDDDDDDDDDD');
v_length := UTL_RAW.length(v_buffer_raw);
dbms_lob.writeappend(v_new_blob, v_length, v_buffer_raw);

insert into t1 values ('tmp\FileD.txt',v_new_blob);

dbms_lob.freetemporary(v_new_blob);
--
commit;
--
select file_name, file_blob into v_file_name, v_new_blob
from t1 where file_name = 'tmp\FileA.txt';
zz_zip.add1file(b_zipped_blob, v_file_name, v_new_blob);
--
select file_name, file_blob into v_file_name, v_new_blob
from t1 where file_name = 'tmp\FileB.txt';
zz_zip.add1file(b_zipped_blob, v_file_name, v_new_blob);
--
select file_name, file_blob into v_file_name, v_new_blob
from t1 where file_name = 'tmp\FileC.txt';
zz_zip.add1file(b_zipped_blob, v_file_name, v_new_blob);
--
zz_zip.finish_zip(b_zipped_blob);
--
zip_files := zz_zip.get_file_list( b_zipped_blob );
--

for i in zip_files.first() .. zip_files.last
loop
dbms_output.put_line( zip_files( i ) );
end loop;
dbms_output.put_line('--');

-- Output
/*
FileA.txt
FileB.txt
FileC.txt
*/
-- Save zip in t1
insert into t1 values ('ZipZZ.zip',b_zipped_blob);
commit;

-- Create new zip file and add ZipZZ.zip and FileD.txt
b_zipped_blob := null;
--
select file_name, file_blob into v_file_name, v_new_blob
from t1 where file_name = 'ZipZZ.zip';
zz_zip.add1file(b_zipped_blob, v_file_name, v_new_blob);
--
select file_name, file_blob into v_file_name, v_new_blob
from t1 where file_name = 'tmp\FileD.txt';
zz_zip.add1file(b_zipped_blob, v_file_name, v_new_blob);
--
zz_zip.finish_zip(b_zipped_blob);
--
zip_files := zz_zip.get_file_list( b_zipped_blob );
--
for i in zip_files.first() .. zip_files.last
loop
dbms_output.put_line( zip_files( i ) );
end loop;
dbms_output.put_line('--');

/* output
ZipZZ.zip
tmp\FileD.txt
*/
-- save ZipXX.zip
insert into t1 values ('ZipXX.zip',b_zipped_blob);
commit;
end;

-----------------------------------------------------
--Test with other zip files.

insert into t1 values ('File1.zip',null);

commit;
-- I've created a small zip file File1.zip using Winzip. It contains only 1 small text file File1.txt
-- I use toad to insert into blob column.


declare
v_new_blob blob;
v_file_name varchar2(100);
v_buffer_raw raw(1000);
v_length integer;
b_zipped_blob BLOB;
zip_files zz_zip.file_list;
t_file blob;

begin

select file_name, file_blob into v_file_name, v_new_blob
from t1 where file_name = 'File1.zip';

dbms_output.put_line('Blob length: '||dbms_lob.getlength(v_new_blob));

zz_zip.add1file(b_zipped_blob, v_file_name, v_new_blob);
--
zz_zip.finish_zip(b_zipped_blob);
--
zip_files := zz_zip.get_file_list( b_zipped_blob );
--
for i in zip_files.first() .. zip_files.last
loop
dbms_output.put_line( zip_files( i ) );
end loop;
dbms_output.put_line('--');

-- save new file as Zip1.zip
insert into t1 values ('ZipFF.zip',b_zipped_blob);
commit;

end;

/*
Output
Blob length: 1855
File1.zip
File1.txt
--
*/

Now, new zip file contains both File1.zip and File.txt.
My expected result was just File1.zip

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 16 2013
Added on Mar 13 2011
7 comments
32,461 views