It is easy to export and save output of executed sql plus query results to excel -xls sheet using toad but how to do it on sql prompt. To do so you need to use some text formatting options and spool file name to save output.
To Spool SQLPLus output to Excel (.xls) file, the trick is to turn On “MARKUP HTML”.
SET PAGESIZE 40000 SET FEEDBACK OFF SET MARKUP HTML ON SET NUM 24 SPOOL file_name.xls ---- Execute your query SPOOL OFF SET MARKUP HTML OFF SPOOL OFF
Example:
SQL> SET PAGESIZE 40000 SQL> SET FEEDBACK OFF SQL> SET MARKUP HTML ON SQL> SET NUM 24 SQL> SPOOL ora_htl_hist.xls SQL> SQL>SELECT COUNT * FROM ORA.HTL_HIST; SQL> SQL>SPOOL OFF SQL>SET MARKUP HTML OFF SQL>SPOOL OFF
A file name ora_htl_hist.xls will be generated in the current directory. This is the best way to export and save sql plus output to xls sheet.
Really Nice way to get the output in XLS. Thanks a lot for saving a lot of time by simplifying and putting things together.
set NUM 24 option is for what? My column names are getting truncated. Shall i increase this limit in NUM 24?
Yes Arun increase this value if value is getting truncated. Basically it controls the default width used when displaying numeric values.
Hi, Thanks for the clear explanation, Also could you please help in adding the multiple queries output in a single XLS.
number fields with leading zeroes are getting trimmed off..is there any way to retain them..while spooling as xls