How to execute multiple commands via ssh

srage · #1 (**permalink**) 01-03-2008

I'm having some problem when I'm trying to remote ssh to another server to execute a sql query statement. The main problem lies with the single quotes, it doesn't recognise the single quote in the query.

To my understand, it's the single quote in the sql query that trigger the problem, tried using backslashes but it doesn't work.

Any advise will be greatly appreciated.

Non working example

# /usr/local/bin/ssh -qn [hostname] 'ORACLE_SID=[SID];export ORACLE_SID;ORACLE_HOME=`/usr/local/bin/dbhome lss`;export ORACLE_HOME;$ORACLE_HOME/bin/sqlplus -s [username]/[password]<< EndOfFile
> select unique granted_role from dba_role_privs where GRANTED_ROLE='DBA';
> exit;
> EndOfFile'
select unique granted_role from dba_role_privs where GRANTED_ROLE=DBA
*
ERROR at line 1:
ORA-00904: "DBA": invalid identifier

Working example

# /usr/local/bin/ssh -qn [hostname] 'ORACLE_SID=[SID];export ORACLE_SID;ORACLE_HOME=`/usr/local/bin/dbhome lss`;export ORACLE_HOME;$ORACLE_HOME/bin/sqlplus -s [username]/[password]<< EndOfFile
> select * from global_name;
> exit;
> EndOfFile'

Thanks And Regards
Eugene

wempy · #2 (**permalink**) 01-03-2008

Quote:

Originally Posted by srage

Non working example

# /usr/local/bin/ssh -qn [hostname] 'ORACLE_SID=[SID];export ORACLE_SID;ORACLE_HOME=`/usr/local/bin/dbhome lss`;export ORACLE_HOME;$ORACLE_HOME/bin/sqlplus -s [username]/[password]<< EndOfFile
> select unique granted_role from dba_role_privs where GRANTED_ROLE='DBA';
> exit;
> EndOfFile'
select unique granted_role from dba_role_privs where GRANTED_ROLE=DBA
*
ERROR at line 1:
ORA-00904: "DBA": invalid identifier

have you tried using double quotes around the name ("DBA") rather than single quotes.

srage · #3 (**permalink**) 01-03-2008

Quote:

Originally Posted by wempy

have you tried using double quotes around the name ("DBA") rather than single quotes.

I tried before too

# /usr/local/bin/ssh -qn [hostname] 'ORACLE_SID=[SID];export ORACLE_SID;ORACLE_HOME=`/usr/local/bin/dbhome lss`;export ORACLE_HOME;$ORACLE_HOME/bin/sqlplus -s [username]/[password]<< EndOfFile
> select unique granted_role from dba_role_privs where GRANTED_ROLE="DBA";
> exit;
> EndOfFile'
select unique granted_role from dba_role_privs where GRANTED_ROLE="DBA"
*
ERROR at line 1:
ORA-00904: "DBA": invalid identifier

It gives the same error, but for the sql statement to be valid, I need the single quote.

Using backslash >> terminated even before I terminate it.

# /usr/local/bin/ssh -qn [hostname] 'ORACLE_SID=[SID];export ORACLE_SID;ORACLE_HOME=`/usr/local/bin/dbhome lss`;export ORACLE_HOME;$ORACLE_HOME/bin/sqlplus -s [username]/[password]<< EndOfFile
> select unique granted_role from dba_role_privs where GRANTED_ROLE=\'DBA\';
#

rubin · #4 (**permalink**) 01-03-2008

The problem lies in setting right the environment in a different machine using ssh. In your query there are many special characters that need to be escaped, not only single quotes, but backquotes, $ signs, etc....
Example, from server A:

Code:

ssh user@serverB "hostname; echo `pwd`"           # same thing here for echo $HOME

Output ---> name_serverB, /home/dir_serverA

and not /home/dir_serverB.

You need to go:

Code:

ssh user@serverB "hostname; echo \`pwd\`"

But to globally escape all the special characters, use this code:

Code:

ssh user@serverB <<\EOF
your sql query....
.....
.....
EOF

or

Code:

ssh user@serverB <<'EOF'
your sql query....
.....
.....
EOF

Good luck

srage · #5 (**permalink**) 01-03-2008

But the scenario works with the a query with no condition (where condition)

eg.

Working SQL query
select * from global_name;

Non-Working SQL query
select unique granted_role from dba_role_privs where GRANTED_ROLE='DBA';

Somehow I need to escape the single quote in the select statement if it's possible because of the single quotes in front which is needed to execute multiple commands after I ssh over to the remote host.

rubin · #6 (**permalink**) 01-04-2008

Did you try my last suggestion ? I have no problem running commands in a ssh session with the below code that have single quotes in them:

Code:

ssh user@serverB <<\EOF
your sql query....
.....
.....
EOF

You have another HERE document in your script that you might want to take in consideration.

Quote:

...because of the single quotes in front which is needed to execute multiple commands after I ssh over to the remote host.

Not entirely true, you can use double quotes , and a HERE document too in a ssh session:

Quote:

ssh user@serverB "command1; command2;...."

Quote:

ssh user@serverB <<EOF
command1
command2
....
EOF

Executing multiple commands remotely is problematic. Take a look at this thread:

IF condition failing in a SSH script

srage · #7 (**permalink**) 01-04-2008

Some environment thingy causes the execution problem somewhere *I Guess*.

Given the same script, I execute it as 2 different OS user,

Script as follor:

Code:

# more t.sh
#!/bin/ksh

OracleLoginUsername=[Username]
OracleLoginPassword=[Password]

QueryStatement=`/usr/local/bin/ssh -qn [Hostname] "cd /usr/local/scripts/user_admin;export ORACLE_SID=[SID];export ORAENV_ASK=NO
;. /usr/local/bin/oraenv;$ORACLE_HOME/bin/sqlplus -s $OracleLoginUsername/$OracleLoginPassword << EndOfFile
@/tmp/CheckUser.sql [Parameter]
EndOfFile"`
UserName=$(echo $QueryStatement)
echo $UserName

As Root User
# ./t.sh
sh: ORACLE_SID=lss: is not an identifier

As another OS User
# ./t.sh
[Returned Result successfully]

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Execute db2 commands in unix	rollthecoin	AIX	3	04-26-2008 01:17 AM
lom don't execute commands	pasalagua	SUN Solaris	6	01-25-2008 04:22 PM
Can Xargs execute multiple commands of evry input file	nilesrex	Shell Programming and Scripting	4	08-30-2006 08:39 AM
how do i get my script to execute multiple commands?	hvincent	Shell Programming and Scripting	1	04-26-2006 08:19 AM
Execute commands parallel in a for loop ?	networkfre@k	Shell Programming and Scripting	3	11-27-2005 07:26 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread: