1 2 Previous Next 20 Replies Latest reply: Jan 30, 2012 3:31 AM by Marwim Go to original post RSS
      • 15. Re: UTL_COMPRESS Zipping Multiple BLOBs
        893117
        I though I was copying all 4 programs ( one function and three procedures ) it as is, looking for typo's again.

        And little did I learn, I found a typo, it works great,, thanks so much.
        • 16. Re: UTL_COMPRESS Zipping Multiple BLOBs
          user466593
          Hi,

          Thank you so much for this.
          However, it does not work if the documents to be zipped together are already using the same compression algorigthm (ex: docx, xlsx, zip, ...).
          Information of the central directory from the compressed file(s) to be zipped are then duplicated into the central directory of the new files.
          In the case of docx, you will then see the internal structure of the file.
          My version fixes this issue.

          I also added the option of choosing the appropriate file date

          CREATE OR REPLACE PACKAGE BODY zip_blobs AS
            --
          
            TYPE offsets IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
          
            g_offsets offsets;
          
            FUNCTION little_endian(p_big IN NUMBER, p_bytes IN PLS_INTEGER := 4) RETURN RAW IS
            BEGIN
              RETURN utl_raw.substr(utl_raw.cast_from_binary_integer(p_big, utl_raw.little_endian),
                                    1,
                                    p_bytes);
            END;
            --
            PROCEDURE add1file(p_zipped_blob IN OUT BLOB,
                               p_name        IN VARCHAR2,
                               p_content     IN BLOB,
                               p_timestamp   IN DATE DEFAULT SYSDATE) IS
              t_now     DATE;
              t_blob    BLOB;
              t_clen    INTEGER;
              v_file_nb INTEGER;
            BEGIN
              t_now  := p_timestamp;
              t_blob := utl_compress.lz_compress(p_content);
              t_clen := dbms_lob.getlength(t_blob);
              IF p_zipped_blob IS NULL THEN
                dbms_lob.createtemporary(p_zipped_blob, TRUE);
              END IF;
            
              v_file_nb := g_offsets.count + 1;
              g_offsets(v_file_nb) := dbms_lob.getlength(p_zipped_blob) + 1;
            
              dbms_lob.append(p_zipped_blob,
                              utl_raw.concat(hextoraw('504B0304') -- Local file header signature
                                            ,
                                             hextoraw('1400') -- version 2.0
                                            ,
                                             hextoraw('0000') -- no General purpose bits
                                            ,
                                             hextoraw('0800') -- deflate
                                            ,
                                             little_endian(to_number(to_char(t_now, 'ss')) / 2 +
                                                           to_number(to_char(t_now, 'mi')) * 32 +
                                                           to_number(to_char(t_now, 'hh24')) * 2048,
                                                           2) -- File last modification time
                                            ,
                                             little_endian(to_number(to_char(t_now, 'dd')) +
                                                           to_number(to_char(t_now, 'mm')) * 32 +
                                                           (to_number(to_char(t_now, 'yyyy')) - 1980) * 512,
                                                           2) -- File last modification date
                                            ,
                                             dbms_lob.substr(t_blob, 4, t_clen - 7) -- CRC-32
                                            ,
                                             little_endian(t_clen - 18) -- compressed size
                                            ,
                                             little_endian(dbms_lob.getlength(p_content)) -- uncompressed size
                                            ,
                                             little_endian(length(p_name), 2) -- File name length
                                            ,
                                             hextoraw('0000') -- Extra field length
                                            ,
                                             utl_raw.cast_to_raw(p_name) -- File name
                                             ));
              -- dbms_lob.append(p_zipped_blob, dbms_lob.substr(t_blob, t_clen - 18, 11)); -- compressed content
              dbms_lob.copy(p_zipped_blob,
                            t_blob,
                            t_clen - 18,
                            dbms_lob.getlength(p_zipped_blob) + 1,
                            11);
              dbms_lob.freetemporary(t_blob);
            END;
            --
            PROCEDURE finish_zip(p_zipped_blob IN OUT BLOB) IS
              t_cnt PLS_INTEGER := 0;
            
              t_offs_dir_header INTEGER;
              t_offs_end_header INTEGER;
              t_comment         RAW(32767) := utl_raw.cast_to_raw('Implementation by Anton Scheffer');
            BEGIN
              t_offs_dir_header := dbms_lob.getlength(p_zipped_blob);
            
              htp.p(dbms_lob.substr(p_zipped_blob,
                                    utl_raw.cast_to_binary_integer(dbms_lob.substr(p_zipped_blob,
                                                                                   2,
                                                                                   g_offsets(1) + 26),
                                                                   utl_raw.little_endian),
                                    g_offsets(1) + 30));
            
              FOR i IN 1 .. g_offsets.count LOOP
                dbms_lob.append(p_zipped_blob,
                                utl_raw.concat(hextoraw('504B0102') -- Central directory file header signature
                                              ,
                                               hextoraw('1400') -- version 2.0
                                              ,
                                               dbms_lob.substr(p_zipped_blob, 26, g_offsets(i) + 4),
                                               hextoraw('0000') -- File comment length
                                              ,
                                               hextoraw('0000') -- Disk number where file starts
                                              ,
                                               hextoraw('0100') -- Internal file attributes
                                              ,
                                               hextoraw('2000B681') -- External file attributes
                                              ,
                                               little_endian(g_offsets(i) - 1) -- Relative offset of local file header
                                              ,
                                               dbms_lob.substr(p_zipped_blob,
                                                               utl_raw.cast_to_binary_integer(dbms_lob.substr(p_zipped_blob,
                                                                                                              2,
                                                                                                              g_offsets(i) + 26),
                                                                                              utl_raw.little_endian),
                                                               g_offsets(i) + 30) -- File name
                                               ));
                t_cnt := t_cnt + 1;
              END LOOP;
            
              t_offs_end_header := dbms_lob.getlength(p_zipped_blob);
              dbms_lob.append(p_zipped_blob,
                              utl_raw.concat(hextoraw('504B0506') -- End of central directory signature
                                            ,
                                             hextoraw('0000') -- Number of this disk
                                            ,
                                             hextoraw('0000') -- Disk where central directory starts
                                            ,
                                             little_endian(t_cnt, 2) -- Number of central directory records on this disk
                                            ,
                                             little_endian(t_cnt, 2) -- Total number of central directory records
                                            ,
                                             little_endian(t_offs_end_header - t_offs_dir_header) -- Size of central directory
                                            ,
                                             little_endian(t_offs_dir_header) -- Relative offset of local file header
                                            ,
                                             little_endian(nvl(utl_raw.length(t_comment), 0), 2) -- ZIP file comment length
                                            ,
                                             t_comment));
            
              g_offsets.delete();
            END;
            --
            PROCEDURE save_zip(p_zipped_blob IN BLOB, p_dir IN VARCHAR2, p_filename IN VARCHAR2) IS
              t_fh  utl_file.file_type;
              t_len PLS_INTEGER := 32767;
            BEGIN
              t_fh := utl_file.fopen(p_dir, p_filename, 'wb');
              FOR i IN 0 .. trunc((dbms_lob.getlength(p_zipped_blob) - 1) / t_len) LOOP
                utl_file.put_raw(t_fh, dbms_lob.substr(p_zipped_blob, t_len, i * t_len + 1));
              END LOOP;
              utl_file.fclose(t_fh);
            END;
          END zip_blobs;
          Thanks again
          • 17. Re: UTL_COMPRESS Zipping Multiple BLOBs
            ascheffer
            I've noticed that too, I changed as
            CREATE OR REPLACE package body as_zip
            is
            --
              c_LOCAL_FILE_HEADER       constant raw(4) := hextoraw( '504B0304' ); -- Local file header signature
              c_END_OF_CENTAL_DIRECTORY constant raw(4) := hextoraw( '504B0506' ); -- End of central directory signature
            --
              function little_endian( p_big number, p_bytes pls_integer := 4 )
              return raw
              is
              begin
                return utl_raw.substr( utl_raw.cast_from_binary_integer( p_big, utl_raw.little_endian ), 1, p_bytes );
              end;
            --
              procedure add1file
                ( p_zipped_blob in out blob
                , p_name varchar2
                , p_content blob
                )
              is
                t_now date;
                t_blob blob;
                t_len integer;
                t_clen integer;
                t_crc32 raw(4) := hextoraw( '00000000' );
                t_compressed boolean := false;
              begin
                t_now := sysdate;
                t_len := nvl( dbms_lob.getlength( p_content ), 0 );
                if t_len > 0
                then 
                  t_blob := utl_compress.lz_compress( p_content );
                  t_clen := dbms_lob.getlength( t_blob ) - 18;
                  t_compressed := t_clen < t_len;
                  t_crc32 := dbms_lob.substr( t_blob, 4, t_clen + 11 );       
                end if;
                if not t_compressed
                then 
                  t_clen := t_len;
                  t_blob := p_content;
                end if;
                if p_zipped_blob is null
                then
                  dbms_lob.createtemporary( p_zipped_blob, true );
                end if;
                dbms_lob.append( p_zipped_blob
                               , utl_raw.concat( c_LOCAL_FILE_HEADER -- Local file header signature
                                               , hextoraw( '1400' )     -- version 2.0
                                               , hextoraw( '0000' )     -- no General purpose bits
                                               , case when t_compressed
                                                    then hextoraw( '0800' ) -- deflate
                                                    else hextoraw( '0000' ) -- stored
                                                 end
                                               , little_endian( to_number( to_char( t_now, 'ss' ) ) / 2
                                                              + to_number( to_char( t_now, 'mi' ) ) * 32
                                                              + to_number( to_char( t_now, 'hh24' ) ) * 2048
                                                              , 2
                                                              ) -- File last modification time
                                               , little_endian( to_number( to_char( t_now, 'dd' ) )
                                                              + to_number( to_char( t_now, 'mm' ) ) * 32
                                                              + ( to_number( to_char( t_now, 'yyyy' ) ) - 1980 ) * 512
                                                              , 2
                                                              ) -- File last modification date
                                               , t_crc32 -- CRC-32
                                               , little_endian( t_clen )                   -- compressed size
                                               , little_endian( t_len )                    -- uncompressed size
                                               , little_endian( length( p_name ), 2 )      -- File name length
                                               , hextoraw( '0000' )                        -- Extra field length
                                               , utl_raw.cast_to_raw( p_name )             -- File name
                                               )
                               );
                if t_compressed
                then                   
                  dbms_lob.copy( p_zipped_blob, t_blob, t_clen, dbms_lob.getlength( p_zipped_blob ) + 1, 11 ); -- compressed content
                elsif t_clen > 0
                then                   
                  dbms_lob.copy( p_zipped_blob, t_blob, t_clen, dbms_lob.getlength( p_zipped_blob ) + 1, 1 ); -- compressed content
                end if;
                if dbms_lob.istemporary( t_blob ) = 1
                then      
                  dbms_lob.freetemporary( t_blob );
                end if;
              end;
            --
              procedure finish_zip( p_zipped_blob in out blob )
              is
                t_cnt pls_integer := 0;
                t_offs integer;
                t_offs_dir_header integer;
                t_offs_end_header integer;
                t_comment raw(32767) := utl_raw.cast_to_raw( 'Implementation by Anton Scheffer' );
              begin
                t_offs_dir_header := dbms_lob.getlength( p_zipped_blob );
                t_offs := 1;
                while dbms_lob.substr( p_zipped_blob, utl_raw.length( c_LOCAL_FILE_HEADER ), t_offs ) = c_LOCAL_FILE_HEADER
                loop
                  t_cnt := t_cnt + 1;
                  dbms_lob.append( p_zipped_blob
                                 , utl_raw.concat( hextoraw( '504B0102' )      -- Central directory file header signature
                                                 , hextoraw( '1400' )          -- version 2.0
                                                 , dbms_lob.substr( p_zipped_blob, 26, t_offs + 4 )
                                                 , hextoraw( '0000' )          -- File comment length
                                                 , hextoraw( '0000' )          -- Disk number where file starts
                                                 , hextoraw( '0000' )          -- Internal file attributes => 
                                                                               --     0000 binary file
                                                                               --     0100 (ascii)text file
                                                 , case
                                                     when dbms_lob.substr( p_zipped_blob
                                                                         , 1
                                                                         , t_offs + 30 + blob2num( p_zipped_blob, 2, t_offs + 26 ) - 1
                                                                         ) in ( hextoraw( '2F' ) -- /
                                                                              , hextoraw( '5C' ) -- \
                                                                              )
                                                     then hextoraw( '10000000' ) -- a directory/folder
                                                     else hextoraw( '2000B681' ) -- a file
                                                   end                         -- External file attributes
                                                 , little_endian( t_offs - 1 ) -- Relative offset of local file header
                                                 , dbms_lob.substr( p_zipped_blob
                                                                  , blob2num( p_zipped_blob, 2, t_offs + 26 )
                                                                  , t_offs + 30
                                                                  )            -- File name
                                                 )
                                 );
                  t_offs := t_offs + 30 + blob2num( p_zipped_blob, 4, t_offs + 18 )  -- compressed size
                                        + blob2num( p_zipped_blob, 2, t_offs + 26 )  -- File name length 
                                        + blob2num( p_zipped_blob, 2, t_offs + 28 ); -- Extra field length
                end loop;
                t_offs_end_header := dbms_lob.getlength( p_zipped_blob );
                dbms_lob.append( p_zipped_blob
                               , utl_raw.concat( c_END_OF_CENTAL_DIRECTORY                                 -- End of central directory signature
                                               , hextoraw( '0000' )                                        -- Number of this disk
                                               , hextoraw( '0000' )                                        -- Disk where central directory starts
                                               , little_endian( t_cnt, 2 )                                 -- Number of central directory records on this disk
                                               , little_endian( t_cnt, 2 )                                 -- Total number of central directory records
                                               , little_endian( t_offs_end_header - t_offs_dir_header )    -- Size of central directory
                                               , little_endian( t_offs_dir_header )                        -- Offset of start of central directory, relative to start of archive
                                               , little_endian( nvl( utl_raw.length( t_comment ), 0 ), 2 ) -- ZIP file comment length
                                               , t_comment
                                               )
                               );
              end;
            --
              procedure save_zip
                ( p_zipped_blob blob
                , p_dir varchar2 := 'MY_DIR'
                , p_filename varchar2 := 'my.zip'
                )
              is
                t_fh utl_file.file_type;
                t_len pls_integer := 32767;
              begin
                t_fh := utl_file.fopen( p_dir, p_filename, 'wb' );
                for i in 0 .. trunc( ( dbms_lob.getlength( p_zipped_blob ) - 1 ) / t_len )
                loop
                  utl_file.put_raw( t_fh, dbms_lob.substr( p_zipped_blob, t_len, i * t_len + 1 ) );
                end loop;
                utl_file.fclose( t_fh );
              end;
            --
            end;
            Anton
            • 18. Re: UTL_COMPRESS Zipping Multiple BLOBs
              Marwim
              Hello Anton,

              in your new version you use a function blob2num. Do you use it to replace
              utl_raw.cast_to_binary_integer(dbms_lob.substr(p_zipped_blob,
                        2,
                        g_offsets(i) + 26),
                        utl_raw.little_endian)
              Would you mind to update the code to include it.

              Regards
              Marcus
              • 19. Re: UTL_COMPRESS Zipping Multiple BLOBs
                ascheffer
                Sorry I missed that procedure. I was cutting that code from a much larger package I was working on (adding a password to the zip)
                But the following code should work:
                declare
                  g_zipped_blob blob;
                --
                  c_LOCAL_FILE_HEADER       constant raw(4) := hextoraw( '504B0304' ); -- Local file header signature
                  c_END_OF_CENTAL_DIRECTORY constant raw(4) := hextoraw( '504B0506' ); -- End of central directory signature
                --
                  function blob2num( p_blob blob, p_len integer, p_pos integer )
                  return number
                  is
                  begin
                    return utl_raw.cast_to_binary_integer( dbms_lob.substr( p_blob, p_len, p_pos ), utl_raw.little_endian );
                  end;
                --
                  function little_endian( p_big number, p_bytes pls_integer := 4 )
                  return raw
                  is
                  begin
                    return utl_raw.substr( utl_raw.cast_from_binary_integer( p_big, utl_raw.little_endian ), 1, p_bytes );
                  end;
                --
                  procedure add1file
                    ( p_zipped_blob in out blob
                    , p_name varchar2
                    , p_content blob
                    )
                  is
                    t_now date;
                    t_blob blob;
                    t_len integer;
                    t_clen integer;
                    t_crc32 raw(4) := hextoraw( '00000000' );
                    t_compressed boolean := false;
                  begin
                    t_now := sysdate;
                    t_len := nvl( dbms_lob.getlength( p_content ), 0 );
                    if t_len > 0
                    then 
                      t_blob := utl_compress.lz_compress( p_content );
                      t_clen := dbms_lob.getlength( t_blob ) - 18;
                      t_compressed := t_clen < t_len;
                      t_crc32 := dbms_lob.substr( t_blob, 4, t_clen + 11 );       
                    end if;
                    if not t_compressed
                    then 
                      t_clen := t_len;
                      t_blob := p_content;
                    end if;
                    if p_zipped_blob is null
                    then
                      dbms_lob.createtemporary( p_zipped_blob, true );
                    end if;
                    dbms_lob.append( p_zipped_blob
                                   , utl_raw.concat( c_LOCAL_FILE_HEADER -- Local file header signature
                                                   , hextoraw( '1400' )     -- version 2.0
                                                   , hextoraw( '0000' )     -- no General purpose bits
                                                   , case when t_compressed
                                                        then hextoraw( '0800' ) -- deflate
                                                        else hextoraw( '0000' ) -- stored
                                                     end
                                                   , little_endian( to_number( to_char( t_now, 'ss' ) ) / 2
                                                                  + to_number( to_char( t_now, 'mi' ) ) * 32
                                                                  + to_number( to_char( t_now, 'hh24' ) ) * 2048
                                                                  , 2
                                                                  ) -- File last modification time
                                                   , little_endian( to_number( to_char( t_now, 'dd' ) )
                                                                  + to_number( to_char( t_now, 'mm' ) ) * 32
                                                                  + ( to_number( to_char( t_now, 'yyyy' ) ) - 1980 ) * 512
                                                                  , 2
                                                                  ) -- File last modification date
                                                   , t_crc32 -- CRC-32
                                                   , little_endian( t_clen )                   -- compressed size
                                                   , little_endian( t_len )                    -- uncompressed size
                                                   , little_endian( length( p_name ), 2 )      -- File name length
                                                   , hextoraw( '0000' )                        -- Extra field length
                                                   , utl_raw.cast_to_raw( p_name )             -- File name
                                                   )
                                   );
                    if t_compressed
                    then                   
                      dbms_lob.copy( p_zipped_blob, t_blob, t_clen, dbms_lob.getlength( p_zipped_blob ) + 1, 11 ); -- compressed content
                    elsif t_clen > 0
                    then                   
                      dbms_lob.copy( p_zipped_blob, t_blob, t_clen, dbms_lob.getlength( p_zipped_blob ) + 1, 1 ); -- compressed content
                    end if;
                    if dbms_lob.istemporary( t_blob ) = 1
                    then      
                      dbms_lob.freetemporary( t_blob );
                    end if;
                  end;
                --
                  procedure finish_zip( p_zipped_blob in out blob )
                  is
                    t_cnt pls_integer := 0;
                    t_offs integer;
                    t_offs_dir_header integer;
                    t_offs_end_header integer;
                    t_comment raw(32767) := utl_raw.cast_to_raw( 'Implementation by Anton Scheffer' );
                  begin
                    t_offs_dir_header := dbms_lob.getlength( p_zipped_blob );
                    t_offs := 1;
                    while dbms_lob.substr( p_zipped_blob, utl_raw.length( c_LOCAL_FILE_HEADER ), t_offs ) = c_LOCAL_FILE_HEADER
                    loop
                      t_cnt := t_cnt + 1;
                      dbms_lob.append( p_zipped_blob
                                     , utl_raw.concat( hextoraw( '504B0102' )      -- Central directory file header signature
                                                     , hextoraw( '1400' )          -- version 2.0
                                                     , dbms_lob.substr( p_zipped_blob, 26, t_offs + 4 )
                                                     , hextoraw( '0000' )          -- File comment length
                                                     , hextoraw( '0000' )          -- Disk number where file starts
                                                     , hextoraw( '0000' )          -- Internal file attributes => 
                                                                                   --     0000 binary file
                                                                                   --     0100 (ascii)text file
                                                     , case
                                                         when dbms_lob.substr( p_zipped_blob
                                                                             , 1
                                                                             , t_offs + 30 + blob2num( p_zipped_blob, 2, t_offs + 26 ) - 1
                                                                             ) in ( hextoraw( '2F' ) -- / 
                                                                                  , hextoraw( '5C' ) -- \
                                                                                  )
                                                         then hextoraw( '10000000' ) -- a directory/folder
                                                         else hextoraw( '2000B681' ) -- a file
                                                       end                         -- External file attributes
                                                     , little_endian( t_offs - 1 ) -- Relative offset of local file header
                                                     , dbms_lob.substr( p_zipped_blob
                                                                      , blob2num( p_zipped_blob, 2, t_offs + 26 )
                                                                      , t_offs + 30
                                                                      )            -- File name
                                                     )
                                     );
                      t_offs := t_offs + 30 + blob2num( p_zipped_blob, 4, t_offs + 18 )  -- compressed size
                                            + blob2num( p_zipped_blob, 2, t_offs + 26 )  -- File name length 
                                            + blob2num( p_zipped_blob, 2, t_offs + 28 ); -- Extra field length
                    end loop;
                    t_offs_end_header := dbms_lob.getlength( p_zipped_blob );
                    dbms_lob.append( p_zipped_blob
                                   , utl_raw.concat( c_END_OF_CENTAL_DIRECTORY                                 -- End of central directory signature
                                                   , hextoraw( '0000' )                                        -- Number of this disk
                                                   , hextoraw( '0000' )                                        -- Disk where central directory starts
                                                   , little_endian( t_cnt, 2 )                                 -- Number of central directory records on this disk
                                                   , little_endian( t_cnt, 2 )                                 -- Total number of central directory records
                                                   , little_endian( t_offs_end_header - t_offs_dir_header )    -- Size of central directory
                                                   , little_endian( t_offs_dir_header )                        -- Offset of start of central directory, relative to start of archive
                                                   , little_endian( nvl( utl_raw.length( t_comment ), 0 ), 2 ) -- ZIP file comment length
                                                   , t_comment
                                                   )
                                   );
                  end;
                --
                  procedure save_zip
                    ( p_zipped_blob blob
                    , p_dir varchar2 := 'MY_DIR'
                    , p_filename varchar2 := 'my.zip'
                    )
                  is
                    t_fh utl_file.file_type;
                    t_len pls_integer := 32767;
                  begin
                    t_fh := utl_file.fopen( p_dir, p_filename, 'wb' );
                    for i in 0 .. trunc( ( dbms_lob.getlength( p_zipped_blob ) - 1 ) / t_len )
                    loop
                      utl_file.put_raw( t_fh, dbms_lob.substr( p_zipped_blob, t_len, i * t_len + 1 ) );
                    end loop;
                    utl_file.fclose( t_fh );
                  end;
                --
                begin
                  add1file( g_zipped_blob, 'test1.txt', utl_raw.cast_to_raw( 'A small text file' ) );
                  add1file( g_zipped_blob, 'dir1/test1234.txt', utl_raw.cast_to_raw( 'A small file in a folder' ) );
                  add1file( g_zipped_blob, 'dir2/', null ); -- an empty folder
                  add1file( g_zipped_blob, 'test2.txt', null ); -- an empty file
                  add1file( g_zipped_blob, 'dir3/', null ); -- an empty folder
                  add1file( g_zipped_blob, 'dir3/test1.txt', utl_raw.cast_to_raw( 'A small text file in a folder' ) );
                  finish_zip( g_zipped_blob );
                  save_zip( g_zipped_blob, 'MY_DIR', 'my.zip' );
                  dbms_lob.freetemporary( g_zipped_blob );
                end;
                Anton
                • 20. Re: UTL_COMPRESS Zipping Multiple BLOBs
                  Marwim
                  But the following code should work:
                  Thanks
                  1 2 Previous Next