Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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.