Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

How to create multiple .csv in email attachment in a loop

antobayMay 26 2020 — edited May 29 2020

Hello Everyone

I will be grateful for your advice on how to accomplish the above. I have a table that contain multiple dates in a column. So select distinct date on this column looks like this

27/05/2020

28/05/2020

29/05/2020

I want to loop round this table and create a record as .csv attachment for every date, so in this case it will be 3 attachments.

I attach my code here. The problem am having is that it continue to print all date records in one attachment as opposed to creating different attachments.

Many thanks for your advice.

--extract CSV files

--Create File 1

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

set verify off

set feedback off

set newpage none

alter session

set nls_numeric_characters = ',.';

set heading off

set pause off

set serveroutput on size 1000000

set linesize 9999

set pagesize 0

set trim on

set trims on

spool /users/&&1/file1_&&2..csv;

- &&1 --user

-- &&2 --file name

-

DECLARE

CURSOR c_get_dates IS

    SELECT distinct delrdd delrdd

    from table1;

CURSOR c_extract(p_delrdd in date) IS

    SELECT col1,

           col2,

           col3,

           col4,

           col5

      FROM table1

       WHERE trunc(delrdd) = p_delrdd;

BEGIN

 

  FOR date_rec IN c_get_dates LOOP

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

  --text_io.put_line(file_id,

  dbms_output.put_line('hdr1' || ',' || 'hdr2 || ',' || 'hdr3' || ',' ||'hdr4' || ',' ||'hdr5');

  FOR r_d IN c_extract(date_rec.delrdd) LOOP

   BEGIN

     --text_io.put_line(file_id,

      dbms_output.put_line(r_d.col1 || ',' || r_d.col2 || ',' ||r_d.col3 || ',' || r_d.col4 || ',' ||r_d.col5);

   

      EXCEPTION

        --output data line

        WHEN OTHERS THEN

          null;

      END; --output data line

    END LOOP;

--spool off;

    END LOOP;

EXCEPTION

WHEN OTHERS THEN

    NULL;

END;

/

spool off;

exit;

This post has been answered by Tubby on May 26 2020
Jump to Answer
Comments
Post Details
Added on May 26 2020
3 comments
531 views