SQL*PLUS Spool Output

Shell Programming and Scripting
1

Hi,
Im writing a script to run a bit of sql(via sqlplus) that pulls back some data and spools it to a file, I want the spool file to only display the data, with no sql command at the top and no reports at the bottom ie(# of records recieved).

I am currently doing it via a grep command but ideally I would like to just turn this reporting off. I have tried to use set echo off, set termout off, set feedback off with no luck.

I'm pretty new to shell so could be a rookie mistake.

CODE:

#!/bin/bash

#Checks if dir exists and creates it if not

datedDir=`date +'%Y%m%d'`
mkdir -p $datedDir

#Sets the Log file name to passed in variable
logFile=${datedDir}/$1

set echo off
set verify off
set termout off
set feedback off

sqlplus -s DB/USER@PW << ENDOFSQL

set pagesize 0

spool $logFile

select * from all

spool off

ENDOFSQL

timeStamp=`date +'%Y%m%d%H%M%S'`


grep -v '[a-zA-Z^$]' ${logFile}.lst > ${logFile}_$timeStamp.dat

# removes the spooled file
rm ${logFile}.lst

touch ${logFile}_$timeStamp.rdy

exit;

My out put is :

SQL> select ........

data

### rows selected

I would just like the data

2

First, the 'sets' need to be within the sql plus call:

sqlplus -s DB/USER@PW << ENDOFSQL
set echo off
 set verify off
 set termout off
 set feedback off
 
set pagesize 0
spool $logFile
select * from all
spool off

ENDOFSQL

Second, I set:

set echo off
set show off
set term on
set heading off
set feedback off
set linesize 200
set pages 0