1

Below script helps me to ssh and do sqlplus to fetch info from database. I checked and it worked well but the only problem is if i use single quotes in the sql query then I am getting an error since I already used single quotes for ssh. 

db_info=`${ssh_path} -qn ${ssh_host} '
DBHost="<TNS>";
$ORACLE_HOME/bin/sqlplus -s $DBUser/$DBPass@$DBHost <<EOF
set heading off feedback off verify off
Select count(*) from table_name where column = 'hell123';
quit;
EOF
'`
echo $db_info
Gilles 'SO- stop being evil'
  • 829,060
JAMUNARANI S
  • 21

2 Answers2

2

You can include a ' in a single-quoted string by writing '\'' (explanation).

But it would be easier to use a here document for the shell snippet to run on the remote host.

db_info=$("${ssh_path}" -qn "${ssh_host}" <<\END_SSH
DBHost="<TNS>";
$ORACLE_HOME/bin/sqlplus -s $DBUser/$DBPass@$DBHost <<\END_SQL
set heading off feedback off verify off
Select count(*) from table_name where column = 'hell123';
quit;
END_SQL
END_SSH
)
echo "$db_info"
Community
  • 1
Gilles 'SO- stop being evil'
  • 829,060
  • Thanks Gillies, I already tried this but gettting below error and for your info i am using SunOS. Pseudo-terminal will not be allocated because stdin is not a terminal. stdin: is not a tty logname: no login name TERM environment variable not set. TERM environment variable not set. Logged out on Thu Jan 7 12:37:23 CET 2016... – JAMUNARANI S Jan 07 '16 at 11:40
  • @JAMUNARANIS Try adding the -t option to SSH. This may or may not work, I don't understand why sqlplus wants a terminal. – Gilles 'SO- stop being evil' Jan 07 '16 at 12:30
1

Thanks Thrig. I used your idea in my script and it works perfectly. Just add '\'' in the place of single quote.

db_info=`${ssh_path} -qn ${ssh_host} '
DBHost="<TNS>";
$ORACLE_HOME/bin/sqlplus -s $DBUser/$DBPass@$DBHost <<EOF
set heading off feedback off verify off
Select count(*) from table_name where column = '\''hell123'\'';
quit;
EOF
'`
echo $db_info
JAMUNARANI S
  • 21