Get oracle table rowcount from remote host through SSH

I am trying the following using a shell script:

1. Login to a remote host through ssh ( I have alreay setup the public-private keys and it is working fine)
2. Fire a SQL query to get count of rows in a table, return that to a variable

This does not appear to work.

The problem is..it does not appear to run the commands within the backtick in the ssh session. This is because the error reported is about the path of sqlplus : "No such file or directory"  (Oracle home is different in the local host and remote hosts) . Need help from shell scripting experts.
source_oracle_home=/u01/app/oracle/product/10.2.0/db_1
sqlquery=select count(*) from test;
 
ssh oracle@$192.168.1.2 "
export ORACLE_SID=MYDB
export ORACLE_HOME=$source_oracle_home
rowcount=`
$source_oracle_home/bin/sqlplus -s dbuser/dbpassword@MYDB << EOF
set heading off
set feedback off
$sqlquery;
exit;
EOF`
echo Number of rows: $rowcount"

Open in new window

LVL 3
mganeshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omarfaridCommented:
try to remove space before EOF in

$source_oracle_home/bin/sqlplus -s dbuser/dbpassword@MYDB << EOF
0
mganeshAuthor Commented:
Did that. The error that I am getting is the same:
/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus: No such file or directory

This path is correct on the remote host. But not correct on the local host. Hence I have a feeling this being executed on the local host.
0
omarfaridCommented:
why don't you spool results to file then cat file on remote system?
0
MikeOM_DBACommented:
1) Add the remote db's to the local tnsnames.ora
2) Create a text file (db_remote.txt) with names of remote db's.
3) Execute something like this:
 

#!/bin/ksh
export ORACLE_SID=MYDB
ORAENV_ASK=NO
. /usr/local/bin/oraenv
SQLQRY='select count(*) from test;'
DBFILE=/path_to_my/scripts/db_remote.txt
>rowcnt.lst
for db in `cat $DBFILE`
do
  rowcount=`
    sqlplus -s dbuser/dbpassword@$DB <<EOF
    set head off feed off term off
    $SQLQRY
    exit;
    EOF`
  echo "$db has $rowcount rows.">>rowcnt.lst
done

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.