Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts

Monday, July 09, 2007

Extracting Data to Excel using only SQL*Plus

Lately I had to quickly extract some data from Oracle DB to Excel. There are a lot of tools that can do that, ex. Toad or SQL Developer, but I had only access to SQL*Plus. Additionally I was on Windows machine and database was in UTF8. And extracted data was also using many different languages.
Here are steps I used to archive my goal:
  1. start command line (CMD)
  2. set NLS_LANG variable
    set NLS_LANG=.UTF8
  3. start sqlplus and log to database
    sqlplus user/pwd@db
  4. set long line size and avoid paging
    SQL> set linesize 4000
    SQL> set pagesize 0
  5. set markup html in sqlplus
    SQL> set markup html on spool on

    It is important to use spool on clause, as it adds html header to spool file when spooling is started
  6. start spooling
    SQL> spool a.html
  7. run your query
  8. turn off spooling
    SQL> spool off
  9. open file in Excel
I've tested this procedure on Oracle 10g R2 (10.2.0.1 and 10.2.0.3) and Excel 2003

Cheers, Paweł
 

Copyright © Paweł Barut
Printing from DOS to USB Printer