Flowers To India

Flowers To India
Send cakes,flowers to India

Wednesday, November 14, 2012

How to spool data to excel sheet from Oracle

#sqlplus user/password
SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;
SQL> SPOOL test.xls;
SQL> SELECT * FROM TAB;
SQL> SPOOL OFF;
------------------------------------------------------------------------

For not getting any sql or feed back or lines, for getting proper excel report I have done like below.

export ORACLE_SID=sid
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export ORACLE_HOME=home path
export PATH=$ORACLE_HOME/bin:$PATH
export DD=`date +%d%m%Y`
sqlplus username/password <show user;
alter session set nls_date_format='dd/mm/yyyy';
set pagesize 0 trimspool on feedback off lines 900;
set feedback off;
SET VERIFY    OFF;
SET TRIMSPOOL ON ;
set trimspool on;
set pages 1500;
set lines 1000;
set underline off;
set colsep ','
set echo off
@sqlscript.sql
exit;
EOF


in sqlscript.sql
spool filename_${DD}.csv
select * from emp;
spool off;
spool filename2.csv
select * from dept;
spool off

No comments: