10 Replies Latest reply: Jul 23, 2013 12:10 AM by Raunaq RSS

    How to ZIP file and send via SMTP in Oracle

    HimaOne

      Dear All,

       

      I want to send data every month via email where the data i got from view.

      The problem is the file is to big, so i should zip it.

      the question is How i can perform it with procedure and send it automatically via Job every 1st month

       

      what i've done was i create a procedure to make the file in zip

      [quote/]

      CREATE OR REPLACE PROCEDURE production.CREATE_EXCEL_DTKPITerminate IS

          vvrun varchar2(3000);

          vsender varchar2(100);

          vrecepient varchar2(100);

        vccrecipient varchar2(1000);

          vsubject varchar2(1000);

          vmessage long;

          v_loc varchar2(5);

      /******************************************************************************

         NAME:       CREATE_EXCEL

         PURPOSE:

       

       

         REVISIONS:

         Ver        Date        Author           Description

         ---------  ----------  ---------------  ------------------------------------

         1.0        10/15/2012          1. Created this procedure.

       

       

         NOTES:

       

       

         Automatically available Auto Replace Keywords:

            Object Name:     CREATE_EXCEL

            Sysdate:         10/15/2012

            Date and Time:   10/15/2012, 9:42:40 , and 10/15/2012 9:42:40

            Username:         (set in TOAD Options, Procedure Editor)

            Table Name:       (set in the "New PL/SQL Object" dialog)

       

       

      ******************************************************************************/

      begin

         vsender := 'asdada@test.com';

           vrecepient := 'xxx@test.com';

        vccrecipient := 'yyy@test.com';

           vsubject := 'KPI Terminate'||TO_CHAR(SYSDATE,'MM-YYYY');

           vmessage :=

              'MESSAGE .';

       

       

           as_xlsx.query2sheet('

           select cmp_company,emp#,name,class,goucode,goudesc,job,job_name,tglkeluar

                  ,nac_seq,nac_code,nac_type,nac_begin,nac_desc,reason,reason_code

                  from V_KPITerminate

           ');

       

       

           --insert into blobs(blob_id,blob_name)

           --values (1,as_xlsx.finish);

           SEND_SMTP_PUZZLE_DTKRY(vsender,vrecepient,vccrecipient,vsubject,vmessage,as_xlsx.finish,'DataKPITerm -'||to_char(sysdate,'yyyy')||'.zip');

        --as_xlsx.save( 'BASE_DIR3', 'SWT.xls' );

      end;

      /

      [/quote]

       

      when i execute this, Error ocured

      Message       : ORA-29278: SMTP transient error: 421 Service not available

      ORA-06512           : at "SYS.UTL_SMTP", line 21

      ORA-06512           : at "SYS.UTL_SMTP", line 97

      ORA-06512           : at "SYS.UTL_SMTP", line 399

      ORA-06512           : at "PU22PROD_123.SEND_SMTP_PUZZLE_DTKRY", line 151

      ORA-29294           : A data error occurred during compression or uncompression.

      ORA-06512           : at "PU22PROD_123.CREATE_EXCEL_KPITERM", line 60

      ORA-06512           : at line 2

       

      cann anyone help?

       

       

      the data is too big so i prefer it zip.. can anyone help..

      the SMTP I use is like this

      CREATE OR REPLACE PROCEDURE production.SEND_SMTP_PUZZLE_DTKRY (pSender VARCHAR2,pRecipient VARCHAR2, pCCRecipient VARCHAR2, pSubject VARCHAR2,pMessage LONG,pattach BLOB,pfilename VARCHAR2) IS

        v_src_loc  BFILE := BFILENAME('BASE_DIR3', 'pajak.xls');

            l_buffer   RAW(54);

            l_amount   BINARY_INTEGER := 54;

           l_pos      INTEGER := 1;

           l_blob     BLOB := EMPTY_BLOB;

           l_blob_len INTEGER;

            v_amount   INTEGER;

            crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);

       

       

        v_connection_handle  UTL_SMTP.CONNECTION;

          v_smtp_host          VARCHAR2(30) := 'mail.mayora.co.id'; --My mail server, replace it with yours.

          v_subject            VARCHAR2(30) := 'Your Test Mail';

          l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';

        pcc varchar2(50);

       

       

        i number := 1;

        j number := 1;

       

       

        l_original_blob blob;

        l_compressed_blob blob;

      BEGIN

       

       

         BEGIN

       

           /*Preparing the LOB from file for attachment. */

           --DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file

           --dBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.

           --v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.

           --DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB

           --l_blob_len := DBMS_LOB.getlength(l_blob);

        l_original_blob     := pattach;

           l_compressed_blob   := TO_BLOB('1');

        UTL_COMPRESS.lz_compress (src => l_original_blob,

                                     dst => l_compressed_blob);

        --DBMS_LOB.FREETEMPORARY(l_compressed_blob);

        l_blob := l_compressed_blob;

           l_blob_len := DBMS_LOB.getlength(l_blob);

           /*UTL_SMTP related coding. */

           v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);

           UTL_SMTP.HELO(v_connection_handle, v_smtp_host);

           UTL_SMTP.MAIL(v_connection_handle, psender);

           UTL_SMTP.RCPT(v_connection_handle, precipient);

          if pCCRecipient is not null then

              if(instr(pCCRecipient,',') = 0) then

              utl_smtp.rcpt(v_connection_handle, pCCRecipient);

              else

             while(instr(pCCRecipient,',',i) > 0)

              loop

              pcc := substr(pCCRecipient,i, instr(substr(pCCRecipient,i),',')-1);

              i := i+instr(substr(pCCRecipient,i),',');

              utl_smtp.rcpt(v_connection_handle,pcc);

              end loop;

       

       

              pcc := substr(pCCRecipient,i,length(pCCRecipient));

              utl_smtp.rcpt(v_connection_handle,pcc);

              end if;

          end if;

       

       

           --UTL_SMTP.RCPT(v_connection_handle, v_cc_email_address);

           UTL_SMTP.OPEN_DATA(v_connection_handle);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                                 'FROM' || ': ' ||  psender || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                                 'TO' || ': ' ||  precipient || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                                 'CC' || ': ' ||  pCCRecipient || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                                 'SUBJECT' || ': ' ||  pSubject || UTL_TCP.CRLF);

         --MIME header.

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               'MIME-Version: 1.0' || UTL_TCP.CRLF);

          UTL_SMTP.WRITE_DATA(v_connection_handle,

                               'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               ' boundary= "' || 'BASE_DIR3.SECBOUND' || '"' ||

                               UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

       

       

       

       

           -- Mail Body

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               '--' || 'BASE_DIR3.SECBOUND' || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               'Content-Type: text/plain;' || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               ' charset=US-ASCII' || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle, Pmessage || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

       

       

       

       

           -- Mail Attachment

         UTL_SMTP.WRITE_DATA(v_connection_handle,

                               '--' || 'BASE_DIR3.SECBOUND' || UTL_TCP.CRLF);

          UTL_SMTP.WRITE_DATA(v_connection_handle,

                               'Content-Type: application/octet-stream' ||

                               UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               'Content-Disposition: attachment; ' || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               ' filename="' || pfilename || '"' || --My filename

                               UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

       

       

       

       

         /* Writing the BLOL in chunks */

           WHILE l_pos < l_blob_len LOOP

             DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);

             UTL_SMTP.write_raw_data(v_connection_handle,

                                    UTL_ENCODE.BASE64_ENCODE(l_buffer));

             UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

             l_buffer := NULL;

             l_pos    := l_pos + l_amount;

          END LOOP;

           UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

       

       

           -- Close Email

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               '--' || 'BASE_DIR3.SECBOUND' || '--' || UTL_TCP.CRLF);

           UTL_SMTP.WRITE_DATA(v_connection_handle,

                               UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);

       

       

       

       

       

       

           UTL_SMTP.CLOSE_DATA(v_connection_handle);

           UTL_SMTP.QUIT(v_connection_handle);

       

       

       

       

        EXCEPTION

          WHEN OTHERS THEN NULL;

          --return 1;

            UTL_SMTP.QUIT(v_connection_handle);

            RAISE;

       

       

        END;

       

       

      END;

      /

        • 1. Re: How to ZIP file and send via SMTP in Oracle
          ascheffer

          Excel in XLSX is a zipped file, so compressing it again won't make it much smaller.

          • 2. Re: How to ZIP file and send via SMTP in Oracle
            EdStevens

            Why use UTL_SMTP when oracle has provided the much simpler UTL_MAIL package?

             

            PS:  please do us all a favor by editing your profile to give yourself a recognizable screen name.

            • 3. Re: How to ZIP file and send via SMTP in Oracle
              HimaOne

              @ascheffer

              i want sent file by using create excel with XLSX and zip itu.. and 1 time i do it, i saw it compressed

               

              what i want to do is, sent data automatically from Oracle Job (the data i send every monday) from a view.. and its an xls file

              when i tried to send the data is too big and if should compressed itu.

              can u tell me how to do that?

               

              @EdStevens

              I dont know different UTL SMTP, UTL MAIL and when i try use UTL SMTP it works.

              can u teach me how to send data from view and sent it compressed ?

              • 4. Re: How to ZIP file and send via SMTP in Oracle
                Raunaq

                UTL_MAIL has a limit of 32k on attachments , so for large files UTL_MAIL is preferrable

                 

                You can refer the below link for more info

                 

                Ask Tom ;Does UTL_MAIL have an attachment limit

                • 5. Re: How to ZIP file and send via SMTP in Oracle
                  Billy~Verreynne

                  when i execute this, Error ocured

                  Message       : ORA-29278: SMTP transient error: 421 Service not available

                   

                  Error means that an invalid/unreachable/firewalled SMTP server and port were used. In other words, the TCP socket connect() call to the server failed.

                  • 6. Re: How to ZIP file and send via SMTP in Oracle
                    Raunaq

                    when i execute this, Error ocured

                    Message       : ORA-29278: SMTP transient error: 421 Service not available

                     

                    Once i also got this error , it was mainly because i was trying to use a connection after closing it.

                    So please check in your code that your are not using the connection after closing it.

                    • 7. Re: How to ZIP file and send via SMTP in Oracle
                      HimaOne

                      @Raunaq

                      I've to send data with lot of char and if i use UTL_MAIL, it will be limited again right?

                       

                      Let see, i;ve data contain 2group. KHK and KHT then i make it simple

                      with

                       

                      select * from V_KH

                      where group like 'KH%'

                       

                      its do some errors again >.<

                       

                      but when i chage it

                      select * from V_KH where group like 'KHT'  and select * from V_KH where group like 'KHK'

                       

                      both of them can be send >.<

                       

                      what happen.. it is cause limited by smtp?

                      cause the error is same

                       

                      when i execute this, Error ocured

                      Message       : ORA-29278: SMTP transient error: 421 Service not available

                      ORA-06512           : at "SYS.UTL_SMTP", line 21

                      ORA-06512           : at "SYS.UTL_SMTP", line 97

                      ORA-06512           : at "SYS.UTL_SMTP", line 399

                      ORA-06512           : at "PU22PROD_123.SEND_SMTP_PUZZLE_DTKRY", line 151

                      ORA-29294           : A data error occurred during compression or uncompression.

                      ORA-06512           : at "PU22PROD_123.CREATE_EXCEL_KPITERM", line 60

                      ORA-06512           : at line 2

                      • 8. Re: How to ZIP file and send via SMTP in Oracle
                        HimaOne

                        this is my smtp procedure

                         

                        CREATE OR REPLACE PROCEDURE PROD.SEND_SMTP_PUZZLE_DTKRY (pSender VARCHAR2,pRecipient VARCHAR2, pCCRecipient VARCHAR2, pSubject VARCHAR2,pMessage LONG,pattach BLOB,pfilename VARCHAR2) IS

                          v_src_loc  BFILE := BFILENAME('BASE_DIR3', 'pajak.xls');

                              l_buffer   RAW(54);

                              l_amount   BINARY_INTEGER := 54;

                             l_pos      INTEGER := 1;

                             l_blob     BLOB := EMPTY_BLOB;

                             l_blob_len INTEGER;

                              v_amount   INTEGER;

                              crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);

                         

                         

                          v_connection_handle  UTL_SMTP.CONNECTION;

                            v_smtp_host          VARCHAR2(30) := 'mail.mayora.co.id'; --My mail server, replace it with yours.

                            v_subject            VARCHAR2(30) := 'Your Test Mail';

                            l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';

                          pcc varchar2(50);

                         

                         

                          i number := 1;

                          j number := 1;

                         

                         

                          l_original_blob blob;

                          l_compressed_blob blob;

                        BEGIN

                         

                         

                           BEGIN

                         

                         

                         

                         

                             /*Preparing the LOB from file for attachment. */

                             --DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file

                         

                         

                             --dBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.

                         

                         

                             --v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.

                             --DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB

                             --l_blob_len := DBMS_LOB.getlength(l_blob);

                         

                         

                          l_original_blob     := pattach;

                             l_compressed_blob   := TO_BLOB('1');

                         

                         

                         

                         

                          UTL_COMPRESS.lz_compress (src => l_original_blob,

                                                       dst => l_compressed_blob);

                         

                         

                          --DBMS_LOB.FREETEMPORARY(l_compressed_blob);

                         

                         

                          l_blob := l_compressed_blob;

                             l_blob_len := DBMS_LOB.getlength(l_blob);

                         

                         

                         

                         

                             /*UTL_SMTP related coding. */

                             v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);

                         

                         

                             UTL_SMTP.HELO(v_connection_handle, v_smtp_host);

                             UTL_SMTP.MAIL(v_connection_handle, psender);

                             UTL_SMTP.RCPT(v_connection_handle, precipient);

                         

                         

                         

                         

                            if pCCRecipient is not null then

                                if(instr(pCCRecipient,',') = 0) then

                                utl_smtp.rcpt(v_connection_handle, pCCRecipient);

                                else

                         

                         

                                while(instr(pCCRecipient,',',i) > 0)

                                loop

                                pcc := substr(pCCRecipient,i, instr(substr(pCCRecipient,i),',')-1);

                                i := i+instr(substr(pCCRecipient,i),',');

                                utl_smtp.rcpt(v_connection_handle,pcc);

                                end loop;

                         

                         

                                pcc := substr(pCCRecipient,i,length(pCCRecipient));

                                utl_smtp.rcpt(v_connection_handle,pcc);

                                end if;

                            end if;

                         

                         

                             --UTL_SMTP.RCPT(v_connection_handle, v_cc_email_address);

                             UTL_SMTP.OPEN_DATA(v_connection_handle);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                   'FROM' || ': ' ||  psender || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                   'TO' || ': ' ||  precipient || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                   'CC' || ': ' ||  pCCRecipient || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                   'SUBJECT' || ': ' ||  pSubject || UTL_TCP.CRLF);

                         

                         

                         

                         

                             --MIME header.

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 'MIME-Version: 1.0' || UTL_TCP.CRLF);

                            UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 ' boundary= "' || 'BASE_DIR3.SECBOUND' || '"' ||

                                                 UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

                         

                         

                         

                         

                             -- Mail Body

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 '--' || 'BASE_DIR3.SECBOUND' || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 'Content-Type: text/plain;' || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 ' charset=US-ASCII' || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle, Pmessage || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

                         

                         

                         

                         

                             -- Mail Attachment

                           UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 '--' || 'BASE_DIR3.SECBOUND' || UTL_TCP.CRLF);

                            UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 'Content-Type: application/octet-stream' ||

                                                 UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 'Content-Disposition: attachment; ' || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 ' filename="' || pfilename || '"' || --My filename

                                                 UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

                         

                         

                         

                         

                           /* Writing the BLOL in chunks */

                             WHILE l_pos < l_blob_len LOOP

                               DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);

                               UTL_SMTP.write_raw_data(v_connection_handle,

                                                      UTL_ENCODE.BASE64_ENCODE(l_buffer));

                               UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

                               l_buffer := NULL;

                               l_pos    := l_pos + l_amount;

                            END LOOP;

                             UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

                         

                         

                             -- Close Email

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 '--' || 'BASE_DIR3.SECBOUND' || '--' || UTL_TCP.CRLF);

                             UTL_SMTP.WRITE_DATA(v_connection_handle,

                                                 UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);

                         

                         

                         

                         

                         

                         

                             UTL_SMTP.CLOSE_DATA(v_connection_handle);

                             UTL_SMTP.QUIT(v_connection_handle);

                         

                         

                         

                         

                          EXCEPTION

                            WHEN OTHERS THEN NULL;

                            --return 1;

                              UTL_SMTP.QUIT(v_connection_handle);

                              RAISE;

                         

                         

                          END;

                         

                         

                        END;

                         

                         

                        is there a mistake?

                        • 9. Re: How to ZIP file and send via SMTP in Oracle
                          Billy~Verreynne

                          What do you not understand with the following?

                           

                          Error means that an invalid/unreachable/firewalled SMTP server and port were used. In other words, the TCP socket connect() call to the server failed.

                           

                          Once i also got this error , it was mainly because i was trying to use a connection after closing it. So please check in your code that your are not using the connection after closing it.

                           

                          Have you

                          • checked that the SMTP server is reachable (on port 25) from your Oracle server?
                          • ensured that your code is not using a closed connection (socket)?
                          • 10. Re: How to ZIP file and send via SMTP in Oracle
                            Raunaq

                            Yes , the server was reachable.

                            I had made separate procedures for opening a connection and adding an attachment.(You might remember)

                             

                            Unknowingly i was closing the connection in the first and using the connection parameter i was attaching the file in the second procedure.

                            Then also , it was giving the same error.