Need help to get a file as a whole in a single mail which is of the size 4 mb in oracle PL/SQL

Question

The below code in a PL/SQL procedure checks for the byte size and if it is above 30000 bytes it iterates the data and sends mail attachments(txt file through multiple mails) for every 30000 bytes(30 kb).
I need to get a file as a whole in a single mail which is of the size 4 mb in oracle PL/SQL.

The data type of lv_message_tab is varchar2(32767) . Can i user CLOB datatype instead of *Varchar2(32767),*if yes please suggest how.

Thanks a million in advance !!!

FOR lv_idx in 1..pv_message.COUNT
      LOOP
         lv_message := lv_message||pv_message(lv_idx)|| lv_cr;

         IF LENGTH(lv_message) >= 30000
         THEN

          lv_message_tab.EXTEND;
          lv_message_tab(lv_message_tab.LAST) := lv_message;
          lv_message := null;

         END IF;

      END LOOP;
--This send the attachment for 30000 characters and above
      IF lv_message_tab.COUNT > 0 
      THEN

           FOR lv_idx in 1..lv_message_tab.COUNT
           LOOP

              UTL_SMTP.HELO(lv_conn, lv_smtp_host);
              UTL_SMTP.MAIL(lv_conn, gc_sender);
              UTL_SMTP.RCPT(lv_conn, lv_to_email_ids);
              UTL_SMTP.RCPT(lv_conn, lv_cc_email_ids);

               UTL_SMTP.DATA(lv_conn,
              'Date: '   || TO_CHAR(SYSDATE, gc_dd_month_yyyy_format) || lv_cr ||
              'From: '   || gc_sender || lv_cr ||
              'Subject: '|| lv_subject || lv_cr ||
              'To: '     || lv_to_email_ids || lv_cr ||
              'CC: '     || lv_cc_email_ids || lv_cr ||

              'MIME-Version: 1.0'|| lv_cr ||    -- Use MIME mail standard
              'Content-Type: multipart/mixed;'|| lv_cr ||
              ' boundary="-----SECBOUND"'|| lv_cr ||
                lv_cr ||

              '-------SECBOUND'|| lv_cr ||
              'Content-Type: text/plain;'|| lv_cr ||
              ' name="excel.csv"'|| lv_cr ||
              'Content-Transfer_Encoding: 8bit'|| lv_cr ||
              'Content-Disposition: attachment;'|| lv_cr ||
              ' filename="'||pv_file_name||'"'|| lv_cr ||
              lv_cr ||lv_message_tab(lv_idx)
              || lv_cr ||   -- Content of attachment
              lv_cr ||

               '-------SECBOUND--' );   

          END LOOP;

      END IF;

      --This send the attachment for the rest of the data...for the last few iterations
      IF lv_message IS NOT NULL
      THEN



          UTL_SMTP.HELO(lv_conn, lv_smtp_host);
          UTL_SMTP.MAIL(lv_conn, gc_sender);
          UTL_SMTP.RCPT(lv_conn, lv_to_email_ids);
          UTL_SMTP.RCPT(lv_conn, lv_cc_email_ids);

           UTL_SMTP.DATA(lv_conn,
          'Date: '   || TO_CHAR(SYSDATE, gc_dd_month_yyyy_format) || lv_cr ||
          'From: '   || gc_sender || lv_cr ||
          'Subject: '|| lv_subject || lv_cr ||
          'To: '     || lv_to_email_ids || lv_cr ||
          'CC: '     || lv_cc_email_ids || lv_cr ||

          'MIME-Version: 1.0'|| lv_cr ||    -- Use MIME mail standard
          'Content-Type: multipart/mixed;'|| lv_cr ||
          ' boundary="-----SECBOUND"'|| lv_cr ||
            lv_cr ||

          '-------SECBOUND'|| lv_cr ||
          'Content-Type: text/plain;'|| lv_cr ||
          ' name="excel.csv"'|| lv_cr ||
          'Content-Transfer_Encoding: 8bit'|| lv_cr ||
          'Content-Disposition: attachment;'|| lv_cr ||
          ' filename="'||pv_file_name||'"'|| lv_cr ||
          lv_cr ||lv_message
          || lv_cr ||   -- Content of attachment
          lv_cr ||

           '-------SECBOUND--' );   


     END IF;


     UTL_SMTP.QUIT(lv_conn);


    pv_rtn := TRUE;

ELSE

    pv_rtn := FALSE;

END IF;

I am calling the procedure by giving the input as the collection table like

PROCEDURE

