Visit Our UNIX and Linux User Community


spool to file in ksh


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting spool to file in ksh
# 1  
Old 08-12-2009
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 spool any data since its in silent mode.

how do i get around this problem ??


heres the code :

Code:
#!/bin/ksh

$SQLPLUS -s $1/$2@$3  > /dev/null 2>&1

spool > "/var/tmp/out.log";

select * from dual;

spool off;

exit;



thanks.

Last edited by vgersh99; 08-12-2009 at 07:55 PM.. Reason: code tags, PLEASE!
# 2  
Old 08-12-2009
To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums
# 3  
Old 08-12-2009
Are you sure it is not spooling the data out or you are not getting any data from your sql command? Did you take the -s out and run it?
# 4  
Old 08-12-2009
hi,
i did try and run the script without the '-s' option. it still doesnt spool. All it does with the '-s' option removed is login to the db and just sits on the sql command prompt.

---------- Post updated at 05:29 PM ---------- Previous update was at 05:27 PM ----------

Quote:
Originally Posted by vgersh99
To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums
hi,
will keep this in mind for next time. how does the "charged 3K" work ??

ta.
# 5  
Old 08-12-2009
Spool does not need the '>'
If you stop redirecting output to the /dev/null you will see like this:
Code:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> spool > grb.sql
SP2-0333: Illegal spool file name: "> grb.sql" (bad character: ' ')
SQL> quit

It is not a -s swich problem:
Code:
src> sqlplus -s $AIM_PSWD >$gr 2>&1
spool grb2.sql
select sysdate from dual;
quit
08/12-17:59:38
src> cat grb2.sql

SYSDATE
---------
12-AUG-09

1 row selected.


Last edited by alex_5161; 08-12-2009 at 09:12 PM..
# 6  
Old 08-12-2009
ok have changed the script to :

Code:
$SQLPLUS -s $1/$2@$3 >$gr 2>&1
spool out.sql
select * from dual;
quit


and i get this error now "spool: not found". Do i need to declare some oracle variables etc ??
# 7  
Old 08-13-2009
This is what worked for me :

Code:
#!/bin/ksh

sqlplus -s $1/$2@$3 << EOF  >>op.txt


select sysdate
from dual ;


exit;

EOF


Previous Thread | Next Thread
Test Your Knowledge in Computers #621
Difficulty: Medium
There is no switch statement in Python.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Order of data in Spool File

Hello, I have a shell script through which I am executing .sql file and spooling the result of Query from .sql . I want to spool the result in ascending order. Is there any parameter to be set to print result in ascending or descending order. Thanks in advance. (4 Replies)
Discussion started by: Aparna.N
4 Replies

2. Shell Programming and Scripting

Spool file requirement

Hi, I have a requirement of Connecting to sqlplus from unix Execute the package. The output of package is stored in a table 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.... (6 Replies)
Discussion started by: Shanmugapriya D
6 Replies

3. UNIX for Dummies Questions & Answers

Column Header in the Spool file

Hi All, I have a problem with the column heading while spooling the data from the database. Since i want the column header, therefore i am not using SET HEADING OFF, and i am getting the header, but for the longer column name, some of the character from the column name is missing. for... (7 Replies)
Discussion started by: Pramod_009
7 Replies

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

Featured Tech Videos