Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Gaurang.

Asked: July 29, 2020 - 4:38 pm UTC

Last updated: August 04, 2020 - 6:46 am UTC

Version: 11.2.03

Viewed 100+ times

You Asked

Hello - We're doing following in oracle db

- Extract comma delimited file from Siebel database using SQL query
- we're using BLOB to store comma delimited file
- Compressing BLOB data using UTL_compress LN_COMPRESS
- Sending data to REST API using HTTP post

REST service is erroring out due to Oracle compression and can't read oracle compressed data. Btw, its fine when I zip file manually using winzip and post using POSTMAN.

REST API owner would like to know Oracle UTL_compress COMPRESS specification. Also there are asking to send OS file (oracle compressed file)

Need to know following...

1)what specification Oracle is using for UTL_COMPRESS
2) How can I create file using Oracle COMRESSED blob?

Thanks in advance

and we said...

UTL_COMPRESS uses the LZ compression (equivalent to gzip).

You take a source blob and get a resultant compressed on, eg

    l_log_blob blob;
    l_uncompressed_blob    blob;
        l_uncompressed_blob := ...


        utl_compress.lz_compress (src => l_uncompressed_blob,
                                  dst => l_log_blob);



and then any blob can be written to file via UTL_FILE

  l_gzip      utl_file.file_type;
  l_piece     raw(32767);
  l_chunklen  number := 32767;
  l_idx       integer := 1;
  l_blob_len  integer;

  l_blob_len := dbms_lob.getlength(l_log_blob);
  l_gzip := utl_file.fopen('blob_dir','myfile.dat.gz','wb', 32767);

  while l_idx <= l_blob_len loop, l_chunklen, l_idx, l_piece);
    utl_file.put_raw(l_gzip, l_piece, true);
    l_idx := l_idx + l_chunklen;
  end loop;


  (2 ratings)


Gaurang, July 31, 2020 - 11:33 am UTC

Thanks Connor
Connor McDonald
August 04, 2020 - 6:46 am UTC

glad we could help

CSV in a (compressed) BLOB?

NextName, August 03, 2020 - 4:06 pm UTC

Why would anyone save structured CSV date in a BLOB in the first place?
Chris Saxon
August 03, 2020 - 4:50 pm UTC

Good question

More to Explore


Check out more PL/SQL tutorials on our LiveSQL tool.