Creating zip files in pl/sql.
846961Mar 13 2011 — edited May 19 2013I'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.