Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gaurang.

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

Answered by: Connor McDonald - Last updated: August 04, 2020 - 6:46 am UTC

Category: PL/SQL - 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

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;



and you rated our response

  (2 ratings)

Reviews

July 31, 2020 - 11:33 am UTC

Reviewer: Gaurang

Thanks Connor
Connor McDonald

Followup  

August 04, 2020 - 6:46 am UTC

glad we could help

CSV in a (compressed) BLOB?

August 03, 2020 - 4:06 pm UTC

Reviewer: NextName from good old Europe

Why would anyone save structured CSV date in a BLOB in the first place?
Chris Saxon

Followup  

August 03, 2020 - 4:50 pm UTC

Good question

More to Explore

PL/SQL

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