How to escape a range of wildcards in unix shell script?

Question

I need to execute the following Oracle SQL in unix crontab. The query is as follows:

select count(*) from tbaadm.htd where cust_id is not null and pstd_flg = 'Y' and del_flg = 'N' and tran_date = (select db_stat_date-1 from tbaadm.gct) and REGEXP_LIKE(tran_particular,'[^[:alnum:] ''`~!@#$%^&*-_{};":/.,<>?()]'); 

I have set escape characters before each wildcard, but still I am getting error. So I have written the crontab where I select the count first. But I am getting error again and again. Following are the relevant contents from my crontab:

. $HOME/.profile function dbconstants { USER="user" PASS="password" MAIL_BODY_PATH="/home/admin/CRONTAB_SHELL/" MAIL_BODY=$MAIL_BODY_PATH"mail.txt" } function checkcount { COUNT=`sqlplus -s $USER/$PASS@proddb <<EOF #connect $USER/$PASS@proddb; set pagesize 0 SET ESCAPE '\' select count(*) from tbaadm.htd where cust_id is not null and pstd_flg = 'Y' and del_flg = 'N' and tran_date = (select db_stat_date-1 from tbaadm.gct) and REGEXP_LIKE(tran_particular,'[^[:alnum:] \'\'\`\~\!\@\#\$\%\^\&\*\-\_\{\}\;\"\:\/\.\,\<\>\?\(\)\]\'\)\; EOF` echo $COUNT echo $COUNT | sed 's/^[ \t]*//;s/[ \t]*$//' |& read -p COUNT1 } function fetchdetails { `sqlplus -s $USER/$PASS@finratna <<EOF >$MAIL_BODY set feed off pages 0 trimspool on set pagesize 60 set linesize 9999 set trim on set head off SET ESCAPE '\' alter session set nls_date_format='DD-MM-YYYY'; select tran_date|| '|,' ||tran_id|| '|,' ||part_tran_srl_num|| '|,' ||tran_particular|| '|,' ||REGEXP_REPLACE (tran_particular,'[^[:alnum:] ''\`~!@#$%^&*-_{};":/.,<>?()]','') reg_par from tbaadm.htd where cust_id is not null and pstd_flg = 'Y' and del_flg = 'N' and tran_date = (select db_stat_date-1 from tbaadm.gct) and REGEXP_LIKE(tran_particular,'[^[:alnum:] ''\`~!@#$%^&*-_{};":/.,<>?()]'); EOF` } function deletefile { rm -f $MAIL_BODY } dbconstants checkcount if [ "$COUNT" -gt 0 ] then fetchdetails else echo "Nothing to Worry" fi deletefile 

The error I am getting is:

checkcount[13]: ~!@#$%^&*-_{};":/.,<>?()]');: not found. Nothing to Worry 

Answer 1

The error indicates the problem is here:

REGEXP_LIKE(tran_particular,'[^[:alnum:] \'\'\`\~\!\@\#\$\%\^\&\*\-\_\{\}\;\"\:\/\.\,\<\>\?\(\)\]\'\)\; ^ 

The shell interprets this backquote as closing the one in COUNT=, and tries to run the remaining of the line as a command, ~!@#$%^&*-_{};":/.,<>?()]');.

Try this:

COUNT=$(sqlplus -s $USER/$PASS@proddb <<EOF <snip> EOF ) 

The $() construct is usually safer (and easier to read).