7 Replies Latest reply on Feb 4, 2014 12:54 AM by Sanjeev Chauhan

    Zip 2+ GB file in plsql. Error when using as_zip

    Sanjeev Chauhan

      I am using as_zip package by Anton Scheffer http://technology.amis.nl/wp-content/uploads/images/as_zip.txt

      When trying to zip a 2+ GB file I get the following error

       

      ORA-06502: PL/SQL: numeric or value error

      ORA-06512: at "SYS.UTL_RAW", line 380

      ORA-06512: at "SANJEEV.AS_ZIP", line 321

      ORA-06512: at "SANJEEV.AS_ZIP", line 356

      ORA-06512: at line 4

      06502. 00000 -  "PL/SQL: numeric or value error%s"

      *Cause:

      *Action:

       

      The error is being raised by the little_endian function.

       

      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;

       

      The function uses utl_raw.cast_from_binary_integer. I tried using utl_raw.cast_from_number but did not create the correct zip file. Has anyone found a fix for this error? The package works great for smaller files--a big thanks to Anton.

        • 1. Re: Zip 2+ GB file in plsql. Error when using as_zip
          gandolf999

          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.

          • 2. Re: Zip 2+ GB file in plsql. Error when using as_zip
            ascheffer

            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.

            • 3. Re: Zip 2+ GB file in plsql. Error when using as_zip
              Sanjeev Chauhan

              I tried version 7 of as_zip from the blog link above. The error is different now.

               

              declare

                g_zipped_blob blob;

              begin

                as_zip.add1file( g_zipped_blob, 'my_big_file.txt', as_zip.file2blob('MY_DIR','my_big_file.txt') );

                as_zip.finish_zip( g_zipped_blob );

                as_zip.save_zip( g_zipped_blob, 'MY_DIR', 'my.zip' );

              end;

              Error report -

              ORA-01426: numeric overflow

              ORA-06512: at "SANJEEV.AS_ZIP", line 27

              ORA-06512: at "SANJEEV.AS_ZIP", line 276

              ORA-06512: at line 4

              01426. 00000 -  "numeric overflow"

              *Cause:    Evaluation of an value expression causes an overflow/underflow.

              *Action:   Reduce the operands.

               

              The error still point to the return from little_endian function

               

                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;

               

              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

              • 4. Re: Zip 2+ GB file in plsql. Error when using as_zip
                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.

                • 5. Re: Zip 2+ GB file in plsql. Error when using as_zip
                  ascheffer

                  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

                  • 6. Re: Zip 2+ GB file in plsql. Error when using as_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.

                    • 7. Re: Zip 2+ GB file in plsql. Error when using as_zip
                      Sanjeev Chauhan

                      That fixed the problem. Thanks a lot. Now I hope we don't reach the 4GB limit any time soon.