Export Table To Spread Sheet with Oracle..

I used to hate oracle due to this .. I hated its bulkyness and hype… Money it makes with DBA certifications… Quite Similar to Microsoft’s strategies…

But as i have to work on Oracle, So i have to dig more , Though i have not reached some cgood performance measure to tune Oracle.. Except altering process size ..
I have been working on stored procedures, and quite curious excited about using them…

Now moving the title topic .. as i was digging up for performance tuning .. Could not get much support through.. Thats why DBA’s are paid much.. [:)]

I found this little sql script to export Table to a Spreadsheet document…and found that there is something in Oracle thats why Oracle is much hyped.. Its a powerful database…

It goes like this

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

If You are beginner like me .. Write this script with your favourite editor..and save it as filename.sql
Go To Command line /Shell Change directory to the location of script u saved..
Run “sqlplus” prompt for me it was “sqlplus /nolog” as i am using Open Suse 10.2 ..
after logging in :
type @filename ..
and it will export the spreadsheet to the location you mention .. In this cae to the same directory….

I got this from Amardeep s blog..He got it somewhere from Oracle Forums…

If You want Read multiple table.. Then here is the script (Not for windows though ) :

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 got it from here

Have fun with Oracle …
If some one can give me help on performance tuning most welcome … I am stuck with it…

Advertisements

2 thoughts on “Export Table To Spread Sheet with Oracle..

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 )

Google+ photo

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

Connecting to %s