January 07, 2011

Exporting Oracle data to HTML or Excel

To export data from an oracle database via SQL to an HTML file, you
can use the "markup html" feature.

This can also be used to Export data to Excel due it can interpret and format HTML files in a proper way


1. Logon to SQLPlus

$ sqlplus user/pw@SID

2. Turn off paging and extend the line size for our requirements
SQL> set linesize 4000
SQL> set pagesize 0

3. Set HTML markup and activate spooling
SQL> set markup html on spool on

4. Name spoolfile
SQL> spool export.html

5. Now, let your query run just as always, e.g.
SQL> select * from tablename;

6. Turn off spooling
SQL> spool off

7. Check out the results in your exported html file with browser or Excel/Open Office. ;-)


Post a Comment