An Oracle blog – Amardeep Sidhu

Little bit of fun with Oracle and the related technologies…

Shell script to spool a no of tables into .xls files…

with 2 comments

On OTN someone asked a question that how to spool data from a table into a xls file. Spooling a single table I discussed in one of the previous posts. We can use the same approach to spool data from more than 1 table also. Well here I will do it through a shell script and assume that you have a text file having list of tables to be spooled (Even if you don’t have one, it can be easily made by spooling the names of tables into a simple text file) Here is the shell script that you can use to spool data to various xls files, table wise.

cat list.txt | while read a
do
echo "spooling $a"
sqlplus username/password@string <<EOF
set feed off markup html on spool on
spool /home/oracle/$a.xls
select * from $a;
spool off
set markup html off spool off
EOF
done

I didn’t see any work around for Windoze as SQLPLUS << EOF thing doesn’t seem to work in Windows. Will try to find some alternative. If you come across something, do let me know.

Sidhu

Written by Sidhu

June 26th, 2007 at 10:38 pm

Posted in Oracle Tips,SQL

2 Responses to 'Shell script to spool a no of tables into .xls files…'

Subscribe to comments with RSS or TrackBack to 'Shell script to spool a no of tables into .xls files…'.

  1. Very very useful..thanks a lot mate .

    Sandep

    7 Jul 11 at 4:31 pm

  2. Cheers ! :)

    Sidhu

    20 Jul 11 at 10:26 am

Leave a Reply

*
To prove you're a person (not a spam script), type the answer to the math equation shown in the picture. Click on the picture to hear an audio file of the equation.
Click to hear an audio file of the anti-spam equation