Issue with quotes when running SQL command from within su -c

Shell Programming and Scripting
1

RHEL 6.2/Bash shell

root user will be executing the below script. It switches to oracle user logs in using sqlplus and tries to
run the below UPDATE statement. All the commands after su -c are enclosed in a single quote delimited by semicolon.
The execution has failed because the quotes surrounding the string (shown in red below) in the UPDATE statement is 'disappearing' during the execution.

# cat update.sh
su - oracle -c 'export ORACLE_SID=BRCFMS ;export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1;export PATH=$PATH:$ORACLE_HOME/bin
sqlplus -s scott/tiger <<EOF
spool /home/oracle/mytestSQL.log

update master_conf set config1='stomper-chk' where conf_id = 432;

exit
EOF
'
#

--- Executing the script as root user

# ./update.sh
update master_conf set config1=stomper-chk where conf_id = 432
                                       *
ERROR at line 1:
ORA-00904: "CHK": invalid identifier


#

-- How I confirmed that the disappearance of single quotes is the cause. I ran the same UPDATE statement
in sqlplus directly and got the same error as above.

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 25 22:43:16 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> update master_conf set config1=stomper-chk where conf_id = 432;
update master_conf set config1=stomper-chk where conf_id = 432
                                       *
ERROR at line 1:
ORA-00904: "CHK": invalid identifier

So, I tried escaping the single quotes in the UPDATE statement. But it errored out with the error below.

# cat update.sh
su - oracle -c 'export ORACLE_SID=BRCFMS ;export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1;export PATH=$PATH:$ORACLE_HOME/bin
sqlplus -s scott/tiger <<EOF
spool /home/oracle/mytestSQL.log

update master_conf set config1=\'stomper-chk\' where conf_id = 432;

exit
EOF
'

#
--- Executing
#

# ./update.sh
where: line 4: warning: here-document at line 1 delimited by end-of-file (wanted `EOF')
#

I tried enclosing everything after -c in doubles quotes instead of single quotes. It didn't work either.

2

Try this:

su - oracle -c "
export ORACLE_SID=BRCFMS
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export PATH=\$PATH:\$ORACLE_HOME/bin

sqlplus -s scott/tiger <<EOF
spool /home/oracle/mytestSQL.log

update master_conf set config1='stomper-chk' where conf_id = 432;

exit
EOF
"

Or just use a SQL script instead of here-doc.

1 Like
3

THANK YOU Radulov. Your fix worked !!
Escaping the $ signs in PATH variable (shown in red below) did the trick . But what is the rationale behind this ?

# cat update2.sh
su - oracle -c "export ORACLE_SID=BRCFMS;export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1;export PATH=\$PATH:\$ORACLE_HOME/bin

sqlplus -s scott/tiger <<EOF
spool /home/oracle/mytestSQL.log

update master_conf set config1='stomper-chk' where conf_id = 432;

exit
EOF
"
#

-- Executing

# chmod 777 update2.sh
#
# ./update2.sh

1 row updated.

#
4

To defer the expansion of the variables:

bash-3.00$ su - oracle -c "x=1; echo $x"
Password:
Sun Microsystems Inc.   SunOS 5.10      Generic January 2005

bash-3.00$ su - oracle -c "x=1; echo \$x"
Password:
Sun Microsystems Inc.   SunOS 5.10      Generic January 2005
1
1 Like