CODEWITHSUNDEEP
oracle-databaseshellunixsqlplus
exit_1
exit_1

Reputation: 1250

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

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:

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

Upvotes: 0

Views: 2588

Answers (2)

Alex Poole
Alex Poole

Reputation: 191580

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
)

Upvotes: 1

User9102d82
User9102d82

Reputation: 1190

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.

Upvotes: 0

Related Questions