SUPPORT THE SITE WITH A CLICK

Subscribe Rss:

SUPPORT THE SITE WITH A CLICK

Wednesday, March 2, 2011

Export oracle sql output to excel format

Usually for exporting oraclce sql output to any formats (text, pdf, excel, xml), we will go for toad or any other tools.But when we want to fetch the sql output from server, this is not an easy one.Some cases firewall issue will break the toad to get connected from server.

For exporting the oracle output to excel, we can use the following command in terminal

SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;
SPOOL /home/$user/sqldata.xls;
SELECT * FROM SITES.ARTICLES;
SPOOL OFF;

SPOOL :Stores query results in a file, or optionally sends the file to a printer

Step 1:
Will convert will remove all the spaces and convert all characters, symbols into ascii

Step 2:
Path to store the sql output as excel

Step 3:
Sql query to fetch

Step 4:
Swtiching off the spool

So its too simple to convert the sql results to excel format in oracle.