How to use the UTL_COMPRESS package to compress a txt file and create a gz file in unix using oracle 11 g?

Question

I have the following requirement: Actually, I have a txt file, I need to compress this file and create a gz file using the oracle UTL_COMPRESS package. I need to implement this functionality in a Unix block with Oracle 11g. I tried it with the code below and it works to some extent. I mean, it works to compress a small file.

DECLARE f utl_file.file_type; compressed BLOB; data_b BFILE; BEGIN f := UTL_FILE.fopen ('DIR_UTL_COM_TEST', 'Test1.gz', 'wb'); data_b := BFILENAME ('DIR_UTL_COM_TEST','pk_intibuy_pkb.txt'); DBMS_LOB.FILEOPEN (data_b, DBMS_LOB.LOB_READONLY); DBMS_LOB.createtemporary (compressed, false); compressed := UTL_COMPRESS.lz_compress (data_b,6); UTL_FILE.put_raw(f, compressed, true); UTL_FILE.fclose (f); DBMS_LOB.FILECLOSE (data_b); DBMS_LOB.freetemporary (compressed); END; 

But this code does not work to compress a large file. Please help if some implement this functionality in oracle 11g. It would be very grateful. Error messages:

 Error report: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 11 06502. 00000 - "PL/SQL: numeric or value error%s" 

Answer 1

I can solve the problem. I changed the code and the code below works fine for compressing a large file size.

 DECLARE in_filename VARCHAR2(100); 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; BEGIN in_filename := 'Test.txt'; src_file := bfilename('DIR_UTL_COM_TEST', in_filename); dbms_lob.fileopen(src_file, dbms_lob.file_readonly); v_content := utl_compress.lz_compress(src_file, 9); v_blob_len := dbms_lob.getlength(v_content); v_file := utl_file.fopen('DIR_UTL_COM_TEST', in_filename || '.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); EXCEPTION WHEN OTHERS THEN IF utl_file.is_open(v_file) THEN utl_file.fclose(v_file); END IF; RAISE; END;