In Oracle 12c onwards, on which the Oracle Apex is by default installed, you can use APEX_ZIP
PL/SQL package to zip the files. So here I am giving a few Oracle APEX_ZIP
examples:
Oracle APEX_ZIP Example
The following PL/SQL code gets the files (BLOB
) from a table and zip it, and gives you a final BLOB
that has all the files you added.
declare
b_zip_file blob;
cursor c_files
is
select file_name,
file_content
from emp_files
where empno = 7894;
begin
for c in c_files
loop
-- adds the each blob to b_zip_file blob one by one
apex_zip.add_file (
p_zipped_blob => b_zip_file,
p_file_name => c.file_name,
p_content => c.file_content );
end loop;
-- finalizes the blob
apex_zip.finish (
p_zipped_blob => b_zip_file );
end;
You can see in the above code that at the end, you will get the final BLOB
which you can further store in the table or send it to a procedure or write a file on the server.
The following example is an addition to the above code. After zipping the files, it will write the zip file to the server.
Zip Files using APEX_ZIP package and Write to the Server
To write the file on the server, you need an Oracle Directory object. Below is an example, how to create a directory object in Oracle pointing to a directory on the server.
Create or Replace directory my_dir as '/your/server/path';
After creating the directory, you can write the zip file as shown in the below example:
declare
b_zip_file blob;
-- variables for writing the files
l_file UTL_FILE.file_type;
l_buffer RAW (32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
-- end variable declaration for file
cursor c_files
is
select file_name,
file_content
from emp_files
where empno = 7894;
begin
for c in c_files
loop
-- adds the each blob to b_zip_file blob one by one
apex_zip.add_file (
p_zipped_blob => b_zip_file,
p_file_name => c.file_name,
p_content => c.file_content );
end loop;
-- finalizes the blob
apex_zip.finish (
p_zipped_blob => b_zip_file );
-- write the file
l_blob_len := DBMS_LOB.getlength (b_zip_file);
l_file :=
UTL_FILE.fopen ('MY_DIR',
'my_zip.zip',
'WB',
32767);
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.read (b_zip_file,
l_amount,
l_pos,
l_buffer);
UTL_FILE.put_raw (l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.fclose (l_file);
end;
After running the above PL/SQL code, you will find the file my_zip.zip
at the MY_DIR
directory location on the server.