OraFAQ Forum: Client Tools » How to spool multiple .csv as attachment (merged)

Home » Other » Client Tools » How to spool multiple .csv as attachment (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to spool multiple .csv as attachment (merged) [message #680624]

Tue, 26 May 2020 15:35

James_s
Messages: 7
Registered: April 2017

Junior Member

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.

I have been advised that this can only be achieved if I put the loop outside of this script and then spool one file per date. Please can you point me in the right direction.

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  
    dbms_output.put_line (SQLERRM);
END;   
/   
spool off;  
exit;

Report message to a moderator

Re: How to spool multiple .csv as attachment [message #680626 is a reply to message #680624]

Tue, 26 May 2020 16:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://community.oracle.com/thread/4331886

Report message to a moderator

Re: How to spool multiple .csv as attachment [message #680629 is a reply to message #680624]

Wed, 27 May 2020 00:37

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

WHEN OTHERS

Write a script which will dynamically generates a script to generate each file.
Something like:

-- set termout off 
set heading off feedback off
spool t.sql
select 'set heading on echo on
spool job_'||job||'.csv
select empno, ename, sal from emp where job='''||job||''';'||'
spool off'
from emp
group by job
/
spool off
@t

which gives:

SQL> @script
set heading on echo on
spool job_CLERK.csv
select empno, ename, sal from emp where job='CLERK';
spool off
set heading on echo on
spool job_SALESMAN.csv
select empno, ename, sal from emp where job='SALESMAN';
spool off
set heading on echo on
spool job_PRESIDENT.csv
select empno, ename, sal from emp where job='PRESIDENT';
spool off
set heading on echo on
spool job_MANAGER.csv
select empno, ename, sal from emp where job='MANAGER';
spool off
set heading on echo on
spool job_ANALYST.csv
select empno, ename, sal from emp where job='ANALYST';
spool off
SQL> spool job_CLERK.csv
SQL> select empno, ename, sal from emp where job='CLERK';
     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7876 ADAMS            1100
      7900 JAMES             950
      7934 MILLER           1300
SQL> spool off
SQL> set heading on echo on
SQL> spool job_SALESMAN.csv
SQL> select empno, ename, sal from emp where job='SALESMAN';
     EMPNO ENAME             SAL
---------- ---------- ----------
      7499 ALLEN            1600
      7521 WARD             1250
      7654 MARTIN           1250
      7844 TURNER           1500
SQL> spool off
SQL> set heading on echo on
SQL> spool job_PRESIDENT.csv
SQL> select empno, ename, sal from emp where job='PRESIDENT';
     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000
SQL> spool off
SQL> set heading on echo on
SQL> spool job_MANAGER.csv
SQL> select empno, ename, sal from emp where job='MANAGER';
     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES            2975
      7698 BLAKE            2850
      7782 CLARK            2450
SQL> spool off
SQL> set heading on echo on
SQL> spool job_ANALYST.csv
SQL> select empno, ename, sal from emp where job='ANALYST';
     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000
      7902 FORD             3000
SQL> spool off

using the temporary generated script:

SQL> host type t.sql
set heading on echo on
spool job_CLERK.csv
select empno, ename, sal from emp where job='CLERK';
spool off
set heading on echo on
spool job_SALESMAN.csv
select empno, ename, sal from emp where job='SALESMAN';
spool off
set heading on echo on
spool job_PRESIDENT.csv
select empno, ename, sal from emp where job='PRESIDENT';
spool off
set heading on echo on
spool job_MANAGER.csv
select empno, ename, sal from emp where job='MANAGER';
spool off
set heading on echo on
spool job_ANALYST.csv
select empno, ename, sal from emp where job='ANALYST';
spool off

In real case remove the "echo on" I put just to show you what happens and activate the "set termout off" I commented.

Report message to a moderator

Re: How to spool multiple .csv as attachment [message #680648 is a reply to message #680629]

Fri, 29 May 2020 08:30

James_s
Messages: 7
Registered: April 2017

Junior Member

Thank you Michel

Report message to a moderator

Previous Topic:	SQL Plus utility wrap command doesn't work
Next Topic:	SqlDeveloper tools in a secure environment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 30 18:20:37 CST 2024