Hi All,
I am new to shell script. I am trying to pass value from .sh file to .sql file .
But I am able to run the .sql file from .sh file with values in sql file.
But I am unable to pass the values from .sh file. can some one please help to resolve this.
here is my .sh file
s1.sh
#!/bin/ksh
echo "please enter case:"
read v_case_nbr
echo "the case number entered is $v_case_nbr"
sqlplus wmtst/wmtst@wmtest <<EOF
@ test.sql $ v_case_nbr
exit;
EOF
here is .sql file
SET SERVERoutput on;
declare
v_case_nbr varchar(400):='&Enter_Case_nbr';
c_stat_code case_hdr.stat_code%type;
cursor c1 is select ch.stat_code into c_stat_code from case_hdr ch where ch.case_nbr=v_case_nbr;
begin
open c1;
LOOP
FETCH c1 into c_stat_code;
EXIT when c1%notfound;
If (c_stat_code = '50')
then
update case_hdr ch
set ch.stat_code='30',
ch.user_id = 'PILLAREM',
ch.mod_date_time = sysdate
where ch.case_nbr = v_case_nbr;
dbms_output.put_line('Case_hdr:Case updated To putaway status ');
end if;
end loop;
close c1;
end;
/
Here I need to pass v_case_nbr='123456'
from .sh file to .sql file
Please help me to resolve this.
Thanks a bunch.
Regards,
reddy298599