Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

John Thorton May 26 2020

antobay wrote:

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.

  1. --extractCSVfiles
  2. EXCEPTION
  3. WHENOTHERSTHEN
  4. NULL;
  5. END;
  6. /
  7. spooloff;
  8. 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

Tubby May 26 2020
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,

Marked as Answer by antobay · Sep 27 2020
antobay May 26 2020

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"

1 - 3

Post Details

Added on May 26 2020
3 comments
691 views