Muhammad Nadeem Chaudhry's Blog


Spool to a .xls file

Posted in Uncategorized by Muhammad Nadeem Chaudhry on October 27, 2010

A small tip, I read on OTN about spooling to a .xls (excel) file:

It goes like this

set feed off markup html on spool on
spool c:\salgrade.xls
select * from salgrade;
spool off
set markup html off spool off

And the xls it makes shows up like:

HTML output from sqlPlus

Oracle is a very feature rich high end database, however sqlPlus is one of it’s weakness. Time to time it you need to list the content of a table or view in Oracle using sqlPlus and it usually results a hard interpretable output. There is the possibility to set line size parameter and modify the display size of the columns but it takes to much time.  

I nice solution would be to display the output in a HTML format. Fortunately we can do that. Now I will show you 2 solutions. The first one is working from the command line and the second one is working inside sqlPLus.

1. Generate Oracle HTML output from command line:

Code:

1.  sqlplus -S -M “HTML ON TABLE ‘BORDER=”2″‘” user/password @test.sql>test.html 

In this example you need to create the test.sql file and put all your sql statements in this file. The result will be written into the test.html file.

2. Generate Oracle HTML output inside sqlPlus:

Code:

1.  SET markup HTML on

2.  spool test.html

3.  SELECT * FROM mytable;

4.  spool off

5.  SET markup HTML off

In this case you can write all of your sql statements between the spool commands. The test.html file will be generated in the actual directory.

As you can see these solutions are not the best as you can view the files in a browser and not in sqlPlus, however in a lot of cases this solution makes your life easier.

Spool a Flat File with Column Headings
 
Try something like this

rem Script to spool a file with column headings.
rem This one is csv, but you can make it tab-delimited by changing || ‘, ‘
|| to || chr(9) ||

rem or any other character you want to delimit by.

set echo off
set pagesize 0

— this turns off column headings, too. If you only want the column heading to appear once, then increase the pagesize — equal to the number of rows + 2 (to account for the column header and the “——…” divider right below it.

–For example ,  if your query returns 1000 records, then set your pagesize to 1002 (or greater).
 
set feedback off
set linesize 200

— make the line wide enough for your purpose set trimspool on
— get rid of trailing spaces used to fill out the line size

set termout off

spool filename.txt

— If you make the extension csv, Excel will automatically load and format the columns
— You might not want this to happen if some columns contain numeric values like ZIP
— codes that have leading zeroes and you want to keep them

select colA || ‘,’ ||
colB || ‘,’ ||
colC || ‘,’ ||
colD
from dual;

select colA.
colB,
colC,
colD
from your_table;
spool off
exit

Note:

If table that need to be spooled, has billions records i recommend to use spooling method as illusterated above rather than using Oracle Toad to save time

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: