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 · Accepted Answer · 2015-08-30 14:51:23Z

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, 2015 at 14:51

answered Aug 30, 2015 at 14:13

meuh

54.7k2 gold badges70 silver badges138 bronze badges

\$lastMonth and so on doesn't work

user2488578
– user2488578

2015-08-30 14:29:17 +00:00
Commented Aug 30, 2015 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
– meuh

2015-08-30 14:49:08 +00:00
Commented Aug 30, 2015 at 14:49

Add a comment |

rudimeier · Accepted Answer · 2016-09-28 19:15:09Z

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
)

Stack Exchange Network

Pass shell variables to SQL statement

2 Answers 2

You must log in to answer this question.

Hot Network Questions

Pass shell variables to SQL statement

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions