spool to file in ksh

Shell Programming and Scripting
1

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 :

#!/bin/ksh

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

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

select * from dual;

spool off;

exit;

thanks.

2

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

```text
 and 
```

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

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

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

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

ta.

5

Spool does not need the '>'
If you stop redirecting output to the /dev/null you will see like this:

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:

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

ok have changed the script to :

$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

This is what worked for me :

#!/bin/ksh

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


select sysdate
from dual ;


exit;

EOF
8

ok got it working, my problem was i didnt have 'EOF' at the end !!!

9 
sqlplus / <<EOF
spool test.log
select sysdate from dual;
exit;
EOF

scripter