CODEWITHSUNDEEP
sqlshellsql-likeoracle21c
DipayanRay
DipayanRay

Reputation: 15

Use of like "%" character in sql query changes my query when executed as shell script

I am new to shell scripting and trying to read a list of employee IDs from a text file as part of my first shell script program and pass it to another shell script as it's an argument. In the second script, I am using that argument on a SQL select command with a like operator. But my issue is that the command gets executed as something else.

Contents of first.sh

#!/bin/sh
while IFS= read -r line; 
do
/d/ShellScripts/second.sh  $line
done < "$1"

Contents of second.sh

#!/bin/sh
EMP_ID=$1
sqlplus -s user/password@//localhost:1521/xepdb1 <<EOF
alter session set current_schema = HR;
set linesize 1000
select * from employees where EMPLOYEE_ID like "\'"%$EMP_ID"\'" ;
select * from employees where EMPLOYEE_ID like %{$EMP_ID} ;
EOF

Contents of list.txt

101
102
103

Command executed in the shell

$ ./first.sh list.txt

Session altered.

"\'"ct * from employees where EMPLOYEE_ID like "\'"%101
                                                   *
ERROR at line 1:
ORA-00911: invalid character

}elect * from employees where EMPLOYEE_ID like %{101
                                               *
ERROR at line 1:
ORA-00911: invalid character

I tried to take help from here. I am just trying to execute a normal SQL query using like operator, using shell scripts.

Upvotes: 0

Views: 481

Answers (2)

DipayanRay
DipayanRay

Reputation: 15

Okay below is the solution which worked.

Modified contents of file second.sh

#!/bin/sh
EMP_ID=$1
sqlplus -s user/password@//localhost:1521/xepdb1 <<EOF
set linesize 1000
select * from ACCOUNT_BALANCE where KEY_1 like '%$EMP_ID' ;
EOF

Rest is untouched.

I previously overlooked the column type. I was trying to apply the '%' operator on a column of type number,

Upvotes: 0

Ishan Anand
Ishan Anand

Reputation: 161

There is no need to escape the character's single quotation mark (') in the bash script. Simply surrounding the variable in a single quotation mark is enough.

You can change your second.sh script lines from this

select * from employees where EMPLOYEE_ID like "\'"%$EMP_ID"\'" ;
select * from employees where EMPLOYEE_ID like %{$EMP_ID} ;

to

select * from employees where EMPLOYEE_ID like '%$EMP_ID' ;

Upvotes: 0

Related Questions