7 Replies Latest reply on May 19, 2013 5:08 AM by jeneesh

    Creating zip files in pl/sql.

    846961
      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.
        • 1. Re: Creating zip files in pl/sql.
          ascheffer
          Your first example looks like I would expect (or do I miss something?).
          Your second example is strange, but it I can't reproduce it. If I zip a zipfile with my package it contains only the zipfile:
          declare
            my_zip blob;
            new_zip blob;
            zip_files as_zip.file_list;
          --
            function file2blob(
              p_dir in varchar2
            , p_file_name in varchar2
            )
              return blob
            is
              file_lob bfile;
              file_blob blob;
            begin
              file_lob := bfilename( p_dir
                                   , p_file_name
                                   );
              dbms_lob.open( file_lob
                           , dbms_lob.file_readonly
                           );
              dbms_lob.createtemporary( file_blob
                                      , true
                                      );
              dbms_lob.loadfromfile( file_blob
                                   , file_lob
                                   , dbms_lob.lobmaxsize
                                   );
              dbms_lob.close( file_lob );
              return file_blob;
            exception
              when others
              then
                if dbms_lob.isopen( file_lob ) = 1
                then
                  dbms_lob.close( file_lob );
                end if;
                if dbms_lob.istemporary( file_blob ) = 1
                then
                  dbms_lob.freetemporary( file_blob );
                end if;
                raise;
            end;
          begin
            my_zip := file2blob( 'MY_DIR', 'as_fop.zip' );
          --
            dbms_output.put_line('zip file to start with');
            zip_files := as_zip.get_file_list( my_zip );
            for i in zip_files.first() .. zip_files.last
            loop
              dbms_output.put_line( zip_files( i ) ); 
            end loop;
            dbms_output.put_line('--');
          --  now create a new zip file containing the existing zip file as_fop.zip
            as_zip.add1file( new_zip, 'as_fop.zip', my_zip );
            as_zip.finish_zip( new_zip );
          --
          --  see what's in the new zip
            dbms_output.put_line('zip file containing a zipfile');
            zip_files := as_zip.get_file_list( new_zip );
            for i in zip_files.first() .. zip_files.last
            loop
              dbms_output.put_line( zip_files( i ) ); 
            end loop;
            dbms_output.put_line('--');
          --
          end;
          /
          
          Connected to:
          Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
          
          zip file to start with
          as_fop.sql
          --
          zip file containing a zipfile
          as_fop.zip
          --
          
          PL/SQL procedure successfully completed.
          
          ANTON@XE>
          Anton
          • 2. Re: Creating zip files in pl/sql.
            846961
            Thanks for looking into this Anton.
            I'll check my code again.
            • 3. Re: Creating zip files in pl/sql.
              user12173926
              Hi SK, did you ever solve this problem (when adding a zip file to a zip file you ended up with the uncompressed version as well)? I'm getting the exact same thing and banging my head against a wall!!

              Jules
              • 4. Re: Creating zip files in pl/sql.
                Paul  Horth
                user12173926 wrote:
                Hi SK, did you ever solve this problem (when adding a zip file to a zip file you ended up with the uncompressed version as well)? I'm getting the exact same thing and banging my head against a wall!!

                Jules
                Welcome to the forum.

                It is forum etiquette not to reply to an old thread.

                Start one of your own, perhaps referencing the old thread.
                • 5. Re: Creating zip files in pl/sql.
                  APC
                  SK last visted these forums in Mar 2011. But who knows, perhaps he'll check in next monday to mark the second anniversary of his last posting.

                  Cheers, APC
                  • 6. Re: Creating zip files in pl/sql.
                    1009738
                    HI Experts,

                    Yes, i have followed your below steps to create zip file and it is very nice but i want something different here. How to add multiple lines in same file ?

                    Suppose i have one procedure which would extract some data and create one file say Text1.txt . I want to add it in zip file.As per your code in below forums i will use code zz_zip.add1file(b_zipped_blob, v_file_name, v_new_blob);

                    for above code we need file data in variable form v_new_blob.

                    How to cast entire file data in v_new_blob.

                    Is there any other way to achieve this then please post it along with the code . I will highly appreciate your post.

                    Please suggest me here as this is very crucial point of my life.



                    Thanks,
                    Nirmal
                    • 7. Re: Creating zip files in pl/sql.
                      jeneesh
                      I will repeat:

                      Welcome to the forum.

                      It is forum etiquette not to reply to an old thread.

                      Start one of your own, perhaps referencing the old thread.