bongo
1
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'
clx
2
can you post the content of connections_sys?
also, Are you getting some error? whats that?
bongo
3
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
haaru
4
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
bongo
5
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.
clx
7
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