Issue with quotes when running SQL command from within su -c
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.
Code:
# 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
Code:
# ./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.
Code:
$ 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.
Code:
# 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
#
Code:
# ./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.
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 ?
Code:
# 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
"
#
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
Hi All,
I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise.
Eg :
Select 'Query 1 output' from dual;
Select 'Query 2 output' from dual;
I want to... (3 Replies)
Hi Everyone!
I am facing an issue in running a command line utility from the CRON.
This utility displays IPC statistics on UNIX message queues: The "queue name" and the "count" of messages in the queue.
When running this utility from prompt, it will provide an output on the screen, like the... (4 Replies)
I got a issue with running the following script below if I remove the inserts and alter table the tables will install just fine but if I try and do the full thing in one go i get the below error dose any one have any ideas on this problem?
its got me spinning, thanks.
REM
REM List of... (1 Reply)
RHEL 6.2/Bash shell
root user will be executing the below script. It switches to oracle user and expect to do the following things
A. Source the environment variables for BATGPRD Database (the file used for sourcing is shown below after the script)
B. Shutdown the DB from sqlplus -- The... (13 Replies)
Hi,
Trying to change the prompt. I have the following code.
export PS1='
<${USER}@`hostname -s`>$ '
The hostname is not displayed
<abc@`hostname -s`>$ uname -a
AIX xyz 1 6 00F736154C00
<adcwl4h@`hostname -s`>$
If I use double quotes, then the hostname is printed properly but... (3 Replies)
i'm trying to write a bash script that executes a mysql statement
mysql -sN -e INSERT INTO "$database"."$tableprefix"users (var1, var2,var3) VALUES (123, '1','')
i don't know where to put the quotes
it doesnt work with this one: `
it seems i can only put double quotes around the... (0 Replies)
Hi.
please help me to write the following query in a shell script.
the Query is :select no,salary from emp_info where name='$var_name'
the following is my code.
#! /bin/sh
var_name=$1
sqlplus -s user/pwd@DB << EOF
select no,salary from emp_info where name="'$var_name'";... (4 Replies)
Hi i am new on Unix..........
I have to write a ddl and for tables and I am using single quotes in the comment for the table and its column...........but when i see on the unix server the ' dont show .
e.g. DROP TABLE DMR_PURGED;
CREATE TABLE DMR_PURGED
(
HEALTH_SERVICE_ID ... (9 Replies)
I am a novice Unix scripter and need a little advice/help on a script I've written that's causing some problems. We are using Solaris 9 on a Sun box and the script is invoked with the korn shell. I have a two-part question: I wrote a shell script that calls and executes 3 separate sql scripts,... (3 Replies)
hi all
yes, I am a beginner in Unix,-sun solorais--V8...
what is the command to run files....
single and batch jobs....any help would be great...
Cheers
ps...i know running files in Sqlplus on Unix prompt, I can use the @' sign...but how does this work..
Cheers
E (2 Replies)
03-25-2014
