1

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"
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'
  • \$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
0

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
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.