Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Spool file requirement

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Spool file requirement
# 1  
Old 07-29-2016
Spool file requirement
Hi,
I have a requirement of
  1. Connecting to sqlplus from unix
  2. Execute the package. The output of package is stored in a table
  3. Need to query the table and move to txt file.

The problem iam facing is, when I try to spool the file. I get the sql query also along with the output. Spooled file looks like below:

Code:
 SQL> select col1 || ',' || col2 from test;
  
 ab,cd
 ef,gh
  
 SQL> spool off;

I don't want to see these sql statements. I used set trimout on, set echo off. But sql statement is not removed. Kindly help
Moderator's Comments:
Mod Comment Please use CODE tags (as required by forum rules) when displaying sample input, sample output, and code segments.
Last edited by rbatte1; 07-29-2016 at 10:29 AM.. Reason: Changed text-numbered list to formatted numbered list
# 2  
Old 07-29-2016
You would want to try the following:-
Code:
set feedback off
set heading off

How are you calling the sqlplus command? Can you share your script? Please wrap it in CODE tags. I fancy there is a flag required on the sqlplus call itself.



Robin
# 3  
Old 07-29-2016
Is that oracle? Or what? Why shouldn't set echo off work?

Quote:
... If echo is set to off, SQL statements that are run will not be displayed while setting echo to on will display them. ...
# 4  
Old 07-29-2016
Not sure how you are calling sqlplus, but sqlplus -s will put it in silent mode and remove everything apart from the result
Last edited by rbatte1; 07-29-2016 at 11:43 AM.. Reason: Added ICODE tags
# 5  
Old 07-29-2016
my code is as below:

Code:
 sqlplus username/pwd@sid <<EOF
 exec pkgname(in parameter);
 set heading off
 set echo off
 spool test_output.txt
 select col1 || ',' || grp_cd from test;
 spool off;
 exit
 EOF

---------- Post updated at 11:27 AM ---------- Previous update was at 11:21 AM ----------

I tried with sqlplus -s and it removed the sql commands

however output now is as below

Code:
 ab,cd
 ef,gh
  
 2 rows selected

****I don't want this 2 rows selected to come, how do I avoid this.


Moderator's Comments:
Mod Comment Please use code tags as required by forum rules AND as requested before!
Last edited by RudiC; 07-29-2016 at 01:50 PM.. Reason: Added code tags.
# 6  
Old 07-29-2016
Please wrap your code fragments or data samples in code tags

Try this:-
Code:
sqlplus -s username/pwd@sid << EOF
exec pkgname(in parameter);
set echo off head off feed off pagesize 0 trimspool on linesize 1000
spool test_output.txt
select col1 || ',' || grp_cd from test;
spool off;
exit
EOF

# 7  
Old 07-30-2016
Thank you very much. It worked finally :-)
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Requirement of Spliting a text file in UNIX Programing

Hi, There is a requirement, needs to split the text file based on RC code present in text file. For this, needs to write a unix shell programing script for the above requirement. For example in text file, if there are distinct RC codes, then we needs to split into multiple text files. In... (1 Reply)
Discussion started by: Chandra2678
1 Replies

2. Shell Programming and Scripting

Spool Data in a file

Hello, I am trying to spool data from database into a file on solaris through ksh. Data is getting fetched but the problem is record is getting split in to multiple lines. excerpt from sql is whenever sqlerror exit 1; set define on set echo off set feed off set head off set... (1 Reply)
Discussion started by: abhi1988sri
1 Replies

3. Shell Programming and Scripting

Spool file, only if data exists.

Hi All, I have one Question, that is it possible to check the spool output? What i mean to say is "spool the file only if data exists" I am trying to fetch data from a sql query, and want to generate a file, on a condition that the file should be generated, only if the output of the sql... (5 Replies)
Discussion started by: spiabhi
5 Replies

4. Shell Programming and Scripting

spool to file in ksh

hi all, am trying to write a ksh script which will take a username, password, db instance as input arguments and login to the database using sqlplus and select some data via sql and spool to a file. problem is that i am using "sqlplus -s" in order to hide the username/password and it doesnt... (8 Replies)
Discussion started by: cesarNZ
8 Replies

5. Shell Programming and Scripting

Problem in spool file

Hi, Iam a newbie. When I give the below query at SQL prompt. SQL> select col1||'`ß`'||col2 from tablename where rownum<2; 1-J7WGX`ß`1-7OKC-23 Iam getting ß within appostropies...... If I remove appostropies and give the query it is throwing an error. If I give the same query in spool as... (1 Reply)
Discussion started by: kknayak
1 Replies

6. AIX

Spool data file

Dear Gurus, Tried searching for some clues in this forum but dont seem to be able to find my answer. :confused: Anyway i have a quick question: Today I have produced a messages generated from a application and placed them on the print queue. Before this I had stopped the printer queue, so... (2 Replies)
Discussion started by: lweegp
2 Replies

7. UNIX for Dummies Questions & Answers

SQLPlus spool file

HI Have some problem with spooling out some relatively large number of records (~2-3 mil) Small table - OK though. Getting error: SP2 0308: cannot close spool file. Any thoughts? sqlplus -s user/pwd << EOF set term off set head off set trims on set pages 0 set... (1 Reply)
Discussion started by: Leo_NN
1 Replies

8. Shell Programming and Scripting

grep spool file error

Hi, I am trying to do this For i in *.txt do sed 'some pattern checking' $i done >file These text files are spool files.And when i find error in these spool files I want to append the text file contents to error.log file. Or is there any option that I do this sed for only files that... (12 Replies)
Discussion started by: ran16
12 Replies

9. Shell Programming and Scripting

how to spool a unix file

hi, can anyone help me by saying how can i spool a unix file.. do we need to specify the pathname as such to spool the file .. right now, i tried giving like spool filename in the sql prompt.. but its not giving me the required output even if i can see that the command is being executed.. ... (2 Replies)
Discussion started by: kripssmart
2 Replies

10. Shell Programming and Scripting

Picking the file based on Date..Requirement

Dear frnds My requirement is as follows -rw-r----- 1 f02 dd 109428250 May 18 14:02 Extracts_20070518104730.zip -rw-r----- 1 f02 dd 109493187 May 21 13:30 Extracts_20070521091700.zip -rw-r----- 1 f02 dd 109993058 May 23 14:14 Extracts_20070523085955.zip -rw-r----- 1... (5 Replies)
Discussion started by: sureshg_sampat
5 Replies
Login or Register to Ask a Question