0 Replies Latest reply on Jun 13, 2016 1:56 PM by 1048687

    sending csv attachment  from plsql.

    1048687

      Dear team,

       

      We are sending an email with csv attachment. If the csv has 1400 records its taking 15 minutes.

       

      The  requirement is to fetch the data from table thru static cursor  and building a utl_file in temporary directory and writing the cursor output record by record into csv file.

       

      We have observed that for 1400 records fetched from cursor its taking 15 minutes to send the email attachment.

       

      We analysed further and found that writing 1400 records one by one from cursor to the utl_file is taking 14 minutes.

       

      ie  difference in  time stamp: between Before opening cursor  and Before closing cursor is 14 minutes.

       

         l_out_rec is varchar2(2000);

       

      DBMS_OUTPUT.put_line ('Before opening cursor ...'|| CURRENT_TIMESTAMP);

          

          FOR r_error_msg_info IN testStg(p_startdate,p_enddate)

            LOOP

              

            

               l_rec_exists := 'Y';

               l_notify_rec_count := l_notify_rec_count + 1;

               DBMS_OUTPUT.put_line ('l_notify_rec_count ' || l_notify_rec_count || '.........'||CURRENT_TIMESTAMP);

               l_resp_team := NULL;

              

               l_out_rec :=

                     r_error_msg_info.col1

                  || CHR (44)

                  || r_error_msg_info. col2

                  || CHR (44)

                  || r_error_msg_info.col3

                  || CHR (44)

                  || r_error_msg_info.col4

                  || CHR (44)

                  || '"'

                  || r_error_msg_info.col5

                  || '"'     ;

               DBMS_OUTPUT.put_line (' before writing to file ..'|| CURRENT_TIMESTAMP);

               UTL_FILE.put_line (v_filehandle, l_out_rec);

               DBMS_OUTPUT.put_line ('after writing to file..'|| CURRENT_TIMESTAMP);

            END LOOP;

           

          DBMS_OUTPUT.put_line ('After closing cursor...'|| CURRENT_TIMESTAMP); 

         end loop;

       

      Please suggest any alternate approach which can save time