Hi,
The following in the code which I am using :
#!/bin/ksh
export ORACLE_HOME=/oracle/product/current
export PATH=$PATH:$ORACLE_HOME/bin
sqlplus -s schema/pswd@db <<EOF
set pagesize 200
set linesize 200
column Name format a25
column New_DeptName format a25
column Old_DeptName format a25
column New_Manager format a25
column Old_Manager format a25
spool $test.xls
SELECT a.first_name || ' ' || a.last_name Name, b.DEPTNAME New_DeptName ,
a.DEPARTMENT_NAME Old_DeptName, Get_New_Manager_Name(b.CS_DEPT_MGR_ID) New_Manager,
Get_Old_Manager_Name (a.manager_id) Old_Manager
FROM table1 a, table2 b,table3 c
WHERE a.emplid = c.emplid
AND a.EMPLID=b.emplid
AND b.EMPL_STATUS = 'A'
AND a.DEPARTMENT_ID <> b.deptid
order by Consultant_Name;
spool off
exit;
EOF
havingRows=`grep -c "no rows selected" $test.xls`;
if [ "$havingRows" -lt 1 ]; then
#mailx -s "this is a test"
test@test.com <$test.txt
ux2dos $test.xls | uuencode test.xls | mailx -m -s "This is a test"
test@test.com
fi