0

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

Community
  • 1
  • 1
exit_1
  • 1,240
  • 4
  • 13
  • 32
  • 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 Feb 16 '17 at 19:55
  • Do you tried without backslashes? I can easily execute your command. – 0xdb Feb 16 '17 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 Feb 16 '17 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 Feb 16 '17 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 Feb 16 '17 at 20:16
  • Ah, I see, it is a ksh version issue, I can duplicate on Solaris 10 (ksh88), but not on Linux. – Alex Poole Feb 16 '17 at 20:17

2 Answers2

1

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
)
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    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](http://stackoverflow.com/q/25569857/266304). I think I might even recognise it from many years ago - better late than never I suppose. – Alex Poole Feb 16 '17 at 20:28
  • excellent, thank you very much. I tried replacing `$(...)` with back ticks and it worked. I think I'll look into getting our version upgraded. – exit_1 Feb 16 '17 at 20:30
0

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.

User9102d82
  • 1,172
  • 9
  • 19