pl/sql in unix problem

Shell Programming and Scripting
1

hi all
i have written this small code.

sqlplus $schema_name/$passwrd@$instance <<eof
set serveroutput on;
declare
lv_count pls_integer;
lv_option varchar2(20);
begin
select count(*) into lv_count from all_objects where upper(object_name)=upper('$temp_file1');
DBMS_OUTPUT.PUT_LINE(lv_count);
if lv_count = 1 then
DBMS_OUTPUT.PUT_LINE('DO YOU REALLY WANT TO DROP THIS TABLE?');
lv_option:='&option';
if (lv_option = 'y' OR lv_option = 'Y') then
execute IMMEDIATE 'drop table $temp_file1';
end if;
END IF;
end;
/
@$file_name
eof

the problem is it never asks me the value of option.
i want it to ask me the option value and then drop the tabale.

please help.

2

Please use code tags!

echo "enter option:\c"
read option 
printf "'%s'" option | read option_value
sqlplus $schema_name/$passwrd@$instance <<eof
set serveroutput on;
declare
lv_count pls_integer;
lv_option varchar2(20);
begin
select count(*) into lv_count from all_objects where upper(object_name)=upper('$temp_file1');
DBMS_OUTPUT.PUT_LINE(lv_count);
if lv_count = 1 then
DBMS_OUTPUT.PUT_LINE('DO YOU REALLY WANT TO DROP THIS TABLE?');
lv_option:="$option_value";
if (lv_option = 'y' OR lv_option = 'Y') then
execute IMMEDIATE 'drop table $temp_file1';
end if;
END IF; 
end;
/
@$file_name
eof

play around with something like that