Dear DBA-Mates, How
to get sql query output in xml format.
Hope you all are doing well and
Safe at Home!!!
We would like to share one of very important and useful
query for How to get SQL Query output in xml/xls
readable format.
This is very headache if you are running in Putty
Session.
Even we also use this same format for such queries. So,
we thought to share to you all and it will be easy for us also for references
in future.
This type of request will be send by the developer or
functional team which they may need for some analysis or monthly reports
generation.
Whatever if they asked then as a DBA we need to provide
them, even though angry inside Hahahah.
And the worst things those queries will be running 2 to 3
hrs and rows may be 2 to 3 lakhs, so can imagine it.
$sqlplus / as sysdba
SQL> SET LINESIZE 10000
SQL> SET VERIFY OFF
SQL> SET FEEDBACK OFF
SQL> SET PAGESIZE 999
SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON
PREFORMAT OFF
SQL> SPOOL <Output_Filename>.xls
SQL> set time on ## To Capture query start and end time
SQL> @<Filename>.sql
SQL> spool off;
Optional:
SQL>SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT
ON
SQL>SET LINESIZE 2000 VERIFY ON FEEDBACK ON
OR
SQL> set feed off markup
html on spool on
SQL> SET PAGESIZE 50000
SQL> SPOOL <output_filename>.xls
SQL> <your_query like
select * from dual;>
SQL> SPOOL OFF;
OR
SQL> set pagesize 50000
SQL> set heading on
SQL> set echo off
SQL> set colsep ','
SQL> set heading on
SQL> SET LINESIZE 999 VERIFY OFF FEEDBACK
OFF
SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON
PREFORMAT OFF
SQL> spool workflow.xls
SQL> <Your query>
SQL> Spool off;
OR
Save Query output as HTML file from SQLPLUS
As alternative you may use below block of code from the
sqlplus only to save the output as HTML file.
SQL> set linesize 4000
SQL> set verify off
SQL> set feedback off
SQL> set pagesize 9999
SQL> set markup html on entmap on spool on preformat
off
SQL> spool /tmp/query_output.html
SQL> --Your query--
SQL> set markup html off
SQL> spool off;
Hope this ma useful and helpful for everyone here.
Some more useful link:
Regards,
