An Oracle blog – Amardeep Sidhu

June 26, 2007

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

Filed under: Oracle Tips,SQL — Sidhu @ 10:38 pm

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

2 Comments »

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

    Comment by Sandep — July 7, 2011 @ 4:31 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Theme: Rubric. Get a free blog at WordPress.com

%d bloggers like this: