Friday, November 22, 2013

How to generate a spool CSV file with current date in windows envrionment discarding the select statement.

Hi Friends,

Sometimes we face problem in generating the CSV spool file which contains only records based on the filter condition on the select statement.

Basically we face this problem in Windows environment because whatever we mention after the set trimspool on , the same is copied to the CSV file also including the select statement and no of rows selected etc.

So to avoid such issue please proceed as mentioned below.


PROMPT
PROMPT *** my_script.sql *** START ***
PROMPT
set termout off
set echo off
set verify off
set heading off
set lines 420
set trimspool on
set pagesize 0
Set feedback off
col dcol new_value mydate noprint
select to_char(sysdate,'YYYY-MM-DD') dcol from dual;
spool C:\&mydate._MY_SPOOL_FILENAME.csv
select 'INIT,'|| e.employee_id ||','|| e.department_id||','||d.manager_id||','|| d.department_id
from employees e, departments d 
where e.department_id = d.department_id 
order by 1
/
spool off
set echo on
set termout on
PROMPT
PROMPT *** my_script.sql *** END ***



Cheers
Rajani

No comments:

Post a Comment