SP1_Send_letter_ATTACH_WAL(pv_job_id IN job.job_id%TYPE,
                           pv_status IN job_transaction_details.status_id%TYPE,
                           pv_subject IN VARCHAR2,
                           pv_message IN stringtable,
                           pv_file_name IN VARCHAR2,
                           pv_date_time IN DATE DEFAULT SYSDATE,
                           pv_rtn OUT BOOLEAN);

I just want to store the data of the variable pv_message to the variable lv_message which is a clob datatype.

lv_message CLOB;

HOW TO STORE lv_message := pv_message (i.e the collection datatype to CLOB type). I already tried to typecast using 'to_clob()' function like lv_message = to_clob(pv_message) but it didn't work.

A.B.Cade · Answer 1 · 2013-04-18T12:34:37.137

1

Try to use in the loop the UTL_SMTP.WRITE_DATA procedure instead of creating the whole mail.

I can't test it, but should be something like this:

          UTL_SMTP.HELO(lv_conn, lv_smtp_host);
          UTL_SMTP.MAIL(lv_conn, gc_sender);
          UTL_SMTP.RCPT(lv_conn, lv_to_email_ids);
          UTL_SMTP.RCPT(lv_conn, lv_cc_email_ids);

           UTL_SMTP.DATA(lv_conn,
          'Date: '   || TO_CHAR(SYSDATE, gc_dd_month_yyyy_format) || lv_cr ||
          'From: '   || gc_sender || lv_cr ||
          'Subject: '|| lv_subject || lv_cr ||
          'To: '     || lv_to_email_ids || lv_cr ||
          'CC: '     || lv_cc_email_ids || lv_cr ||

          'MIME-Version: 1.0'|| lv_cr ||    -- Use MIME mail standard
          'Content-Type: multipart/mixed;'|| lv_cr ||
          ' boundary="-----SECBOUND"'|| lv_cr ||
            lv_cr ||

          '-------SECBOUND'|| lv_cr ||
          'Content-Type: text/plain;'|| lv_cr ||
          ' name="excel.csv"'|| lv_cr ||
          'Content-Transfer_Encoding: 8bit'|| lv_cr ||
          'Content-Disposition: attachment;'|| lv_cr ||
          ' filename="'||pv_file_name||'"'|| lv_cr ||
          lv_cr;

 -- this is the importent part (other parts were taken from the question and were just moved out of the loop)
      FOR lv_idx in 1..lv_message_tab.COUNT
       LOOP

         UTL_SMTP.WRITE_DATA(lv_conn, lv_message_tab(lv_idx));
       END LOOP;

     UTL_SMTP.WRITE_DATA(lv_conn,lv_cr ||   -- Content of attachment
          lv_cr ||'-------SECBOUND--' );

There used to be a package called DEMO_MAIL on OTN but I can't find it...
If you could find it, it's very usefull.

edited Apr 18 '13 at 12:34

answered Apr 18 '13 at 12:26

A.B.Cade

16,735
1
37
53

The format of the mail should not be changed and since the size of the variable which stores the data lv_message is 32767 (since it is the max size of the varchar data type).I am not getting mail if i change the data type to CLOB.so is there any way to get it by using clob – user2294897 Apr 18 '13 at 12:44
Please note in the above procedure there are two blocks in the above procedure, if the data is below 30000 bytes it is sent as a single mail. – user2294897 Apr 18 '13 at 12:49
@user2294897, I'm not sure that I understand you right. All the procedures in the `UTL_SMTP` are restricted to the 32767 length. So if you want to send a mail with a message bigger than that, you must iterate and use `utl_smtp.write_data` to append more data. In my answer I used your code so I stayed with the `lv_message_tab` but of course you can hold it all in a clob and use [DBMS_LOB.SUBSTR](http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349) to iterate on the clob – A.B.Cade Apr 18 '13 at 13:02
I all need is to send the text file greater than 4 mb size (obviously it will greater than varchar2(32767) so i am using clob data type for lv_message instead of varchar) using clob in a single mail,without dividing the files in to many (iterating) – user2294897 Apr 19 '13 at 06:30
@user2294897,OK then hold the file content in the CLOB, add all the headers and stuff and then when coming to add the CLOB content - loop on chunks of 32767 (use `dbms_lob.substr`) and add them with `utl_smtp.write_data`. this will create a mail with one file (in whatever size you want) – A.B.Cade Apr 19 '13 at 07:01
Thank you very much A.B.Cade .. It was very help full – user2294897 Apr 19 '13 at 13:07

Need help to get a file as a whole in a single mail which is of the size 4 mb in oracle PL/SQL

1 Answers1