Pass shell variables to SQL statement

Question

I want to pass shell variables to an SQL statement. Both shell script and SQL statement are present in the same script file.

I want the values of the variables retMonth, retLastDay and retPrvYear in the SQL statement. Below is the code.

echo $retMonth  //This prints 07
echo $retLastDay //This prints 31
echo $retPrvYear  //This prints 2015

count=$(sqlplus -s  ${DBA_ORACLE_USER}/${DBA_ORACLE_PWORD}@${ORACLE_SID} <<END
#connect ${DBA_ORACLE_USER}/${DBA_ORACLE_PWORD}@${ORACLE_SID}
set serveroutput on
set linesize 1000
set heading off
set feedback off
define lastMonth=$retMonth
define lastYear=$retPrvYear
define lastDay=$retLastDay
SELECT count(1)
FROM MYTABLE
WHERE partition_date between '01-$lastMonth-$lastYear' and '$lastDay-$lastMonth-$lastYear'
  );
END
)

If I execute this, it prints:

partition_date between '01--' and '--' \ 0 0] 1 1] 12-DEC-14 1"

meuh · Answer 1 · 2015-08-30T14:51:23.473

1

When you use a hereis string <<END all $variable expansion is done. This is how your define lines can work. But you dont want the $lastMonth and so on in the WHERE statement to be expanded, so you need to quote them with backslash. The single quote has no particular effect here as we are inside a hereis.

However, it seems that sqlplus uses ampersand & to expand DEFINE variables so you probably want

WHERE partition_date between '01-&lastMonth-&lastYear' and '&lastDay-&lastMonth-&lastYear'

edited Aug 30 '15 at 14:51

answered Aug 30 '15 at 14:13

meuh

49,672
2
52
114

\$lastMonth and so on doesn't work – user2488578 Aug 30 '15 at 14:29
I dont know sqlplus, but it seems you should be using `&` not $ for these variables in the WHERE statement, so no need for a backslash either. – meuh Aug 30 '15 at 14:49

rudimeier · Answer 2 · 2016-09-28T19:15:09.503

Seems that you've made a mistake with sqlplus variable expansion. You should use & rather than $.

However you can avoid sql variable at all, directly using the shell variables only:

count=$(sqlplus -s  ${DBA_ORACLE_USER}/${DBA_ORACLE_PWORD}@${ORACLE_SID} <<END
#connect ${DBA_ORACLE_USER}/${DBA_ORACLE_PWORD}@${ORACLE_SID}
set serveroutput on
set linesize 1000
set heading off
set feedback off
SELECT count(1)
FROM MYTABLE
WHERE partition_date between '01-$retMonth-$retPrvYear' and '$retLastDay-$retMonth-$retPrvYear'
  );
END
)

Note you could have debugged this problem by yourself using cat instead sqlplus just to print the expanded sql code:

echo $(cat <<END
...
END
)

Pass shell variables to SQL statement

2 Answers2