How to pass string into sql query?

Shell Programming and Scripting
1

Hi Gurus,

I have a request which needs to pass string into sql.
dummy code as below:

sqlplus -s user/password@instance << EOF >>output.txt
set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep ,
select emp_no, emp_name from emp
where emp_no in ('a', 'b', 'c');
exit;
EOF

for above query, I need pass string 'a', 'b', 'c' dynamically.

any input is really appreciate.

Thanks in advance.

2

Using positional parameters:

a=$1
b=$2
c=$3

sqlplus -s user/password@instance << EOF >>output.txt
set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep ,
select emp_no, emp_name from emp
where emp_no in ($a, $b, $c);
exit;
EOF
1 Like
3

Thanks for you reply. this works.
one more question, I have file list like below:

a
b
c
d

I want to create parameter as
para='a','b','c','d'

would you please help me out this.

Thanks in advance

4

Read file and create parameter:

while read p
do
        [ -z "$para" ] && para="'${p}'" || para="${para},'${p}'"
done < file

Use the variable in sql:

where emp_no in ( $para );
1 Like
5

Thanks this code work perfect.

Thanks again.