PL/SQL: numeric or value error in UTL_COMPRESS

Question

This code works fine when compressing small files (4M), but I get numeric or value error when trying to compress large files (1.3G). Any idea what I am missing here? The piped line function ListDir is working correctly, so that is not the issue.

DECLARE
   dir_name VARCHAR2(50) ;
   dir_path VARCHAR2(1024);
   ns VARCHAR2(1024)       ;
   f    utl_file.file_type ;
   compressed    blob ;
   data_b        bfile ;
   compress_rate integer := 9 ;
BEGIN
   BEGIN
      select directory_name,directory_path
      into dir_name,dir_path
      from all_directories where directory_name = 'PURGE_ARCH_DIR' ;          
  EXCEPTION
      WHEN no_data_found
      THEN
          RAISE ;       
   END ;

   FOR each_file IN (SELECT column_value FROM TABLE(ListDir(dir_path)) ) 
   LOOP
      IF ( each_file.column_value = '07-22-2015_ccr_records_arc_purged.csv' )
      THEN
           DBMS_OUTPUT.PUT_LINE(each_file.column_value);
           f := UTL_FILE.fopen (dir_name, each_file.column_value || '.gz', 'wb');
           data_b := BFILENAME (dir_name,each_file.column_value);
           DBMS_LOB.FILEOPEN (data_b, DBMS_LOB.LOB_READONLY);
           DBMS_LOB.createtemporary (compressed, false);
           compressed := UTL_COMPRESS.lz_compress (data_b,compress_rate);
           UTL_FILE.put_raw(f, compressed, true);
           UTL_FILE.fclose (f);
           DBMS_LOB.FILECLOSE (data_b);
           DBMS_LOB.freetemporary (compressed);               
      END IF ;          
   END LOOP;       
EXCEPTION
     WHEN  others THEN
         RAISE ;
END;
/

The errors are:

07-22-2015_ccr_records_arc_purged.csv
ERROR at line 1:
ORA-20001: Error_Stack...
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 36

Missed this: ORA-06512: at line 36, which is: UTL_FILE.put_raw(f, compressed, true); — Stringer, Jul 23 '15 at 19:08

score 2 · Answer 1 · answered Jul 23 '15 at 21:01

I got this fixed. The problem is you can't compress a large file, in the case 1.3G, in a single pass. I had to loop though the Blob and write it out in chunks.

Here is the working code:

DECLARE
   dir_name VARCHAR2(1024) ;
   dir_path VARCHAR2(50)   ;
   ns VARCHAR2(1024)       ;
   v_process_name varchar2(61) := 'test_compress';
   src_file   BFILE;
   v_content  BLOB;
   v_blob_len INTEGER;
   v_file     utl_file.file_type;
   v_buffer   RAW(32767);
   v_amount   BINARY_INTEGER := 32767;
   v_pos      INTEGER := 1;
   v_compress_rate INTEGER := 9 ;
BEGIN
   BEGIN
      select directory_name,directory_path
      into dir_name,dir_path
      from all_directories where directory_name = 'PURGE_ARCH_DIR' ;

      EXCEPTION
         WHEN no_data_found
         THEN
              RAISE ;   
   END ;

   FOR each_file IN (SELECT column_value FROM TABLE(ListDir(dir_path)) ) 
   LOOP
      -- test large file (1.3G)
      IF ( each_file.column_value = '07-22-2015_imr_aces.aces_records_arc_purged.csv' )
      THEN
           DBMS_OUTPUT.PUT_LINE(each_file.column_value);

           src_file := bfilename(dir_name, each_file.column_value);
           dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
           v_content  := utl_compress.lz_compress(src_file, v_compress_rate);
           v_blob_len := dbms_lob.getlength(v_content);
           v_file     := utl_file.fopen(dir_name,
                            each_file.column_value || '.gz','wb');

           WHILE v_pos < v_blob_len 
           LOOP
                dbms_lob.READ(v_content, v_amount, v_pos, v_buffer);
                utl_file.put_raw(v_file, v_buffer, TRUE);
                v_pos := v_pos + v_amount;
           END LOOP;

           utl_file.fclose(v_file);

      END IF ;

   END LOOP;

   EXCEPTION
      WHEN  others THEN
         IF utl_file.is_open(v_file) 
         THEN
               utl_file.fclose(v_file);
         END IF;
         RAISE ;

END;
/

PL/SQL: numeric or value error in UTL_COMPRESS

1 Answers1