Link to home
Start Free TrialLog in
Avatar of DevSupport
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(sysdate, 'yymmdd')||'_'||to_char(sysdate, '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_WHATCHANGE;
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_CAPTURED, '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!
ASKER CERTIFIED SOLUTION
Avatar of Pavel Gushchin
Pavel Gushchin

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial