Hi Guys,

I am using linux to run the query.
I have one query like select * from Emp;
I want to spool the results into one file(this file has only exported data without number of rows selected message) and what ever the log i.e number of lines selected or error msg into another file.

how to get it done? could any one suggest me how to go.

I have tried below code but it is not working and i do not want to use count(*) in query.

sqlplus -s paml/aml12345@amladev<< EOF
set verify off
set colsep ","
set linesize 32000
set pagesize 40000
set feedback off
set serveroutput off
set heading off
COLUMN row_cnt_col NOPRINT NEW_VALUE row_cnt
spool $RBACKUP
select EMPNO, COUNT(*) OVER() AS row_cnt_col from EMP;
spool off
spool $RLOG
SELECT 'Number of lines exported : ' ||&row_cnt AS "total_rows_found" FROM dual;
spool off
EXIT;
EOF



Thanks:)

sqlplus -s ... << EOF
set verify off
spool $RBACKUP
...
spool off
EXIT;
EOF

if [ "`grep 'ORA-' $RBACKUP`" ] ;then
   echo We had an error
   mailx -s "ERROR" joe@support.com < $RBACKUP
   exit 1
fi

linecount=`wc -l $RBACKUP`
echo "Number of lines exported : $linecount" >> $RLOG