Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

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 1000+ 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 Connor said...

UTL_COMPRESS uses the LZ compression (equivalent to gzip).

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

declare
    l_log_blob blob;
    l_uncompressed_blob    blob;
begin
        l_uncompressed_blob := ...

        dbms_lob.createtemporary(l_log_blob,true);

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


        dbms_lob.freetemporary(l_log_blob);
        dbms_lob.freetemporary(l_uncompressed_blob);

end;
/


and then any blob can be written to file via UTL_FILE

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

  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
    dbms_lob.read(l_log_blob, l_chunklen, l_idx, l_piece);
    utl_file.put_raw(l_gzip, l_piece, true);
    l_idx := l_idx + l_chunklen;
  end loop;
  
  utl_file.fclose(l_gzip);
  
end;



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library