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"

Solution

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'

Comments

[user2488578]

\$lastMonth and so on doesn't work

[meuh]

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.