This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 30, 2012 1:31 AM by Marwim RSS

UTL_COMPRESS Zipping Multiple BLOBs

780189 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Big thanks to Anton. This is a great package and has proved very helpful and useful.
  • 13. Re: UTL_COMPRESS Zipping Multiple BLOBs
    893117 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points