redirecting oracle sqlplus select query into file

So, I would like to run differen select queries on multiple databases..

I made a script wich I thought to be called something like..

./script.sh sql_file_name out.log

or to enter select statement in a command line..
(aix)

and I did created some shell script wich is not working..
it looks like:

#!/bin/bash
sql=$1
out_file=$2
if [ -f $out_file ]
then
        rm $out_file
fi
while read line
do
        if [[ ! "$line" =~ ^# ]]; then
                tns="$(echo $line | cut -d: -f2)"
                pwd="$(echo $line | cut -d: -f1)"
                echo SYS@ $tns >> $out_file
                export ORACLE_SID=$tns
                #sqlplus -S -L sys/$pwd@$tns  AS SYSDBA < $sql >> $out_file
                #echo +++++++++++++++++++ >> $out_file
                echo $sql
                sqlplus -s -l sys/$pwd@$tns AS SYSDBA < $sql >> $out_file
        fi
done < "connections_sys"

so, what's wrong?

I would like to make something like 'gimme all instance names from all databases into my file'

can you post the content of connections_sys?
also, Are you getting some error? whats that?

connection_sys looks something like:

dbpwd1:DATABASE1
dbpwd2:DATABASE2

error is like:

./run_sys.sh: line 20: $sql: ambiguous redirect
SELECT NUM, NAME, VALUE FROM V$PARAMETER WHERE NUM=1715

If I understand correctly and you are trying to redirect the query results to a text file then you can try something like this:

 
sqlplus -s user/pass << EOF
SPOOL /Myoutputdir/myfile.txt
select sysdate from dual;
SPOOL OFF
 

You might also want to check sqlplus set commands like:

set linesize
set pagesize
set echo
set term

and such

yes, but sql statement should be different each time

Hi
Try this way:

sqlplus -s -l sys/$pwd@$tns AS SYSDBA  @$sql >> $out_file

Guru.

you can have two files.

one is "connection_sys" and the other one is the sql queries for the respective databases. and read the files simultaneously.

exec 4<connection_sys
while read query
do
 IFS=":" read user pass sid <&4
 $ORACLE_HOME/bin/sqlplus -S $user/$pass@$sid  << EOF
 $query
EOF
done < sql_queries
4<&-

you can change the sqlplus syntax as you need for sysdba.

remember to change

IFS=":" read user pass sid <&4

to

IFS=":" read pass sid <&4

when you remove the username part from the "connection_sys" file.

the current format is:

user1:pass1:sid1
user2:pass2:sid2