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

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\';
#

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:
You need to go:
But to globally escape all the special characters, use this code:

or


Good luck

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.

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:
You have another HERE document in your script that you might want to take in consideration.

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

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

IF condition failing in a SSH script

I need to set the environment variables like ORACLE_HOME, SID without playing around with the user profile, that's why the need to execute multiple commands in the ssh command.

The below work fines given it only execute sql queries. But I still need to set the environment settings.

Will try again, thanks alot