0

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.

DipayanRay
  • 1
  • (a) in general you should quote your variable uses. eg. `"$line"`; (b) `list.txt` probably contains embedded `\r` (DOS-style line endings) that you need to strip; (c) `"\'"%$EMP_ID"\'"` and `%${EMP_ID}` should both probably just be `"%$EMP_ID"` – jhnc Mar 12 '23 at 01:43
  • Hello jhnc, I tried your suggested method and found the following issue `Session altered. "elect * from employees where EMPLOYEE_ID like "%101 * ERROR at line 1: ": invalid identifier ` – DipayanRay Mar 12 '23 at 02:26
  • that's because you haven't stripped the `\r` from your input file (and also apparently because oracle wants `''%EMP_ID'` rather than `"%EMP_ID"` for string literals) – jhnc Mar 12 '23 at 05:40

1 Answers1

0

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' ;
Ishan Anand
  • 119
  • 1
  • 7
  • I tried a different approach where I try to match strings so I ran the below 2 queries simultaneously in the same manner list.txt `David` second.sh `select first_name||' '||last_name "Name" from employees where first_name='David'; select first_name||' '||last_name "Name" from employees where first_name='{EMP_ID}';` output `Session altered. Name ---------------------------------------------- David Austin David Bernstein David Lee no rows selected` I am very new to this, what am I doing wrong? – DipayanRay Mar 12 '23 at 12:34
  • @DipayanRay Try the queries for concatenating two columns as shown : -> select concat( first_name, ' ', last_name ) as "Name" from employees where first_name='David'; I noticed that you are passing the wrong variable (EMP_ID) to column 'first_name', change your second query column "first_name" to "EMPLOYEE_ID" and fix the '{EMP_ID}' variable with '$EMP_ID' as shown -> select concat( first_name, ' ', last_name ) as "Name" from employees where EMPLOYEE_ID = '$EMP_ID'; – Ishan Anand Mar 12 '23 at 15:20
  • I fixed the variable name, below 3 lines are being executed with EMP_F_Name passed as an argument to second.sh `select first_name,last_name from employees where first_name='David'; select first_name,last_name from employees where first_name='$EMP_F_Name'; select first_name,last_name from employees where first_name=$EMP_F_Name;` Now, for 1st line I get 3 rows as output, 2nd line I get `no rows selected`, 3rd line I get `ORA-00904: "DAVID": invalid identifier` What should I do to get the value properly placed in the query? – DipayanRay Mar 13 '23 at 01:45