 |
|
Oracle create spreadsheet tips
Oracle Tips by Burleson Consulting
|
Question: I want to write SQL that will put the
output into spreadsheet form. ODBC is far too slow for writing spreadsheets
from Oracle. What are the ways to output a Excel spreadsheet from Oracle?
Answer: Oracle has many ways to output spreadsheet
data including punching Oracle data from tables in a comma-delimited (csv)
format.
1 - Excel creation tools
- Using inexpensive tools such as Excel-DB
for full Oracle spreadsheet interfaces (i.e. creates .xls spreadsheets from
Oracle).
2 - SQL*Plus to create excel
spreadsheet - In the simplest format, this SQL will create a
comma-delimited flat file, which can be read my MS-Excel as a .csv file:
set feedback off
set
heading off
set underline off
set colsep ','
spool /u01/app/oracle/export/mysheet.cs
select * from mytab'
spool off
3 - shell script to create Oracle
data Excel spreadsheet - You can also generate an Excel spreadsheet
from Oracle data within a UNIX/Linux shell script:
echo "select * from emp;"|
sqlplus -M "HTML ON" -s scott/tiger|
uuencode emp.xls|
mail laurentschneider@example.com
Of course there are limits to the power of csv spreadsheets
and most Oracle professionals will use an inexpensive tool (Excel-DB) to create
“real” .xls spreadsheets from Oracle queries. These tools also have the
performance benefits of using native API’s which are far faster than ODBC.
Here are other notes on creating spreadsheets with Oracle
data:
