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.