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:
- start command line (CMD)
-
set NLS_LANG variable
set NLS_LANG=.UTF8
-
start sqlplus and log to database
sqlplus user/pwd@db
-
set long line size and avoid paging
SQL> set linesize 4000
SQL> set pagesize 0 -
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 -
start spooling
SQL> spool a.html
-
run your query
-
turn off spooling
SQL> spool off
- open file in Excel
Cheers, Paweł
3 comments:
one thing you can try as well:
define a dsn for ODBC in the client to the Oracle data provider and your database alias, setup oracle client on that pc and then run the query directly from inside Excel, using a SQL statement from inside the spreadsheet.
works quite well.
ODBC is also an solution. But in my case I could not force it to work correctly with UTF8 data. I had data in many languages and lot of data get corrupted that way.
Pawel,
This trick is great as I have an web application which handles many languages and I could not get output which is readable from queries. Thanks for the good tip.
- Susanta
Post a Comment