Oracle create spreadsheet tips Oracle Database Tips by Donald Burleson

 

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.csv

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: