如何使用UTL_COMPRESS包压缩txt文件并在带有oracle 11 g的unix中创建gz文件? [英] How to use UTL_COMPRESS package to compress the txt file and create gz file in unix with oracle 11 g?

问题描述

我有以下要求: 实际上我有txt文件，我需要压缩该文件并使用oracle UTL_COMPRESS包创建gz文件. 我需要在带有Oracle 11g的Unix机器上实现此功能. 我已经用下面的代码尝试过了，并且在某种程度上可以正常工作.我的意思是它正在压缩小型文件.

I have following requirement : Actually I have txt file I need to compress this file and create gz file using oracle UTL_COMPRESS package. I need to implement this functionality on Unix box with Oracle 11g. I have tried it with below code and it is working for some extent.I mean it is working to compress the small size 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;

但是此代码无法压缩大文件. 如果有人在oracle 11g中实现此功能，请提供帮助. 将不胜感激. 错误消息:

But this code is not working to compress the large file. Please help if some implement this functionality in oracle 11g. Would be much appreciate. 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"

推荐答案

我能够解决此问题.我已经修改了代码，下面的代码也可以很好地压缩大文件.

I am able to resolve the problem. I have modified the code and below code is working fine for compress the large size file as well.

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;

这篇关于如何使用UTL_COMPRESS包压缩txt文件并在带有oracle 11 g的unix中创建gz文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！