Shell Script (ksh) - SQLPlus query filter using a string variable

Shell Programming and Scripting
1

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable.

REPO_DB=DEV1
FOLDER_NM='U_nmalencia'
 
FOLDER_CHECK=$(sqlplus -s /nolog  <<EOF
CONNECT user/pswd_select@${REPO_DB}
set echo off heading off feedback off
select subj_name
from subject
where subj_name='${FOLDER_NM}';
exit;
EOF)
 
echo FOLDER_CHECK: ${FOLDER_CHECK}

This is giving me the following trace and error. The evaluation of ${FOLDER_NM} is not happening.

+ REPO_DB=DEV1
+ FOLDER_NM=U_nmalencia
+ + sqlplus -s /nolog
+ 0<<
CONNECT user/pswd@ETLDEV1
set echo off heading off feedback off
select subj_name
from subject
where subj_name="${FOLDER_NM}";
exit;
FOLDER_CHECK=where subj_name="${FOLDER_NM}"
                *
ERROR at line 3:
ORA-00904: "${FOLDER_NM}": invalid identifier

I also tried...

FOLDER_CHECK=$(sqlplus -s /nolog <<EOF
CONNECT user/pswd_select@${REPO_DB}
set echo off heading off feedback off
select subj_name
from subject
where subj_name=\'${FOLDER_NM}\';
exit;
EOF)

and got the following error:

FOLDER_CHECK=where subj_name=\'U_nmalencia\'
                *
ERROR at line 3:
ORA-00911: invalid character

Stumped in Atlanta....

2

Try :

where subj_name='${FOLDER_NM}';

or

where subj_name='$FOLDER_NM';
3

I initially tried using the code as you have suggested, with and without the curley braces.

where subj_name='${FOLDER_NM}';

Neither worked. I turned tracing on and I am seeing the single quotes are being evaluated as double quotes and the variable is not being replaced.

4

try:

EOF
)
5

do

FOLDER_CHECK=`sqlplus -s uname/pwd@sid  <<EOF
set echo off 
set heading off 
set feedback off
select subj_name
from subject
where subj_name=$FOLDER_NM;
exit
EOF`
1 Like
6

I was able to get it to work correctly using the following code:

REPO_DB=DEV1
FOLDER_NM='U_TEST'
FOLDER_CHECK=$(sqlplus -s /nolog  <<EOF
CONNECT user/pswd@${REPO_DB}
set echo off heading off feedback off
select subj_name
from subject
where subj_name=`echo "'${FOLDER_NM}'"`;
exit;
EOF)
echo FOLDER_CHECK: ${FOLDER_CHECK}

---------- Post updated at 10:31 AM ---------- Previous update was at 09:49 AM ----------

I also tried the code that Makarand Dodmis used, with a slight change to use the CONNECT command and it also worked correctly. Thank you Makarand, I like this code better because it seems more efficient....

FOLDER_CHECK=`sqlplus -s /nolog <<EOF
CONNECT user/pswd@${REPO_DB}
set echo off
set heading off
set feedback off
select subj_name
from subject
where subj_name='$FOLDER_NM';
exit
EOF`
echo ${FOLDER_CHECK}
1 Like