CODEWITHSUNDEEP
oracle-databaseplsqlclobvarchar2
user2294897
user2294897

Reputation: 1

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

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.

Upvotes: 0

Views: 605

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16905

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.

Upvotes: 1

Related Questions