DBA Hub

📋Steps in this guide1/5

APEX_ZIP : Manage Zip Files From PL/SQL

The APEX_ZIP package provides an API to manage zip files from PL/SQL.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Setup

Create a directory object and make sure the test user has read/write permissions on it. Connect back to the test user and compile the file_to_blob and blob_to_file procedures. Create a table to hold files in columns. Get a zip file of your choice and put it on the "/tmp" location on the database server. In this example we are using a file called "test_file.zip", which contains PNG files in subdirectories, and includes an empty subdirectory. We are now ready to start using the the package.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

create or replace directory tmp_dir as '/tmp/';
grant read, write on directory tmp_dir to testuser1;

conn testuser1/testuser1@//localhost:1521/pdb1

@https://oracle-base.com/dba/miscellaneous/file_to_blob.sql
@https://oracle-base.com/dba/miscellaneous/blob_to_file.sql

# drop table media purge;

create table media (
  id             number generated always as identity,
  content_type   varchar2(100)  not null,
  file_name      varchar2(100)  not null,
  content        blob           not null,
  constraint media_pk primary key (id),
  constraint media_uk unique (file_name)
);
2

GET_FILES Procedure : List File and Directory Names in a Zip File

The function returns the names of the files and directories in the zip file. The following example loads the zip file into a , then uses the procedure to retrieve the list of file and directory names into a collection. It then loops through the collection displaying the file names. By default the subdirectory directory names are not included in the list. Setting the parameter to FALSE includes them in the list.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
set serveroutput on
declare
  l_zip    blob;
  l_files  apex_zip.t_files;
begin
  dbms_lob.createtemporary(l_zip, false);

  -- Load the zip file into a BLOB.
  file_to_blob(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file.zip');

  -- Get the list of files.
l_files := apex_zip.get_files(p_zipped_blob => l_zip);
-- Loop through the files and list the names.
  for i in 1 .. l_files.count loop
    dbms_output.put_line(i || ' : ' || l_files(i));
  end loop;
  
  dbms_lob.freetemporary(l_zip); 
exception
  when others then
    dbms_lob.freetemporary(l_zip);
    raise;
end;
/
1 : clone/multitenant-clone-2.png
2 : clone/multitenant-create-from-seed-2.png
3 : clone/multitenant-remote-clone-2.png
4 : unplug/multitenant-unplug-plug-2.png


PL/SQL procedure successfully completed.

SQL>

set serveroutput on
declare
  l_zip    blob;
  l_files  apex_zip.t_files;
begin
  dbms_lob.createtemporary(l_zip, false);

  -- Load the zip file into a BLOB.
  file_to_blob(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file.zip');

  -- Get the list of files.
l_files := apex_zip.get_files(p_zipped_blob => l_zip,
p_only_files  => false
);
-- Loop through the files and list the names.
  for i in 1 .. l_files.count loop
    dbms_output.put_line(i || ' : ' || l_files(i));
  end loop;
  
  dbms_lob.freetemporary(l_zip); 
exception
  when others then
    dbms_lob.freetemporary(l_zip);
    raise;
end;
/
1 : clone/
2 : clone/multitenant-clone-2.png
3 : clone/multitenant-create-from-seed-2.png
4 : clone/multitenant-remote-clone-2.png
5 : empty_dir/
6 : unplug/
7 : unplug/multitenant-unplug-plug-2.png


PL/SQL procedure successfully completed.

SQL>
3

GET_FILE_CONTENT Function : Retrieve Files From a Zip File

The function returns the file from the zip file as a . We check the contents of the table. This example builds on the first one. Instead of displaying the file name it uses the function to return the file as a , then inserts it into the table. In this example we know the files in the zip are PNG files, so we're just hard-coding the content type. In a real situation we would have to infer the content type based on the file extension. We are also not stripping the directories from the file names. Check the contents of the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
select count(*) from media;

  COUNT(*)
----------
         0

SQL>

declare
  l_zip          blob;
  l_files        apex_zip.t_files;
  l_file         blob;
begin
  dbms_lob.createtemporary(l_zip, false);

  -- Load the zip file into a BLOB.
  file_to_blob(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file.zip');

  -- Get the list of files.
  l_files := apex_zip.get_files(p_zipped_blob => l_zip);

  -- Loop through the files and list the names.
  for i in 1 .. l_files.count loop
l_file := apex_zip.get_file_content(p_zipped_blob => l_zip,
                                        p_file_name   => l_files(i));
insert into media (content_type, file_name, content)
    values ('image/png', l_files(i), l_file);
  end loop;
  commit;
  
  dbms_lob.freetemporary(l_zip); 
exception
  when others then
    dbms_lob.freetemporary(l_zip);
    raise;
end;
/

column file_name format a40

select id, file_name, length(content) as content_length
from   media;

        ID FILE_NAME                                CONTENT_LENGTH
---------- ---------------------------------------- --------------
         1 clone/multitenant-clone-2.png                      7614
         2 clone/multitenant-create-from-seed-2.png           7222
         3 clone/multitenant-remote-clone-2.png               6192
         4 unplug/multitenant-unplug-plug-2.png               6996

SQL>
4

ADD_FILE and FINISH Procedures : Create a New Zip File

The procedure adds a new file to a zip file in a . It can be called multiple times, and once the last file is added, the procedure closes off the zip file. The following example adds all the files from the media table to a new zip file and writes it out to the file system with the name "test_file_2.zip". We can check the contents of the "test_file_2.zip" file using the function.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
declare
  l_zip  blob;
begin
  -- Add the file to a new zip.
  for cur_rec in (select file_name,
                         content
                  from   media)
  loop
apex_zip.add_file(p_zipped_blob => l_zip,
                      p_file_name   => cur_rec.file_name,
                      p_content     => cur_rec.content);
end loop;

  -- Close the zip file.
apex_zip.finish(p_zipped_blob => l_zip);
-- Write the file to the file system.
  blob_to_file(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file_2.zip');
end;
/

set serveroutput on
declare
  l_zip    blob;
  l_files  apex_zip.t_files;
begin
  dbms_lob.createtemporary(l_zip, false);

  -- Load the zip file into a BLOB.
  file_to_blob(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file_2.zip');

  -- Get the list of files.
  l_files := apex_zip.get_files(p_zipped_blob => l_zip);

  -- Loop through the files and list the names.
  for i in 1 .. l_files.count loop
    dbms_output.put_line(i || ' : ' || l_files(i));
  end loop;
  
  dbms_lob.freetemporary(l_zip); 
exception
  when others then
    dbms_lob.freetemporary(l_zip);
    raise;
end;
/
1 : clone/multitenant-clone-2.png
2 : clone/multitenant-create-from-seed-2.png
3 : clone/multitenant-remote-clone-2.png
4 : unplug/multitenant-unplug-plug-2.png


PL/SQL procedure successfully completed.

SQL>
5

Comments

Here are some comments about the package. - Once the procedure is called, you cannot write any more files to the zip. - If you load an existing zip and attempt to use the add new files using the procedure, the operation will fail silently, and the new files will not be added. To add new files to an existing zip you must transfer the contents of the existing zip to a new zip, add the new files, then close the new zip. - There is no functionality to delete a file from an existing zip. To achieve this you need to create a new zip and transfer the files you want to keep to it. - The UTL_COMPRESS package uses the gzip format, but it can only handle a single file, rather than a collection of files in the archive. Depending on your use case, either or could be useful. For more information see: - APEX_ZIP - APEX_ZIP : Manage Zip Files From PL/SQL - UTL_COMPRESS : Compress and Uncompress Data from PL/SQL Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!