Forum Stats

  • 3,769,696 Users
  • 2,253,009 Discussions
  • 7,875,155 Comments

Discussions

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

antobay
antobay Member Posts: 289 Blue Ribbon
edited May 29, 2020 9:17AM in SQL & PL/SQL

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;EXCEPTIONWHEN OTHERS THEN    NULL;END; / spool off;exit;
antobay

Best Answer

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited May 26, 2020 10:13AM Accepted Answer

    You are using spool, if you want multiple files you'd need to get the distinct dates outside of this script and then spool one file per date.

    Or you could look at using UTL_FILE if these files are going to be on the server.

    Cheers,

    antobay

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 26, 2020 10:05AM
    antobay wrote:Hello EveryoneI 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/202028/05/202029/05/2020I 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.
    1. --extractCSVfiles
    2. EXCEPTION
    3. WHENOTHERSTHEN
    4. NULL;
    5. END;
    6. /
    7. spooloff;
    8. exit;
    --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;

    WHEN OTHERS THEN NULL is a bug that should NEVER exist withing any PL/SQL code

    click on URL below & read why

    http://www.orafaq.com/wiki/WHEN_OTHERS

    antobay
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited May 26, 2020 10:13AM Accepted Answer

    You are using spool, if you want multiple files you'd need to get the distinct dates outside of this script and then spool one file per date.

    Or you could look at using UTL_FILE if these files are going to be on the server.

    Cheers,

    antobay
  • antobay
    antobay Member Posts: 289 Blue Ribbon
    edited May 26, 2020 12:12PM

    Thanks for your Tubby, Just wondering if it will work if i include the loop on the existing shell script that calls this sql script like below.

    I will appreciate your advice.

    VOUTFILE="/users/$2/file1_.csv"

    VSQLEX="my_sql"

    #-----------------------------------------------------------------

    #Create file(s)

    echo "Start ${VSQLEX}.sql"

    #

    declare

      CURSOR c_get_dates IS

        SELECT distinct delrdd delrdd

        from table1;

    BEGIN

      FOR date_rec IN c_get_dates LOOP

      $VDTE := date_rec.delrdd

    sqlplus -s / @$WORKDIR/sqlex/${VSQLEX}.sql $VDTE

    END LOOP

    echo "after ${VSQLEX}.sql"