Hi
My DB version is Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.
I have been using PLSQL code to read data from DB using any query, write it into a CLOB (this part in below sample code is skipped using a fixed CLOB Value just to simply the example code ) and generate on the fly csv file and then compress it and send it as an email attachment.
Everything works fine till I uses the default compression format provided by, utl_compress.lz_compress ( gzip) and I keep the attached file name as <file_name>.gz
This file can be opened using 7z. However I want file to be a zip file ( file_name.zip) which can be directly opened by Windows default Utility to extract file from zip.
I read some article and tried to use the code between these two lines in the complete code given below. However it does not help and the zip file I receive in email cant be opened. 
------------------- Start Converting Compressed Blob into zlib Format ----------------
------------------- Finish Converting Compressed Blob into zlib Format ----------------
I am not writing data to server file, its runtime generation of of CLOB --> BLOB --> Compress BLOB --> Attachment --> Email
I have seen few articles having mention of Java zip utility but seems it requires creating a physical file on server, or some shell script which again similar to java can zip a physical file on server.
However I am just wondering if there is a way to get the compress file in zip format (Windows default zip readable format) within PLSQL itself without creating any physical file.
DECLARE
lv_clob_data_from_table CLOB := 'id, name, desc'||CHR(10)
||'1,ABCD\_NAME,ABCD\_NAME\_DESC'||CHR(10)
||'2,EFGH\_NAME,EFGH\_NAME\_DESC'||CHR(10)
||'3,HIJK\_NAME,HIJK\_NAME\_DESC'
;
--Convert Clob into Blob Related Variables
lv_blob BLOB := TO_BLOB('0');
lv_in PLS_INTEGER := 1;
lv_out PLS_INTEGER := 1;
lv_lang PLS_INTEGER := 0;
lv_warning PLS_INTEGER := 0;
--Compress Blob Related Variables.
lv_compressed_blob_temp BLOB := TO_BLOB('0');
lv_compressed_blob_final BLOB := TO_BLOB('0');
--zlib format related variables.
s1 PLS_INTEGER := 1;
s2 PLS_INTEGER := 0;
lv_ret VARCHAR2(10000);
--Attached and Email Blob Related Variables
lv_email_from VARCHAR2(500) := 'test_zip@abcd.com';
lv_email_ids VARCHAR2(10000) := 'name@companyname.com';
lv_to_list_temp VARCHAR2(1000) := lv_email_ids ;
lv_rcpt VARCHAR2(100);
lv_subject VARCHAR2(500) := 'Test Convert Clob to Blob, Compress and Email Zip File';
lv_message VARCHAR2(2000) := 'Check the Attached Zip File';
lv_file_name VARCHAR2(100) := 'Test_File.csv.zip';
lv_buffer_size PLS_INTEGER := 54;
lv_buffer_next BINARY_INTEGER := 54;
lv_body_buffer RAW(54);
lv_connection_handle UTL_SMTP.CONNECTION;
lv_smtp_host VARCHAR2(30) := 'localhost';
BEGIN
DBMS_OUTPUT.PUT_LINE (lv_clob_data_from_table);
--Convert Clob into Blob
DBMS_LOB.convertToBlob( lv_blob
,lv\_clob\_data\_from\_table
,DBMS\_LOB.getlength(lv\_clob\_data\_from\_table)
,lv\_in
,lv\_out
,DBMS\_LOB.default\_csid
,lv\_lang
,lv\_warning
);
DBMS_OUTPUT.PUT_LINE('Length of Input Clob = '||LENGTH(lv_clob_data_from_table));
DBMS_OUTPUT.PUT_LINE('Length of Blob = '||LENGTH(lv_blob));
-- Compress the Blob in Default Format
lv_compressed_blob_temp := utl_compress.lz_compress( lv_blob );
DBMS_OUTPUT.PUT_LINE('Length of Compressed Temp Blob = '||LENGTH(lv_compressed_blob_final));
lv_compressed_blob_final := lv_compressed_blob_temp;
------------------- Start Converting Compressed Blob into zlib Format ----------------
lv_compressed_blob_final := hextoraw( '789C' ); -- zlib header
dbms_lob.copy( lv_compressed_blob_final, lv_compressed_blob_temp, dbms_lob.getlength( lv_compressed_blob_temp ) - 10 - 8, 3, 11 );
FOR i in 1 .. DBMS_LOB.GETLENGTH( lv_blob )
LOOP
s1 := mod( s1 + to\_number( rawtohex( dbms\_lob.substr( lv\_blob, 1, i ) ), 'XX' ), 65521 );
s2 := mod( s2 + s1, 65521);
END LOOP;
lv_ret := to_char( s2, 'fm0XXX' ) || to_char( s1, 'fm0XXX' );
DBMS_OUTPUT.PUT_LINE (lv_ret);
dbms_lob.append( lv_compressed_blob_final, hextoraw( lv_ret ) ); -- zlib trailer
dbms_lob.freetemporary( lv_compressed_blob_temp );
DBMS_OUTPUT.PUT_LINE('Length of Compressed Final Blob = '||LENGTH(lv_compressed_blob_final));
------------------- Finish Converting Compressed Blob into zlib Format ----------------
/*UTL_SMTP related coding. */
lv_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => lv_smtp_host);
UTL_SMTP.HELO(lv_connection_handle, lv_smtp_host);
UTL_SMTP.MAIL(lv_connection_handle, lv_email_from);
FOR i IN 1 .. (LENGTH(lv_email_ids) - LENGTH(REPLACE(lv_email_ids,';','')) + 1)
LOOP
lv\_rcpt := SUBSTR(lv\_to\_list\_temp,INSTR(lv\_to\_list\_temp,';',-1,1) + 1);
lv\_to\_list\_temp := SUBSTR(lv\_to\_list\_temp,1,INSTR(lv\_to\_list\_temp,';',-1,1) - 1);
UTL\_SMTP.RCPT(lv\_connection\_handle,lv\_rcpt );
END LOOP;
UTL_SMTP.OPEN_DATA(lv_connection_handle);
UTL_SMTP.WRITE_DATA(lv_connection_handle, 'From' || ': ' || lv_email_from || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, 'To' || ': ' || lv_email_ids || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, 'Subject' || ': ' || lv_subject || UTL_TCP.CRLF);
--MIME header.
UTL_SMTP.WRITE_DATA(lv_connection_handle,'MIME-Version: 1.0' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle,'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, ' boundary= "' || 'SECBOUND' || '"' ||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, UTL_TCP.CRLF);
-- Mail Body
UTL_SMTP.WRITE_DATA(lv_connection_handle,'--' || 'SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle,'Content-Type: text/plain;' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle,' charset=US-ASCII' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, lv_message || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, UTL_TCP.CRLF);
-- Mail Attachment
UTL_SMTP.WRITE_DATA(lv_connection_handle,'--' || 'SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle,'Content-Type: application/octet-stream' ||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle,'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle,' filename="' || lv_file_name || '"' ||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle,'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, UTL_TCP.CRLF);
----- Write BLOB data into attachment
FOR i IN 0 .. CEIL(DBMS_LOB.GETLENGTH(lv_compressed_blob_final) / lv_buffer_size) - 1
LOOP
DBMS\_LOB.READ( lv\_compressed\_blob\_final
,lv\_buffer\_next
,i \* lv\_buffer\_size + 1
,lv\_body\_buffer
);
UTL\_SMTP.write\_raw\_data(lv\_connection\_handle, UTL\_ENCODE.BASE64\_ENCODE(lv\_body\_buffer));
UTL\_SMTP.WRITE\_DATA(lv\_connection\_handle, UTL\_TCP.CRLF);
END LOOP;
UTL_SMTP.WRITE_DATA(lv_connection_handle, UTL_TCP.CRLF);
-- Close Email
UTL_SMTP.WRITE_DATA(lv_connection_handle,'--' || 'SECBOUND' || '--' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(lv_connection_handle, UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
UTL_SMTP.CLOSE_DATA(lv_connection_handle);
UTL_SMTP.QUIT(lv_connection_handle);
END;
/
And below is the output of his code. The file too is delivered but can't be read
id, name,desc
1,ABCD_NAME,ABCD_NAME_DESC
2,EFGH_NAME,EFGH_NAME_DESC
3,HIJK_NAME,HIJK_NAME_DESC
Length of Input Clob = 95
Length of Blob = 95
Length of Compressed Temp Blob = 1
FEFF1A70
Length of Compressed Final Blob = 64
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.73
Regards
Arun