How to use single quotes in sqlplus select string in Unix shell script

Question

I know this is a commonly discussed topic but I have tried all of the answers in other posts to no avail so I have finally decided to post my situation.

In my script:

failures=$(sqlplus -s << EOF
${SQLIN}
set heading off;
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
select failures from dba_jobs where what like dbms_refresh.refresh%;
EOF
)

dbms_refresh.refresh% is what I need to capture in single quotes, however, I know just using single quotes by themselves is not enough. I do not fully understand how unix and oracle translate single quotes in a select command.

I have tried the following with no luck:

\'dbms_refresh.refresh%\' : from here
'\''dbms_refresh.refresh%'\'' : from here

Can someone please give me an explanation on how unix and oracle are talking to eachother that determines how a single quote is read in a select query?

EDIT: below is my result if I use 'dbms_refresh.refresh%'

select failures from dba_jobs where what like "dbms_refresh.refresh%" check_mview_test.sh check_mview_test_v1.ksh check_mview_test_v1.sh get_pageid_test.sh ERROR at line 1: ORA-00904: "dbms_refresh.refresh%": invalid identifier

Why do you think just using single quotes by themselves is not enough? Using where what like 'dbms_refresh.refresh%'; works fine for me in bash (and variants). What error do you get when you just use single quotes? — Alex Poole
– Alex Poole, Commented Feb 16, 2017 at 19:55
Do you tried without backslashes? I can easily execute your command. — 0xdb
– 0xdb, Commented Feb 16, 2017 at 20:00
hm thats interesting. I was using korn shell. I'll try executing in bash. Ill update my question with my error. — exit_1
– exit_1, Commented Feb 16, 2017 at 20:06
Your edit is using double quotes, not single quotes, according to the error. I don't have a problem with single quotes in ksh either; but possibly it's a version issue (if you do get an error) - what does ksh --version tell you? — Alex Poole
– Alex Poole, Commented Feb 16, 2017 at 20:10
see thats what I'm confused about. y script uses single quotes but the error return shows double quotes. ksh --version returns --version: bad option(s), sorry, not sure how to get better output for you — exit_1
– exit_1, Commented Feb 16, 2017 at 20:16

Alex Poole · Accepted Answer · 2017-02-16 20:22:07Z

What you've shown works with plain single quotes in ksh93, but ksh88 seems to be changing the single quotes into double quotes inside the command substitution, or perhaps inside the heredoc. That's why, in your edit, yoru query with single quotes gets an error reported showing a (double-)quoted identifier.

From some experimentation you can avoid that by changing the assignment from $(...) to backticks:

failures=`sqlplus -s << EOF
${SQLIN}
set heading off;
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
select failures from dba_jobs where what like 'dbms_refresh.refresh%';
EOF
`

Or you could put the string into its own shell variable, which also seems to avoid it; but that doesn't really scale for more complicated queries:

VALUE="'dbms_refresh.refresh%'"

failures=$(sqlplus -s << EOF
${SQLIN}
set heading off;
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
select failures from dba_jobs where what like ${VALUE};
EOF
)

Of course, having realised it was a ksh version issue, I should have researched that instead of trying to find a workaround... it seems to be a well-known issue with heredocs in ksh88. I think I might even recognise it from many years ago - better late than never I suppose.
excellent, thank you very much. I tried replacing $(...) with back ticks and it worked. I think I'll look into getting our version upgraded.

User9102d82 · Accepted Answer · 2017-02-16 19:55:16Z

Seems to work, although not exactly on "SQLPLUS" but I tried it on my postgres.

Here is the script:

=>|Fri Feb 17|01:23:36|postgres@[STATION]:/var/lib/pgsql> cat test.sh
#!/bin/bash
mytable="$1"
failures=$(psql <<EOF
select phonenumber from $mytable where phonenumber like '91%' ;
EOF
)
echo "==========RESULT==========="
echo $failures
echo "============END============"

=>|Fri Feb 17|01:23:39|postgres@[STATION]:/var/lib/pgsql>

The output:

=>|Fri Feb 17|01:24:12|postgres@[STATION]:/var/lib/pgsql> ./test.sh mdn_2
==========RESULT===========
phonenumber -------------- 919821217792 (1 row)
============END============

=>|Fri Feb 17|01:24:14|postgres@[STATION]:/var/lib/pgsql>

Hope this helps. Let us know. Thanks.

Collectives™ on Stack Overflow

How to use single quotes in sqlplus select string in Unix shell script

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related