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
  • Try escaping them with backslashes. Something like this: \'hell123\'. – slm Jan 6 '16 at 16:02
  • Use double quotes for the ssh command and then single quotes for the column and for the DBHost. You could also replace the backticks by a $() construct. – ott-- Jan 6 '16 at 16:25
  • echo 'Like '\'' This' – thrig Jan 6 '16 at 17:18
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"
  • 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 7 '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 Jan 7 '16 at 12:30
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.