Wednesday, 6 August 2014

APEX5 EA APEX_ZIP usage

I recently saw a link to a presentation that made mention of APEX_ZIP for the upcoming release of APEX (Version 5). Currently in early adopter, you can have a play around. So just thought I'd go and have a bit more of a play to see the possiblities.

On the presentation, and specifically for extracting files the sample code is:

declare
    l_zip_file  BLOB;
    l_unzipped_file     BLOB;
    l_files             apex_zip.t_files;
begin

    select file_content
    into l_zip_file
    from my_zip_files
    where file_name = :P13_file_name;
    
    l_files := apex_zip.get_files(p_zipped_blob => l_zip_file);
    
    for i in 1..l_files.COUNT
    LOOP
        l_unzipped_file := apex_zip.get_file_content(p_zipped_blob      => l_zip_file,
                                                     p_file_name        => l_files(i));
                                                     
        insert into my_files (file_name, file_content)
        values (l_files(i), l_unzipped_file);
    END LOOP;

end;


With a bit of looking around, we can soon find that APEX_ZIP refers to WWV_FLOW_ZIP, and we can grab the package specification by querying all_source. It seems that the package is derived from that code originally shared on the OTN forums here: https://community.oracle.com/message/4509996

Side note: If the zip file contains folders, you may need to get familiar with string functions to weed out only the folders you are interested in.

The other common usage would of course be creating a zip:

declare
    l_output_zip BLOB;
    l_filename varchar2(200) := 'newzip.zip';
begin

    for file in (
        select filename, contents
        from unzipped_files
    )
    LOOP
    
        apex_zip.add_file(
            p_zipped_blob       => l_output_zip
          , p_file_name         => file.filename
          , p_content           => file.contents
        );
    
    END LOOP;
    
    apex_zip.finish(
        p_zipped_blob           => l_output_zip
    );
    
    insert into zipped_files (mime_type, filename, contents) 
        values ('application/zip', l_filename, l_output_zip);


end;

If you wanted to put files in a specific folder, you just need to prepend the file name with the folder name and a forward slash: 'folder/' || file.filename

nb: it is essential to call apex_zip.finish once all files have been added


No comments:

Post a Comment