extracting data into text file

I have written a shell script to login to sqlplus, run sql and spool into a text file.
Here is a part of the script:
sqlplus $login < set feed off;
set verify off;
set head off;
set echo off;
set termout off;
#set page 0;
spool $pth/GLACCT.D$dt;
select rownum||'|'||acct||'|'||acct_desc||'|'||entity||'|'||org_cd||'|'||prod_cd||'|'||state||'|'||invest_y ear||'|'||
amt_class||'|'||curr_cd||'|'||amt from bar_stg_ss_acct_balance
where aud_hdr_id in (select aud_hdr_id from bar_aud_hdr where to_char(load_dt,'mm/dd/yyyy')=to_char(sysdate,'mm/dd/yyyy'))
/
spool off;

Now the problem is the text file also contains the sql and the command spool off besides the data . How do I eliminate that from getting spooled??

hanx
Kavita

If you don't want your sql echoed to the screen, you have to run it from a script file.

Can you give me an example please?? I didn't follow the method

Thanx
Kavita

example - run this as a script and from sqlplus

Code:

---

spool tabs2.out
set termout off
select table_name from user_tables;
spool off
set termout on

---

As a script, your output will look like:

Code:

---

ABC
WAITSTAT_BEFORE   
XYZ123                                                                         
XyZ                                                                           
xyz

---

and from sqlplus, your output will look like:

Code:

---

SQL> set termout off
SQL> select table_name from user_tables;
ABC                                                                           
WAITSTAT_BEFORE   
XYZ123                                                                         
XyZ                                                                           
xyz                                                                           
SQL> spool off

---

I am running it from a shell script so your example doesn't work it works otherwise when you login to sqlplus manually, Well finally it worked when I added -s option while invoking sqlplus , here's the script which worked

sqlplus -s < set feed off;
set head off;
set echo off;
spool /ex.dat;
select * from user_tables;
spool off;
!

Thanks

Quote:

---

Originally posted by Knarayan
I am running it from a shell script so your example doesn't work

---

That was the point. If you put your commands in a script file and then:
sqlplus / @scriptname.sql

Got you Jeff.:D

Thanx