Have you thought about gzipping the file before you load it into the database? There are likely limitations with the method that you are using. The other option would be to get and pay for advanced compression, which would work much better that what you are doing. It is like expensive though. You might find that there is a bug in this method that effects 5-10% of the files. If you do find a bug 3-6 months after you implement this kind of solution, you will have corrupted data in your database. Which is why home grown solutions can help, but if you application is dependent on something, especially something like compression, you need to know that you got it right each and every time.

Rather than using blobs, you might try bfiles, where you have a process that compresses files that have not been used in awhile, and if a file is needed there is something that runs to un-compress the file. You should be able to create a java stored procedure that will run the relevant OS commands. Although, if you go the Java route, there is probably native compression that you can do from a Java App running in an Oracle database. You would then need to call that code to store and retrieve the blob.

See the blog itself, http://technology.amis.nl/2010/06/09/parsing-a-microsoft-word-docx-and-unzip-zipfiles-with-plsql/

Th package is at version 7. That might solve your problems. But note that the maximum file size, dictated by the used zip format, is 4 Gb, And that limit is both for a file to be zipped and for the resulting zip file itself.

The file size is 2,229,468,313 bytes which exceeds the binary_integer's range of -2,147,483,647 to 2,147,483,647 if that helps

That appears to be the reason for the problem. See the Packages and Types doc for UTL_RAW

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_raw.htm#i1004238

CAST_FROM_BINARY_INTEGER Function

This function returns the RAW binary representation of a BINARY_INTEGER value.

That function requires a binary integer but is being passed a value outside that range:

t_len := nvl( dbms_lob.getlength( p_content ), 0 );

. . .

little_endian( t_len )                       -- uncompressed size 

Anton will have to address that specifically but it appears to me that the max size for his code is not 4GB but 2GB.

If you change the function little_endian in the package you can zip files with a length between 2 and 4 GB

  function little_endian( p_big number, p_bytes pls_integer := 4 )

  return raw

  is

    t_big number := p_big;

  begin

    if t_big > 2147483647

    then

      t_big := t_big - 4294967296;

    end if;

    return utl_raw.substr( utl_raw.cast_from_binary_integer( t_big, utl_raw.little_endian ), 1, p_bytes );

  end;

And if you change blog2num you can read them again

  function blob2num( p_blob blob, p_len integer, p_pos integer )

  return number

  is

    rv number;

  begin

    rv := utl_raw.cast_to_binary_integer( dbms_lob.substr( p_blob, p_len, p_pos ), utl_raw.little_endian );

    if rv < 0

    then

      rv := rv + 4294967296;

    end if;

    return rv;

  end;

Zipping a file of that size will take some time, on my 11XE database 15 minutes.

By the way, I just saw that this code zip/unzip is included in the APEX 5.0 ea release, named wwv_flow_zip

By the way, I just saw that this code zip/unzip is included in the APEX 5.0 ea release, named wwv_flow_zip

Congratulations! Now you can retire and live on that royalty stream. Gee - we're gonna miss you.