Thursday, April 7, 2011

Oracle output into excel sheet

Hi All

this is the proto-type format of shell script embedded with SQL script , which output into a excel sheet.
=========================================
#!/bin/ksh
echo $ORACLE_HOME
myfile=/home/oracle/awr/perf.sql
emailfile=/home/oracle/awr/MY_TEST_FILE.xls
echo $myfile
echo $emailfile
$ORACLE_HOME/bin/sqlplus -s xxx/yyy@conn_identifier @$myfile
cat $emailfile | uuencode $emailfile | mailx -s "report" jignesh.kankrecha@jpmchase.com
exit;

============================================

####################################
put $myfile which is /home/oracle/awr/perf.sql in below format
####################################
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
set linesize 125
set numwidth 10
set pagesize 50
col TEXT FOR A30
col ELAPSED_TIME_SEC for 99999999
SET ECHO OFF;
SPOOL MY_TEST_FILE.xls;
--your SQL STATEMENT HERE;
spool off;
exit;
####################################