Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Select Data from a Query and Send output in Windows Zip Format as Attachment

Arun Kumar GuptaAug 4 2020 — edited Aug 5 2020

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. Zip_File_Format_Error.JPG

------------------- 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

Comments
Post Details
Added on Aug 4 2020
4 comments
1,143 views