DevSupport
asked on
sort a spool into file output in oracle
Using Oracle query I would like to sort the output which I am spooling into csv file.
I am using the following query to create a csv file from table.
set lines 10000
set trimspool on;
set feedback off;
set pages 0;
set pagesize 10000;
/*set pagesize 5000;*/
/*spool datacollection_full_report .csv*/
col spoolname new_value spoolname
select 'datacollection_whatchange _report_'| |to_char(s ysdate, 'yymmdd')||'_'||to_char(sy sdate, 'hh24_mi_ss')||'.csv' spoolname from dual;
spool '&spoolname'
select SERVER ||','|| CHANGE_FROM ||','|| to_char(DATE_CAPTURED, 'DD-MM-YYYY') ||','|| CHANGE_TO ||','|| CHANGE_TYPE from APEX.DATACOLLECTION_WHATCH ANGE;
spool off
I want to sort the output of the select query such that I want only top 200 rows and the result should be ORDER BY DATE_CAPTURED DESC
i.e:
select SERVER, CHANGE_FROM,to_char(DATE_C APTURED, 'DD-MM-YYYY') as CAPTURE_DATE, CHANGE_TO, CHANGE_TYPE from DATACOLLECTION_WHATCHANGE WHERE ROWNUM <=150 ORDER BY DATE_CAPTURED DESC;
But I am unable to get this result into the spool file.
Please let me know if this is possible
Thank You so much!
I am using the following query to create a csv file from table.
set lines 10000
set trimspool on;
set feedback off;
set pages 0;
set pagesize 10000;
/*set pagesize 5000;*/
/*spool datacollection_full_report
col spoolname new_value spoolname
select 'datacollection_whatchange
spool '&spoolname'
select SERVER ||','|| CHANGE_FROM ||','|| to_char(DATE_CAPTURED, 'DD-MM-YYYY') ||','|| CHANGE_TO ||','|| CHANGE_TYPE from APEX.DATACOLLECTION_WHATCH
spool off
I want to sort the output of the select query such that I want only top 200 rows and the result should be ORDER BY DATE_CAPTURED DESC
i.e:
select SERVER, CHANGE_FROM,to_char(DATE_C
But I am unable to get this result into the spool file.
Please let me know if this is possible
Thank You so much!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.