How to get sql query output in xml format from putty session

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:

How to change database DB name using NID utility

Stuck Thread in WebLogic Error and solution

How to create weblogic user

What is PDB and CDB in oracle 12c

ORA-01110 data file 1: system01.dbf recovery

Regards,