BASH - Single quote inside double quote for SQL Where clause

Question

I need to send a properly formatted date comparison WHERE clause to a program on the command line in bash.

Once it gets inside the called program, the WHERE clause should be valid for Oracle, and should look exactly like this:

highwater>TO_DATE('11-Sep-2009', 'DD-MON-YYYY')

The date value is in a variable. I've tried a variety of combinations of quotes and backslashes. Rather than confuse the issue and give examples of my mistakes, I'm hoping for a pristine accurate answer unsullied by dreck.

If I were to write it in Perl, the assignment would I think look like this:

$hiwaterval = '11-Sep-2009';
$where = "highwater>TO_DATE(\'$hiwaterval\', \'DD-MON-YYYY\')";

How do I achieve the same effect in bash?

Answer 1

Have you tried using using double ticks? Like highwater>TO_DATE(''11-Sep-2009'', ''DD-MON-YYYY''). Just a suggestion. I haven't tried it out.

Answer 2

hiwaterval='11-Sep-2009'

where="highwater > TO_DATE('$hiwaterval', 'DD-MON-YYYY')"

optionally add "export " before final variable setting if it is to be visible ourside the current shell.

Answer 3

You can assign the where clause like this:

export WHERECLAUSE=`echo "where highwater >TO_DATE('11-Sep-2009', 'DD-MON-YYYY')"`

(with backticks around the echo statement - they're not showing up in my editor here...)

which works with a shell script of the form:

sqlplus /nolog <<EOS
connect $USERNAME/$PASSWD@$DB
select * from test $WHERECLAUSE
;
exit
EOS