1 2 Previous Next 20 Replies Latest reply: Jan 30, 2012 3:31 AM by Marwim RSS

    UTL_COMPRESS Zipping Multiple BLOBs

    780189
      Hey everyone, I'm having trouble figuring out how to use UTL_COMPRESS.

      What I want to do is take several BLOBs from one table, and put them into one zip file. Can anyone show me an example of where this is done?

      Here is some sample code of what I have so far:

      My table:
      CREATE TABLE  "FILES" 
         (     "FILE_ID" NUMBER, 
           "FILE_TYPE" NUMBER, 
           "FILENAME" VARCHAR2(1000), 
           "DATE_ADDED" DATE, 
           "ADDED_BY" VARCHAR2(255), 
           "FILE_BLOB" BLOB, 
         )
      /
      Some sample code:
      DECLARE
      
        b_dl_file1 BLOB;
        b_dl_file2 BLOB;
        b_dl_file3 BLOB;
      
        b_compressed_file BLOB;
      
      BEGIN
      
      select FILE_BLOB
      into b_dl_file1
      from FILES
      where FILE_ID = 64;
      
      select FILE_BLOB
      into b_dl_file2
      from FILES
      where FILE_ID = 65;
      
      select FILE_BLOB
      into b_dl_file3
      from FILES
      where FILE_ID = 66;
      
      b_compressed_file := UTL_COMPRESS.LZ_COMPRESS(b_dl_file1);
      END;
      So what I've posted works fine, it compresses a single file. What I want is to take all three of those blobs (for example) and add them to one zip file. I've looked into using UTL_COMPRESS.LZ_COMPRESS_ADD, but as far as I can see, that only takes RAW data. Will I have to break my BLOBs into RAW strings and then compress those? Will that even work?

      Any help would be appreciated!
        • 1. Re: UTL_COMPRESS Zipping Multiple BLOBs
          Marwim
          Hello Username,

          with UTLCOMPRESS you can only zip one file. If you need more than one you can use JAVA http://www.oracle.com/global/de/community/tipps/zip/index.html.

          Regards
          Marcus

          Edited by: Marwim on 17.08.2010 15:27
          • 2. Re: UTL_COMPRESS Zipping Multiple BLOBs
            BluShadow
            Username wrote:
            Hey everyone, I'm having trouble figuring out how to use UTL_COMPRESS.

            What I want to do is take several BLOBs from one table, and put them into one zip file. Can anyone show me an example of where this is done?

            { snip }

            So what I've posted works fine, it compresses a single file. What I want is to take all three of those blobs (for example) and add them to one zip file. I've looked into using UTL_COMPRESS.LZ_COMPRESS_ADD, but as far as I can see, that only takes RAW data. Will I have to break my BLOBs into RAW strings and then compress those? Will that even work?
            There's a world of difference between LZ compessed data and a Zip file. Whilst ZIP files contain LZ compression based data they actually tend to use LZW and have additional information stored in the file for storing the file table and the relative starting positions within the Zip. It's not just a case of taking LZ compressed data and joining it together unfortunately. So, to achieve what you want you'll need some external or 3rd party means of doing it, there's nothing built into Oracle as standard.
            • 3. Re: UTL_COMPRESS Zipping Multiple BLOBs
              ascheffer
              You can try this
              declare
                g_zipped_blob blob;
                b_dl_file1 BLOB;
                b_dl_file2 BLOB;
                b_dl_file3 BLOB;
              -
                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
                  )
                is
                  t_now date;
                  t_blob blob;
                  t_clen integer;
                begin
                  t_now := sysdate;
                  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;
                  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.freetemporary( t_blob );
                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 := dbms_lob.instr( p_zipped_blob, hextoraw( '504B0304' ), 1 );
                  while t_offs > 0
                  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( '0100' )          -- Internal file attributes
                                                   , hextoraw( '2000B681' )      -- External file attributes
                                                   , little_endian( t_offs - 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, t_offs + 26 ), utl_raw.little_endian )
                                                                    , t_offs + 30
                                                                    )            -- File name
                                                   )
                                   );
                    t_offs := dbms_lob.instr( p_zipped_blob, hextoraw( '504B0304' ), t_offs + 32 );
                  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
                                                 )
                                 );
                end;
              --
                procedure save_zip
                  ( p_zipped_blob in blob
                  , p_dir in varchar2 := 'MY_DIR'
                  , p_filename in 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
              select FILE_BLOB
              into b_dl_file1
              from FILES
              where FILE_ID = 64;
               
              select FILE_BLOB
              into b_dl_file2
              from FILES
              where FILE_ID = 65;
               
              select FILE_BLOB
              into b_dl_file3
              from FILES
              where FILE_ID = 66;
                add1file( g_zipped_blob, 'test1.bin', b_dl_file1 );
                add1file( g_zipped_blob, 'test2.bin', b_dl_file2 );
                add1file( g_zipped_blob, 'test2.bin', b_dl_file3 );
                finish_zip( g_zipped_blob );
                save_zip( g_zipped_blob, 'MY_DIR', 'my.zip' );
              end;
              Anton
              • 4. Re: UTL_COMPRESS Zipping Multiple BLOBs
                MichaelS
                Hats off, Anton! One of your superb great posts again ;)

                That 's the continuation for one of the elder posts about Re: Create Zip File:

                »
                But maybe, if one elaborates more on this, one can modify the bytestream to add some zip headers to make the file zip-compatible
                «

                you just did it, bravo!
                • 6. Re: UTL_COMPRESS Zipping Multiple BLOBs
                  780189
                  Wow! Thanks! That's exactly what I'm looking for. I'm having some trouble getting this code to run though, I'm recieving the following error:

                  "ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 "

                  I've been debugging and it looks like its coming from the add1file procedure.

                  I modified the code a bit to make it into a package:
                  CREATE OR REPLACE PACKAGE zip_blobs AS
                    /*Package written by Anton Scheffer, used with permission*/
                    PROCEDURE add1file(p_zipped_blob in out blob, p_name in varchar2, p_content in blob);
                    PROCEDURE finish_zip(p_zipped_blob in out blob);
                    PROCEDURE save_zip(p_zipped_blob in blob, p_dir in varchar2, p_filename in varchar2);
                  
                  END zip_blobs;
                  CREATE OR REPLACE PACKAGE BODY zip_blobs AS
                  --
                    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
                      )
                    is
                      t_now date;
                      t_blob blob;
                      t_clen integer;
                    begin
                      t_now := sysdate;
                      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;
                      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.freetemporary( t_blob );
                    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 := dbms_lob.instr( p_zipped_blob, hextoraw( '504B0304' ), 1 );
                      while t_offs > 0
                      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( '0100' )          -- Internal file attributes
                                                       , hextoraw( '2000B681' )      -- External file attributes
                                                       , little_endian( t_offs - 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, t_offs + 26 ), utl_raw.little_endian )
                                                                        , t_offs + 30
                                                                        )            -- File name
                                                       )
                                       );
                        t_offs := dbms_lob.instr( p_zipped_blob, hextoraw( '504B0304' ), t_offs + 32 );
                      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
                                                     )
                                     );
                    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;
                  My code:
                  DECLARE
                    b_dl_file1 BLOB;
                    b_dl_file2 BLOB;
                    b_dl_file3 BLOB;
                    b_zipped_blob BLOB;
                  
                  BEGIN
                    select FILE_BLOB
                    into b_dl_file1
                    from FILES
                    where FILE_ID = 64;
                   
                    select FILE_BLOB
                    into b_dl_file2
                    from FILES
                    where FILE_ID = 65;
                   
                    select FILE_BLOB
                    into b_dl_file3
                    from FILES
                    where FILE_ID = 66;
                  
                    zip_blobs.add1file(b_zipped_blob, 'test1.bin', b_dl_file1);
                    zip_blobs.add1file(b_zipped_blob, 'test2.bin', b_dl_file2);
                    zip_blobs.add1file(b_zipped_blob, 'test3.bin', b_dl_file3);
                    zip_blobs.finish_zip(b_zipped_blob);
                  
                    insert into compressed_files(compressed_blob) values(b_zipped_blob);
                  
                  END;
                  So I'm inserting it into a table, but even when I use save_zip, I get the same error. I tried playing around with freeing p_zipped_blob and not freeing t_blob, but it didn't seem to work, and I'm not seeing where else it could be going wrong. Any more help would be awesome!

                  Thanks!
                  • 7. Re: UTL_COMPRESS Zipping Multiple BLOBs
                    ascheffer
                    It works for me :).
                    You can always try
                          dbms_lob.createtemporary( b_zipped_blob, true );
                    before the first call to add1file
                    • 8. Re: UTL_COMPRESS Zipping Multiple BLOBs
                      ascheffer
                      It's just some code I had lying around. See http://technology.amis.nl/blog/8090/parsing-a-microsoft-word-docx-and-unzip-zipfiles-with-plsql for some unzipping code

                      Anton
                      • 9. Re: UTL_COMPRESS Zipping Multiple BLOBs
                        780189
                        Thanks again Anton, I got it working. It looks like certain file sizes break the add1files procedure, I'm not sure why, but I have more than enough information to debug it. This was a HUGE help!
                        • 10. Re: UTL_COMPRESS Zipping Multiple BLOBs
                          ascheffer
                          I can save you some time: I just found out that dbms_lob.substr returns a raw, not a blob. And that has a max size of 32767 bytes. So the procedure failed for the large (compressed) blobs.

                          A simple solution change the line
                          -   dbms_lob.append( p_zipped_blob, dbms_lob.substr( t_blob, t_clen - 18, 11 ) );     -- compressed content
                          to
                              dbms_lob.copy( p_zipped_blob, t_blob, t_clen - 18, dbms_lob.getlength( p_zipped_blob ) + 1, 11 ); -- compressed content
                          So I learn something new about PL/SQL every day :)
                          • 11. Re: UTL_COMPRESS Zipping Multiple BLOBs
                            780189
                            Thanks! I had come to the same conclusion, and was writing a loop that would break the blob into 32K chunks, and append them one by one, but that line worked perfectly. Lucky I checked back here!
                            • 12. Re: UTL_COMPRESS Zipping Multiple BLOBs
                              ant7
                              Big thanks to Anton. This is a great package and has proved very helpful and useful.
                              • 13. Re: UTL_COMPRESS Zipping Multiple BLOBs
                                893117
                                Morning

                                I am running apex 4.1 on Oracle 11gR2 on a Red Hat Linux Server.

                                I hate to beat a dead horse, but I found your code for zipping multiple files and I am tring to use it but I am having problems with the finish_zip procedure. I never get out of the loop.

                                I see where you are setting the t_offs as the location of ''504B03040' to start, but I am not sure about what is happening at the end of the loop, I am not sure if I would ever get a 0 or less at the end.

                                I put a output line for testing and I alwas get the compressed file size plus one which is the t_offs from the first test.

                                Merph
                                • 14. Re: UTL_COMPRESS Zipping Multiple BLOBs
                                  ascheffer
                                  Evening,

                                  thats strange. My first guess would be that you have been modifying the code, my second quess would be that you are calling finish_zip without calling add1file before, and my last guess would be that you are calling add1file with empty blobs.
                                  But I don't know what you are doing, so it's just guessing.

                                  Anton
                                  1 2 Previous Next